7.2.1 — Queries and Joins

A report is meant to list every customer with the value of their refunded orders, including customers who never had a refund. Someone writes this:

SELECT c.name, COUNT(o.id)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'refunded'
GROUP BY c.name;

Customers with no refunds have vanished. The LEFT JOIN was supposed to keep them.

It did keep them — and then the WHERE threw them away. That one bug is the best entry point into how a query is actually evaluated, because once the evaluation order is clear the bug stops being surprising and becomes obvious.

1. The order a query is evaluated in

SQL is written in an order that has almost nothing to do with the order it runs in. SELECT is written first and evaluated sixth.

1. FROM + JOIN     build the working set of rows
2. WHERE           filter individual rows
3. GROUP BY        collapse rows into groups
4. HAVING          filter groups
5. SELECT          compute the output columns (and column aliases)
6. DISTINCT        remove duplicate output rows
7. ORDER BY        sort
8. LIMIT / OFFSET  take a slice

This is the logical processing order. The engine is free to execute differently as long as the answer matches — that freedom is the algebra from Chapter 7.1 section 7 — but the meaning of your query is defined by this order.

Three everyday facts fall straight out of it:

You cannot use a SELECT alias in WHERE. The alias does not exist yet at step 2.

SELECT price * qty AS line_total FROM order_lines
WHERE line_total > 100;      -- error: column "line_total" does not exist

You can use it in ORDER BY, because step 7 comes after step 5. (MySQL and PostgreSQL also allow it in GROUP BY, which is a friendly extension rather than standard behaviour.)

WHERE filters rows; HAVING filters groups. WHERE qty > 0 runs before grouping, so it decides which rows are counted. HAVING SUM(qty) > 100 runs after, so it decides which groups survive. Putting an aggregate in WHERE is an error, and putting a plain row condition in HAVING works but is slower, because you have grouped rows you were going to discard.

Now the opening bug reads itself. Step 1 keeps every customer, filling refund columns with NULL for customers with no matching order. Step 2 evaluates o.status = 'refunded' on those rows, where o.status is NULL, which is unknown, which is not true — so the row is dropped. A WHERE clause on the right-hand table of a LEFT JOIN turns it into an inner join.

The fix is to move the condition into the join, where it decides what matches rather than which rows survive:

SELECT c.name, COUNT(o.id) AS refunds
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
 AND o.status = 'refunded'      -- (1)
GROUP BY c.name;

(1) This is part of the match test, so a customer with no refunded orders still produces one row with all o.* columns null. COUNT(o.id) counts non-null values, so those customers correctly show 0. COUNT(*) would show 1 — it counts rows, and the row exists. That distinction is the second half of this bug, and it is the one people get wrong even after fixing the first half.

2. The join types, and what each keeps

The four join types differ only in which unmatched rows survive. Everything else about them is identical.

INNER JOIN keeps only rows that matched on both sides. This is the default; the word INNER is optional and worth writing anyway, because it makes the intent explicit next to LEFT JOIN in the same query.

LEFT OUTER JOIN keeps every row of the left table, filling right-hand columns with NULL where nothing matched. This is how you ask "and show me the ones with nothing".

RIGHT OUTER JOIN is the mirror. Most style guides say to avoid it: reading a query where some joins keep the left and others keep the right is needlessly hard. Swap the tables and use LEFT.

FULL OUTER JOIN keeps unmatched rows from both sides. Its main real use is reconciliation — comparing two sources and finding what exists in one and not the other. MySQL does not have it; you emulate it with LEFT JOIN … UNION … RIGHT JOIN.

CROSS JOIN pairs every row with every row. It is occasionally deliberate — generating a grid of dates by products — and is far more often an accident. Omitting the ON clause of a join produces one, and a query that ran in 20 ms against test data can consume the whole server against production data.

A self-join is a table joined to itself, and it is how you compare rows within one table.

-- Every employee alongside their manager's name.
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;   -- (1)

