Level 2 · Customers who never ordered

LEFT JOIN keeps every row from the left table, whether or not it finds a match. When there is no match, the columns from the right table are filled with NULL:

SELECT c.customer_id, c.email, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id;

A customer with five orders appears five times. A customer with no orders appears once, with order_id NULL. Compare with JOIN, which would drop that customer entirely.

Finding what is missing

Because unmatched rows have NULLs on the right side, you can keep only those:

SELECT c.customer_id, c.email, c.signup_date
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

This pattern is called an anti-join: rows in A with no match in B. Analysts use it constantly: customers who never ordered, products that never sold, sessions with no customer, orders with no shipment.

Test a column that cannot be NULL on a match

Check the right table’s key (o.order_id) or the join column (o.customer_id), never an optional column. If you wrote WHERE o.coupon_code IS NULL, you would also get every customer whose orders simply had no coupon, which is most of them. A primary key is never NULL in a real match, so its NULL means “no match” and nothing else.

Other ways to write it

-- NOT EXISTS: often the clearest, and safe with NULLs
SELECT c.customer_id, c.email, c.signup_date
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id
);

NOT EXISTS is covered in chapter 7. Avoid WHERE customer_id NOT IN (SELECT customer_id FROM orders) when the subquery column can contain NULL: a single NULL makes NOT IN return no rows at all.

RIGHT and FULL joins

RIGHT JOIN is a LEFT JOIN with the tables swapped. Most teams never use it and write the table they want to keep first. FULL OUTER JOIN keeps unmatched rows from both sides, useful for reconciling two sources (“which orders are in the warehouse system but not in billing, and the reverse”). MySQL does not support FULL OUTER JOIN; you emulate it with a UNION of a LEFT and a RIGHT join.

Common mistakes

• Using JOIN and then looking for NULLs. An inner join never produces unmatched rows, so the result is always empty.
• Counting “customers” after a LEFT JOIN with COUNT(*). Customers with several orders are counted several times. Use COUNT(DISTINCT c.customer_id).