JOINs are the bread and butter of SQL. If you cannot JOIN tables confidently, you cannot test databases. These 8 problems cover every JOIN type you will face at work.
Show each customer with their orders. Include customer name, order_id, order_date, total_amount, and status. Only customers who have placed orders.
SELECT c.first_name, c.last_name, o.order_id,
o.order_date, o.total_amount, o.status
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.first_name, o.order_date;Find products that no one has ever ordered. This is a classic QA check — dead inventory.
SELECT p.product_id, p.product_name, p.category, p.price
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.item_id IS NULL;LEFT JOIN + WHERE IS NULL is the most common pattern in QA testing. It finds orphans, missing records, and gaps. Memorize this pattern.
Show full order details: customer name, order_id, product name, quantity, unit_price, and line total. This requires joining 3 tables.
SELECT c.first_name, c.last_name, o.order_id,
p.product_name, oi.quantity, oi.unit_price,
(oi.quantity * oi.unit_price) AS line_total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
ORDER BY o.order_id;Show all orders with their payment status. Include orders that have NO payment record (this is a data quality issue).
SELECT o.order_id, o.order_date, o.total_amount, o.status,
p.payment_method, p.payment_status,
CASE
WHEN p.payment_id IS NULL THEN 'NO PAYMENT RECORD'
ELSE 'Payment exists'
END AS payment_check
FROM orders o
LEFT JOIN payments p ON o.order_id = p.order_id
ORDER BY o.order_id;Show all reviews with customer name, product name, rating, and review text. Join 3 tables.
SELECT c.first_name, c.last_name,
p.product_name, p.category,
r.rating, r.review_text, r.review_date
FROM reviews r
JOIN customers c ON r.customer_id = c.customer_id
JOIN products p ON r.product_id = p.product_id
ORDER BY r.review_date DESC;Find pairs of customers who live in the same city. Avoid duplicates (Rahul-Anjali and Anjali-Rahul should appear only once).
SELECT c1.first_name AS customer_1,
c2.first_name AS customer_2,
c1.city
FROM customers c1
JOIN customers c2
ON c1.city = c2.city
AND c1.customer_id < c2.customer_id
ORDER BY c1.city;Build the complete order pipeline: customer → order → order items → product → payment. Show customer name, order date, product name, quantity, payment method, and payment status.
SELECT c.first_name, c.last_name,
o.order_id, o.order_date, o.status AS order_status,
p.product_name, oi.quantity,
pay.payment_method, pay.payment_status
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
LEFT JOIN payments pay ON o.order_id = pay.order_id
ORDER BY o.order_id;Find products where the customer who reviewed them also bought them. This requires joining reviews, order_items, and orders.
SELECT DISTINCT c.first_name, c.last_name,
p.product_name, r.rating, r.review_text
FROM reviews r
JOIN customers c ON r.customer_id = c.customer_id
JOIN products p ON r.product_id = p.product_id
JOIN order_items oi ON oi.product_id = r.product_id
JOIN orders o ON oi.order_id = o.order_id
AND o.customer_id = r.customer_id
ORDER BY c.first_name;Self JOINs are tricky. The key is to use c1.customer_id < c2.customer_id (not !=) to avoid duplicates. Using != gives you both (A,B) and (B,A). Using < gives you only (A,B).
Q: What is the difference between INNER JOIN and LEFT JOIN? When would you use each in testing?
A: INNER JOIN returns only matching rows from both tables. LEFT JOIN returns ALL rows from the left table and matching rows from the right — with NULL for non-matches. In QA testing, LEFT JOIN is more useful because it helps find missing data. Example: LEFT JOIN orders with payments, then check WHERE payment_id IS NULL to find orders without payments. INNER JOIN would hide those problematic rows — exactly the bugs you are looking for.
Key Point: LEFT JOIN + WHERE IS NULL is the most important pattern in QA — it finds orphans, missing records, and data gaps. Master it.