Chapter 3 · 8 min

Outer joins and missing rows

An outer join keeps rows from one side even when the other side has no match. The unmatched columns come back as nulls. That is the mechanism behind questions like “customers who have never ordered” and “products that were never sold”.

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

This is an anti-join: the outer join produces nulls for customers with no orders, and the WHERE clause keeps exactly those rows. The same question can be answered with NOT EXISTS, which is usually clearer and does not depend on a null test:

SELECT c.customer_id, c.email
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

WHERE on the outer side undoes the outer join

A predicate on the null-padded table in WHERE filters out the unmatched rows, turning the outer join back into an inner join:

-- Only customers who have at least one paid order.
SELECT c.customer_id, count(o.order_id) AS paid_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
GROUP BY c.customer_id;

o.status is null for customers with no orders, and null = 'paid' is unknown, so those customers disappear. If the intent is “every customer, with a count of paid orders”, move the condition into the ON clause:

LEFT JOIN orders o
  ON o.customer_id = c.customer_id AND o.status = 'paid'

Now the join only matches paid orders, and customers without any still survive with a count of zero. The placement of that predicate is the whole difference between the two answers.

Counting the unmatched side

count(*) counts rows, including the null-padded row produced by an unmatched left join. count(o.order_id) counts only non-null values, so it returns zero for a customer with no orders. When the question asks for “how many orders”, count the right table’s key, not the star.

FULL OUTER and symmetric gaps

FULL OUTER JOIN keeps unmatched rows from both sides. It is useful for reconciliation questions: which records exist in one table but not the other. In most interview prompts, a LEFT JOIN plus an anti-join predicate is enough, and a full outer join is a heavier answer than the question needs. State which side must survive before choosing the join type.