(1) Two aliases for the same table make it two logical tables. LEFT is deliberate: the chief executive has no manager, and an inner join would delete them from the report. This is the same shape as the opening bug, seen from the other side.

3. The join condition is not always equality

Almost every join you write is an equi-join — matching on =. But the condition is an arbitrary boolean, and non-equality joins solve problems that otherwise need application code.

-- Which price band does each order fall into?
SELECT o.id, b.label
FROM orders o
JOIN price_bands b
  ON o.total_minor >= b.min_minor
 AND o.total_minor <  b.max_minor;    -- (1)

(1) A range join. Each order matches exactly one band because the bands are built to be non-overlapping and half-open — >= on the low end, < on the high end. If the bands overlap by one penny, orders on the boundary match twice and the row count silently doubles, which is why half-open ranges are the standard way to express any band, including time windows.

4. Row multiplication — the bug that inflates every total

This is the most damaging join mistake, because the query runs, returns plausible numbers, and is wrong.

An order has 3 lines and 2 shipments. Join both to the order and you get 3 × 2 = 6 rows, because the join produces every combination.

-- WRONG: totals are inflated
SELECT o.id, SUM(l.qty) AS items, SUM(s.weight_g) AS weight
FROM orders o
JOIN order_lines l ON l.order_id = o.id
JOIN shipments   s ON s.order_id = o.id
GROUP BY o.id;

Each line's qty now appears twice (once per shipment), and each shipment's weight appears three times. items is double the truth and weight is triple it. Nothing errors.

Two correct shapes:

-- Fix A: aggregate before joining
SELECT o.id, l.items, s.weight
FROM orders o
LEFT JOIN (SELECT order_id, SUM(qty) AS items
           FROM order_lines GROUP BY order_id) l ON l.order_id = o.id   -- (1)
LEFT JOIN (SELECT order_id, SUM(weight_g) AS weight
           FROM shipments GROUP BY order_id) s ON s.order_id = o.id;

-- Fix B: count distinct, when the shape is simple
SELECT o.id, SUM(DISTINCT l.id) ...   -- (2)

(1) Each subquery collapses to one row per order before the join, so no multiplication can happen. This is the reliable fix and the one to reach for. (2) COUNT(DISTINCT …) works for counting and is a trap for summing — SUM(DISTINCT qty) adds each distinct value once, so two lines of quantity 2 sum to 2. Use fix A.

The rule to carry: joining two one-to-many children of the same parent multiplies rows. Whenever a query has two JOINs that both fan out from the same table and an aggregate anywhere, check it against a single order by hand.

5. Semi-joins and anti-joins: EXISTS, IN, NOT EXISTS

Often you do not want columns from the other table, only the answer to "is there one?". That is a semi-join (has a match) or an anti-join (has none).

-- Customers who have ordered at least once
SELECT c.* FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);   -- (1)

-- Customers who never ordered
SELECT c.* FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);  -- (2)

(1) EXISTS stops at the first matching row — it never counts them. SELECT 1 is idiomatic because the columns are ignored entirely; SELECT * is equally fine and equally fast, and the choice is style. Crucially, a semi-join returns each customer once no matter how many orders they have, so unlike a JOIN it cannot multiply rows. (2) The anti-join, and it is the safe one.

Why NOT EXISTS and not NOT IN. From Chapter 7.1 section 3: if the subquery returns even one NULL, NOT IN returns nothing at all, because x <> NULL is unknown and the AND chain can never be true.

-- Silently returns ZERO rows if any order has a NULL customer_id
SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);

NOT EXISTS is unaffected because it asks a different question — does a matching row exist — and a null simply fails to match. Default to EXISTS / NOT EXISTS. Modern optimisers usually produce identical plans for IN and EXISTS on non-null columns, so this is a correctness preference, not a performance one.

