Practice: joins
50 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)Join questions in interviews come from a short list: matched rows, every row with a count or total, rows with no match, a table joined to itself, and a report across several tables. Each has a standard shape, and each has one or two classic mistakes.
Interview-style join questions
Most interview join questions map to one shape. Name the shape first, then write the query:
Example 1 · Every customer with the amount spent
SELECT c.customer_id, c.name, COUNT(DISTINCT o.order_id) AS orders, COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS spentFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status <> 'CANCELLED'LEFT JOIN order_items oi ON oi.order_id = o.order_idGROUP BY c.customer_id, c.nameORDER BY spent DESC, c.customer_id;+-------------+--------------+--------+----------+ | customer_id | name | orders | spent | +-------------+--------------+--------+----------+ | 1 | Asha Rao | 3 | 68834.00 | | 6 | Arjun Mehta | 1 | 64990.00 | | 4 | Kabir Singh | 2 | 27196.00 | | 8 | Rahul Verma | 1 | 19298.00 | | 2 | Ravi Kumar | 3 | 8741.00 | | 5 | Priya Sharma | 1 | 5196.00 | | 9 | Sneha Patil | 1 | 1300.00 | | 7 | Divya Nair | 1 | 898.00 | | 3 | Meena Iyer | 0 | 0.00 | | 10 | Imran Khan | 0 | 0.00 | +-------------+--------------+--------+----------+
What this code does
- All 10 customers appear: the status test sits in
ON, and the second join is also aLEFT JOIN, so nobody is dropped. Meena (only a cancelled order) and Imran (no orders) show 0. COUNT(DISTINCT o.order_id)counts orders, not item lines, andCOALESCEturns theNULLsum into 0.
- "Show X with its Y" → inner join on the key.
- "Every X with its count or total, including zero" →
LEFT JOIN,COUNT(right.pk),COALESCE(SUM(...), 0). - "X with no Y" →
LEFT JOIN ... WHERE right.pk IS NULL, orNOT EXISTS. - "Employee and manager", "earns more than manager", "pairs" → self join.
The hardest version combines them: every customer with the amount spent on orders that were not cancelled, showing 0 for everyone else. That needs a left join chain, the status test in ON, and COALESCE around the sum.
Predict row counts
Predicting the row count before you run a join is the fastest way to catch a wrong join. The rules:
Example 2 · Predict, then check: four row counts
-- Predict each number before you run this block.
-- 1. Every customer with their ordersSELECT COUNT(*) AS customers_left_ordersFROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id;
-- 2. Every order with its itemsSELECT COUNT(*) AS orders_join_itemsFROM orders o JOIN order_items oi ON oi.order_id = o.order_id;
-- 3. Every customer with every categorySELECT COUNT(*) AS customers_cross_categoriesFROM customers c CROSS JOIN categories cat;
-- 4. Every department with its employeesSELECT COUNT(*) AS departments_left_employeesFROM departments d LEFT JOIN employees e ON e.dept_id = d.dept_id;+-----------------------+ | customers_left_orders | +-----------------------+ | 16 | +-----------------------+ +-------------------+ | orders_join_items | +-------------------+ | 25 | +-------------------+ +----------------------------+ | customers_cross_categories | +----------------------------+ | 70 | +----------------------------+ +----------------------------+ | departments_left_employees | +----------------------------+ | 13 | +----------------------------+
What this code does
- 1: 15 orders plus 1 row for Imran = 16. 2: one row per item = 25. 3: 10 × 7 = 70.
- 4: 12 employees have a department, plus 1 row for Research = 13. Tanvi (no department) is on the right side, so the left join does not keep her.
- Inner join, child to parent on a valid key: one row per child row.
- Left join, parent to child: matched child rows plus one row per parent with no child.
- Cross join or missing condition: rows of one table × rows of the other.
ShopLite has 10 customers, 15 orders and 25 order items; the company has 5 departments and 13 employees. If your prediction and the real count disagree, either your picture of the data or your join is wrong, and both are worth knowing.
Predict the output
Predict the row counts
Work out the answer before you run the query.
Round 1 of 2
10 customers and 7 categories.
Fix wrong joins
Wrong joins almost always come from five mistakes, and none of them raises an error:
Example 3 · Fix: the last order date of every customer
-- Wrong: the status test in WHERE drops Meena and ImranSELECT c.name, MAX(o.order_date) AS last_orderFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_idWHERE o.status <> 'CANCELLED'GROUP BY c.customer_id, c.nameORDER BY c.customer_id;
-- Right: the status test decides what matchesSELECT c.name, MAX(o.order_date) AS last_orderFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status <> 'CANCELLED'GROUP BY c.customer_id, c.nameORDER BY c.customer_id;+--------------+------------+ | name | last_order | +--------------+------------+ | Asha Rao | 2025-04-12 | | Ravi Kumar | 2025-06-01 | | Kabir Singh | 2025-05-02 | | Priya Sharma | 2025-03-03 | | Arjun Mehta | 2025-03-28 | | Divya Nair | 2025-04-04 | | Rahul Verma | 2025-04-25 | | Sneha Patil | 2025-05-09 | +--------------+------------+ +--------------+------------+ | name | last_order | +--------------+------------+ | Asha Rao | 2025-04-12 | | Ravi Kumar | 2025-06-01 | | Meena Iyer | NULL | | Kabir Singh | 2025-05-02 | | Priya Sharma | 2025-03-03 | | Arjun Mehta | 2025-03-28 | | Divya Nair | 2025-04-04 | | Rahul Verma | 2025-04-25 | | Sneha Patil | 2025-05-09 | | Imran Khan | NULL | +--------------+------------+
What this code does
- The first result has 8 rows. Imran's row has
NULLstatus and Meena's only order is cancelled;WHEREremoves both, so the report says nothing about them. - With the test in
ON, all 10 customers appear, andNULLinlast_orderhonestly says "no live order".
- A right-table test in
WHEREafter a left join (it becomes an inner join). COUNT(*)after a left join (empty rows count as 1).- A missing or wrong
ONcondition (rows multiply). - A later inner join after a left join (kept rows vanish).
The fifth is double counting after a one-to-many join. The habit that catches all five: run COUNT(*) on the join without grouping, look at a few raw rows, and only then add GROUP BY.
Fix the error
Fix the wrong count
Pick the line that makes the report correct.
Imran Khan has no orders but shows 1.
Error
No error, but Imran Khan shows 1 order instead of 0.
Mixed review
Mixed reports use several patterns in one query. A city report, for example, counts every customer (a left join keeps those without orders), counts customers who did order (COUNT(DISTINCT o.customer_id) ignores the NULLs), and totals revenue (COALESCE for cities with none).
Example 4 · A city report from several patterns
SELECT c.city, COUNT(DISTINCT c.customer_id) AS customers, COUNT(DISTINCT o.customer_id) AS buyers, COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS revenueFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'DELIVERED'LEFT JOIN order_items oi ON oi.order_id = o.order_idGROUP BY c.cityORDER BY revenue DESC, c.city;+-----------+-----------+--------+----------+ | city | customers | buyers | revenue | +-----------+-----------+--------+----------+ | Mumbai | 1 | 1 | 64990.00 | | Bengaluru | 1 | 1 | 64436.00 | | Delhi | 2 | 1 | 27196.00 | | Pune | 2 | 1 | 5196.00 | | Chennai | 2 | 1 | 1947.00 | | Kochi | 1 | 1 | 898.00 | | Hyderabad | 1 | 0 | 0.00 | +-----------+-----------+--------+----------+
What this code does
customerscounts every customer of the city (the left joins keep them all);buyerscounts only customers with a delivered order, becauseCOUNT(DISTINCT ...)skipsNULLs.- Chennai has 2 customers but 1 buyer (Meena's order was cancelled). Hyderabad's only customer never ordered, so it shows 0 buyers and 0 revenue instead of disappearing.
Build such a query in layers: first the FROM and joins with SELECT * on a few rows, then the WHERE, then GROUP BY and the aggregates, checking the numbers by hand for one group. One correct group is strong evidence that the rest are correct too.
Order of execution
Put the query clauses in order
Every report query follows this fixed order.
Next step
Check your understanding
12 quick questions