Joins that keep the right rows

A join is a question about rows: which ones survive, and what fills the gaps when there’s no match.

The idea

Two tables, one key. When you join customers to orders, the join type decides the fate of a customer who never ordered. INNER throws them away — only matched rows survive. LEFT keeps every customer and pads the missing order columns with NULL. And an anti-join flips it around to keep only the customers with no match — the “who signed up but never bought” question.

Pick the wrong join and rows quietly appear or vanish. So the real skill is reading a question — “including the ones who never ordered” — and knowing which join keeps them.

How it works

Read a join as a loop. Walk the left table (customers) row by row and try to match it against the right table (orders) on the key. What you do with a customer who finds no match is the whole difference:

for each customer c:
    matches = orders where o.customer_id = c.id
    if matches:  emit one row per match   (Ada -> Book, Ada -> Pen, ...)
    else:        INNER  -> drop c        (Cleo disappears)
                 LEFT   -> emit c, o.* = NULL   (Cleo, product NULL)
                 ANTI   -> keep c, drop the rest (only Cleo survives)

The anti-join is just a LEFT join with a filter that reads “the match failed”:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.customer_id IS NULL;   -- only the no-match rows remain

A self-join is the same machinery pointed at one table twice — to attach each employee to their manager, join employees to employees. Use LEFT so the CEO (whose manager_id is NULL) isn’t dropped:

SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

When to use it

Watch out for

• A WHERE on the right table turns LEFT back into INNER. LEFT JOIN orders o … WHERE o.product = 'Book' drops every NULL-padded customer, because NULL = 'Book' is never true. Put that condition in the ON clause instead, or you’ve quietly rebuilt an inner join.
• Fan-out doubles your sums. Ada has two orders, so she appears twice. Join first, then SUM, and her revenue — or worse, a customer-level fee — is counted twice. Aggregate the many-side before joining, or count distinct.
• NULL never equals anything. o.customer_id = NULL matches nothing. To test for a missing match you must write IS NULL.
• NOT IN with a NULL in the subquery returns nothing at all. If any order has a NULL customer_id, WHERE c.id NOT IN (SELECT customer_id FROM orders) yields zero rows. Prefer NOT EXISTS or the LEFT…IS NULL pattern.
• Forgetting the ON condition is a cross join. No join key means every left row pairs with every right row — 4 customers × 4 orders = 16 rows, all wrong.

Worked example

An interviewer asks: “How many of our customers have never placed an order?” The word never is the tell — it’s an anti-join. Start from customers (the side you must keep all of), LEFT JOIN orders on the customer id, and keep only the rows where the order side came back NULL:

SELECT COUNT(*)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.customer_id IS NULL;   -- answer: 1  (Cleo)

If instead they ask “total spend per customer, including those who’ve spent nothing,” that’s a LEFT JOIN with a NULL-safe total: COALESCE(SUM(o.amount), 0), grouped by customer — so the never-ordered customers land at 0 instead of vanishing. Naming which join keeps the non-matching rows is the answer the interviewer is listening for.