Join pitfalls and how to avoid them

Why join bugs are dangerous

A syntax error stops you at once. A join bug does not: the query runs and returns a neat table of wrong numbers, which then goes into a report. This topic collects the usual traps, how to spot each, and how to fix it.

1. Fan-out: rows multiply, totals inflate

When one row matches many rows, it is repeated once per match. That is correct and expected. The trouble starts when you add up a column of the "one" side, or join two different "many" tables to the same row. This is called fan-out.

Say each order can have several items and also several payments (a customer may pay part by UPI and the rest by card). Order 101 has 2 items and 2 payments. Joining both gives 2 × 2 = 4 rows for order 101, and every sum doubles.

SELECT o.id, SUM(oi.qty * oi.price) AS items_total, SUM(p.amount) AS paid
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
JOIN payments AS p     ON p.order_id = o.id
GROUP BY o.id;
-- 101 | 3560 | 3560   <- real values: 1780 and 1780
-- 103 | 2248 | 4496   <- 2 items, so the one payment is counted twice

The fix: aggregate each "many" table first, down to one row per order, and then join the small results. A subquery in FROM (module 6 explains it fully) does this neatly.

SELECT i.order_id, i.items_total, p.paid
FROM (SELECT order_id, SUM(qty * price) AS items_total FROM order_items GROUP BY order_id) AS i
JOIN (SELECT order_id, SUM(amount) AS paid FROM payments GROUP BY order_id) AS p
  ON p.order_id = i.order_id;
-- 101 | 1780 | 1780
-- 103 | 2248 | 2248
-- 105 | 1770 | 1000   <- part-paid, now clearly visible

A milder fan-out: customers → orders → order_items, then COUNT(o.id). Each order is counted once per line, so Asha shows 5 orders instead of 3. COUNT(DISTINCT o.id) fixes counts, but there is no safe "SUM DISTINCT" for money; two different lines can have the same amount. For sums, aggregate first.

2. ON versus WHERE in a LEFT JOIN

You want every customer with their number of Delivered orders, zeros included. Putting the status filter in WHERE runs it after the join. The NULL rows of customers with no Delivered orders fail o.status = 'Delivered' (NULL is not true), so they are removed, and the LEFT JOIN behaves like an inner join.

-- Wrong: only Asha 2 and Ravi 2 come back
SELECT c.name, COUNT(o.id) AS delivered
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'Delivered'
GROUP BY c.id, c.name;

-- Right: the filter decides which orders match; every customer stays
SELECT c.name, COUNT(o.id) AS delivered
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id AND o.status = 'Delivered'
GROUP BY c.id, c.name;
-- Asha 2, Ravi 2, Priya 0, Imran 0, Sneha 0, Arjun 0

The rule: conditions on the right table of a LEFT JOIN go in ON; conditions on the left table (which rows to report on) go in WHERE. For inner joins it makes no difference.

3. Ambiguous and wrong keys

"ambiguous column name: id" means two tables have that column; add the alias prefix. Worse is a wrong key that runs: employees JOIN departments ON d.id = e.id pairs employee 1 with department 1, employee 2 with department 2, and so on. Rahul Sharma lands in Sales. Always join a foreign key to the key it references.

4. NULL keys never match

NULL = NULL is not true, so rows with a NULL join key never match, even with each other. Deepak (dept_id NULL) disappears from an inner join. If such rows matter, use a LEFT JOIN, and show them with COALESCE(d.name, 'No department').

5. USING and NATURAL JOIN

When both tables name the key the same, JOIN payments USING (order_id) is a short form of ON order_items.order_id = payments.order_id, and the column appears once in SELECT *. It is fine to use. NATURAL JOIN goes further and joins on every column with the same name, automatically. Avoid it: customers NATURAL JOIN orders matches on id (customer id = order id) and returns 0 rows, and products NATURAL JOIN order_items matches only on price. If someone later adds a column such as updated_at to both tables, the join silently changes.

Common mistakes

• Summing after joining two one-to-many tables. Aggregate each one first, then join.
• Filtering the right table of a LEFT JOIN in WHERE. Move the condition into ON.
• Using DISTINCT to hide duplicate rows. Find which join fans out instead.
• Using NATURAL JOIN. Write the ON (or USING) explicitly.