LEFT JOIN plus WHERE: the filter that turns it into an INNER JOIN

Twelve thousand four hundred customers in the table, 8,310 in the report. Nobody filtered on purpose. Someone wrote a LEFT JOIN, then a WHERE on a column of the right table, and the 4,090 customers with no orders fell out. This page covers why, where the condition belongs, the IS NULL that finds the customers you were actually asked about, and the thirty-second count test. The print-ready A4 PDF is at the bottom.

The query

• LEFT JOIN orders o: keep every customer, with or without orders.
• WHERE o.status = 'paid': for a customer with no orders, o.status is NULL. NULL = 'paid' is not true, so the row is gone.

That second line is the whole trap. The LEFT JOIN did its job; the WHERE undid it.

The fix

• AND o.status = 'paid' in the ON clause, with the join. It filters orders, not customers.
• ON, not WHERE, for anything about the right table. The 4,090 customers come back, with order_id NULL.
• WHERE o.id IS NULL, after the ON condition, gives the customers with no paid order.

Same join, two row counts

The condition filters orders in ON. In WHERE it filters the joined rows, and the customers with no orders are joined rows full of NULLs.

WHERE, 8,310 rows:

SELECT c.id, o.id AS order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
WHERE o.status = 'paid';
-- no orders: no row

ON, 12,400 rows:

SELECT c.id, o.id AS order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
 AND o.status = 'paid';
-- no orders: order_id NULL

The customers with no paid order, 4,090 rows:

SELECT c.id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
 AND o.status = 'paid'
WHERE o.id IS NULL;

Remember

• NULL in a WHERE is never true and never false. The row is dropped either way.
• WHERE c.country = 'FR' is fine: a left-table condition belongs in WHERE.
• Count twice: COUNT(*) with and without the WHERE.

The trap: three conditions, three results

The quick test before you send a number: run the query with and without the WHERE and compare COUNT(*). If the LEFT JOIN count equals the INNER JOIN count, the WHERE ate the NULLs.

The quiz

Which one keeps the customers with no orders?

A) SELECT c.id, o.id
   FROM customers c
   LEFT JOIN orders o
     ON o.customer_id = c.id
   WHERE o.status = 'paid';

B) SELECT c.id, o.id
   FROM customers c
   LEFT JOIN orders o
     ON o.customer_id = c.id
    AND o.status = 'paid';

B. In A, a customer with no orders has o.status NULL, and NULL = 'paid' is not true, so the WHERE drops the row: the LEFT JOIN behaves like an INNER JOIN. In B the condition is part of the join, it filters orders only, and the customer stays with o.id NULL.

The four joins themselves are on the SQL joins sheet. This page is only the trap.