IN with a literal list is fine and common. The caveat is size: a list of 50,000 values generated by an application is a query the planner handles badly and that may exceed statement limits. At that size, put the values in a temporary table or use = ANY(array) in PostgreSQL.

6. Subqueries, derived tables and lateral joins

A scalar subquery returns one row and one column and can sit anywhere a value can.

SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS orders
FROM customers c;

This is a correlated subquery: it mentions c.id from the outer query, so conceptually it runs once per outer row. Modern planners often rewrite it into a join, but "conceptually once per row" is the right mental model, and it is why a correlated subquery over a large outer set with no supporting index is a reliable way to make a query 1,000× slower.

A derived table is a subquery in FROM, and it is just a table you built inline — the fix-A shape above.

A CTE (common table expression) is the same thing with a name at the top, covered properly in Chapter 7.2.2 along with the recursive form.

A lateral join is the one that unlocks "top N per group". Normally a subquery in FROM cannot see columns from tables to its left. LATERAL (PostgreSQL, SQL Server's CROSS APPLY) lifts that restriction.

-- The 3 most recent orders for each customer
SELECT c.name, o.id, o.placed_at
FROM customers c
CROSS JOIN LATERAL (                          -- (1)
  SELECT id, placed_at FROM orders
  WHERE customer_id = c.id                    -- (2)
  ORDER BY placed_at DESC
  LIMIT 3
) o;

(1) LATERAL means the subquery is evaluated once for each row of c. (2) This reference to c.id is exactly what a plain derived table cannot do. Use LEFT JOIN LATERAL … ON true instead of CROSS JOIN LATERAL if you want customers with no orders to survive.

The window-function alternative to this is in Chapter 7.2.2, and it wins when you want all rows ranked; LATERAL wins when you want a small N per group, because it can stop after N using an index.

7. Set operations

SELECT sku FROM in_stock
UNION       SELECT sku FROM on_order   -- distinct rows from both
UNION ALL   SELECT sku FROM on_order   -- everything, duplicates kept
INTERSECT   SELECT sku FROM on_order   -- in both
EXCEPT      SELECT sku FROM on_order;  -- in the first, not the second

The rules: both sides need the same number of columns with compatible types, the column names come from the first branch, and ORDER BY applies to the whole result and goes at the very end.

UNION deduplicates, and deduplication is not free — the engine sorts or hashes the entire result. If you know the branches cannot overlap, UNION ALL is strictly faster and is the correct choice. This is the single most common easy win in a slow reporting query.

8. How a join actually runs

The planner picks one of three algorithms. Knowing them turns Chapter 7.2.3's execution plans from noise into a diagnosis.

Nested loop join. For each row of the outer table, look up matches in the inner table. Cost is roughly outer rows × cost of one inner lookup. Excellent when the outer side is small and the inner side has an index on the join column — this is the plan behind almost every fast lookup query. Catastrophic when the outer side is large and the index is missing, because then it is a full scan per row.

Hash join. Build a hash table from the smaller side in memory, then scan the larger side probing it. Roughly linear in the two table sizes, needs no index, and is the workhorse for joining two large tables. Its weakness is memory: if the hash table does not fit in the working memory the engine spills to disk in batches, and the query slows sharply. It only works for equality conditions.

Merge join. Sort both sides on the join key, then walk them together like merging two sorted lists. Free when both inputs are already sorted — typically because both are being read in index order — and expensive otherwise, because it pays for two sorts.

	Nested loop	Hash	Merge
Needs index	Yes, to be fast	No	Helps a lot
Equality only	No	Yes	Mostly
Memory	Tiny	Large	Sort space
Best when	Small outer set	Two big tables	Inputs already sorted

A nested loop over a large outer table is the classic slow plan, and it usually means either a missing index or a bad row estimate. That is the diagnosis Chapter 7.2.3 makes concrete.

9. Habits that prevent the common bugs

Alias every table and qualify every column. SELECT id FROM orders o JOIN customers c … is ambiguous the moment both have id. Fully qualified columns also survive someone adding a column to the other table.

Never SELECT * in application code. It breaks when a column is added, it fetches large columns you did not want, and it defeats a covering index (Chapter 7.3.2). It is fine when exploring by hand.

Write the ON clause immediately after the JOIN, before you write anything else. Most accidental cross joins happen in a query that was edited, not in one that was written.

When a total looks slightly wrong, count the rows before grouping. Delete the GROUP BY, add the primary key, and look at one entity. Row multiplication is visible in two seconds that way and invisible in the aggregate.

What the interviewer will push on

"What is the difference between WHERE and HAVING?" WHERE filters rows before grouping, HAVING filters groups after. The tell is going further: because of that ordering, a WHERE on the right-hand table of a LEFT JOIN turns it into an inner join, and the fix is to move the condition into ON. That connects the rule to a bug they have seen.

"Why did my SUM double?" Two one-to-many joins from the same parent multiply rows. Show the 3 lines × 2 shipments = 6 arithmetic, then give the fix: aggregate each child to one row per parent in a derived table before joining. The wrong answer is SUM(DISTINCT …), which quietly drops equal values.

"IN or EXISTS?" For correctness, NOT EXISTS over NOT IN, because a single NULL in the subquery makes NOT IN return zero rows. For performance they are usually planned identically on non-null columns, so anyone who claims a large speed difference as the main answer has memorised an old rule.

"How would you get the three most recent orders per customer?" Two valid answers: a window function with ROW_NUMBER() … PARTITION BY filtered to <= 3, or a LATERAL join with ORDER BY … LIMIT 3. The tell is knowing why you would pick each — LATERAL can stop early using an index, the window function ranks everything.

"Which join algorithm would you expect here, and why?" Small filtered outer set with an indexed inner table gives a nested loop; two large tables with no useful index gives a hash join; both sides already ordered by the key gives a merge join. Then name the failure: a nested loop over millions of outer rows is the plan you are looking for when a query got slow after the data grew.

One thing to volunteer: point out that COUNT(*) and COUNT(column) differ on the outer side of a LEFT JOIN — COUNT(*) counts the null-filled row and reports 1, COUNT(o.id) reports 0. It is the second half of the left-join bug and almost nobody mentions it unprompted.

Recall

• Logical order: FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. That is why a SELECT alias works in ORDER BY and not in WHERE.
• A WHERE on the right table of a LEFT JOIN makes it an inner join. Put the condition in ON instead. Then use COUNT(o.id), not COUNT(*), or the zero comes back as one.
• Join types differ only in which unmatched rows survive. A missing ON clause is a CROSS JOIN, and it is the fastest way to melt a server.
• Two one-to-many joins from the same parent multiply rows and silently inflate every aggregate. Aggregate each child to one row per parent first.
• EXISTS / NOT EXISTS are semi-joins and anti-joins: they never multiply rows, and NOT EXISTS is immune to the NULL that makes NOT IN return nothing.
• A correlated subquery conceptually runs once per outer row; a lateral join is the supported way to do that on purpose, and is the clean answer for top-N-per-group.
• UNION deduplicates by sorting or hashing the whole result. If the branches cannot overlap, UNION ALL is free speed.
• Three join algorithms: nested loop (small outer set, indexed inner), hash (two large tables, equality only, memory-hungry), merge (inputs already sorted). A nested loop over a huge outer table is the classic slow plan.

Self-test: Why can't you filter on a SELECT alias in WHERE? · Rewrite a broken LEFT JOIN + WHERE correctly · Why did joining lines and shipments triple the weight? · What makes NOT IN return zero rows? · When does LATERAL beat a window function? · Which join algorithm needs an index to be fast, and which needs memory?

Next: 7.2.2 covers aggregation properly — GROUP BY semantics, window functions as the tool that computes a total without collapsing the rows, and CTEs including the recursive form that walks a hierarchy.