Joins

Every backend engineer writes joins on day one and gets one subtly wrong in year three: a LEFT JOIN that quietly became an inner join because of a WHERE clause, or a total that doubled because a one-to-many fanned the rows out. This lesson is the join types as the engine sees them, the two mistakes that account for almost every wrong result, and the anti-join, which is the query you write when you want the rows that do not match.

#What a join is

A join is a filtered cross product. Conceptually the engine pairs every row of the left table with every row of the right, then keeps the pairs for which the ON condition is true. The planner never actually builds the cross product, but thinking of it that way explains every result you will ever see.

CREATE TABLE customers (id INT PRIMARY KEY, name TEXT NOT NULL, country TEXT);
CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT REFERENCES customers(id),
                     placed_at TIMESTAMP NOT NULL, total NUMERIC(10,2) NOT NULL);

JOIN ... USING (customer_id) is shorthand for ON a.customer_id = b.customer_id and collapses the column to one in the output. Fine in reports, avoided in application code, where an explicit ON survives a column rename in one table.

#ON versus WHERE, and the inner join in disguise

For an inner join it does not matter where a condition goes. For an outer join it is the whole difference:

SELECT c.name, o.id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.placed_at >= '2026-01-01'
ORDER BY c.name;

A condition in ON decides which right rows pair with each left row. A customer with no 2026 orders is still returned, with NULLs. Now move the date into WHERE:

SELECT c.name, o.id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.placed_at >= '2026-01-01';

WHERE runs after the join. A customer with no orders has o.placed_at = NULL, NULL >= '2026-01-01' is not true, and the row is dropped. The LEFT is still in the text and does nothing. The rule: a condition on the outer side belongs in ON; a condition on the preserved side can go in WHERE. The one exception is the anti-join below, which uses WHERE ... IS NULL on purpose.

#Fan-out from a one-to-many

A customer with three orders produces three rows from the join. That is correct for a list and a disaster for anything aggregated or joined further:

SELECT c.name, SUM(o.total)
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN addresses a ON a.customer_id = c.id     -- two addresses → every order counted twice
GROUP BY c.name;

The sum is right for customers with one address and wrong for everyone else, which is the kind of bug that passes the test data and fails in production. Fixes, in order of preference:

1. Aggregate before you join. Compute SUM(total) per customer in a subquery or CTE, then join the one-row-per-customer result to whatever else you need.
2. Join only what the query needs. The addresses table added nothing to this sum.
3. COUNT(DISTINCT o.id) as a last resort for counts; it does not rescue a sum.

Whenever a query joins two one-to-many relationships off the same parent, ask what the grain of the result is. If you cannot say "one row per X", the aggregates are wrong.

#Anti-joins: the rows that do not match

"Customers with no orders" is the query that every ORM makes hard. Three forms, and they are not equivalent:

-- 1. NOT EXISTS — the correct one, always
SELECT c.* FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
 
-- 2. LEFT JOIN ... IS NULL — equivalent, reads worse, same plan on both engines
SELECT c.* FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
 
-- 3. NOT IN — WRONG the moment the subquery can return a NULL
SELECT c.* FROM customers c
WHERE c.id NOT IN (SELECT customer_id FROM orders);

If any orders.customer_id is NULL, form 3 returns no rows at all: x NOT IN (1, 2, NULL) is x <> 1 AND x <> 2 AND x <> NULL, and the last comparison is unknown, so the whole predicate is never true. The column is a nullable foreign key in half the schemas in the world. Use NOT EXISTS; it is what the planner turns the others into anyway, and it has no NULL trap. The Subqueries lesson returns to this.

The mirror, a semi-join, is EXISTS: "customers with at least one order", without the fan-out a plain join would produce.

#Join order, and what the planner does with it

The order you write joins in is not the order the engine runs them. The planner chooses based on estimated row counts and available indexes, and for inner joins any order gives the same answer. Outer joins constrain it: A LEFT JOIN B LEFT JOIN C must preserve A before anything else. So a query that is slow with three outer joins sometimes becomes fast when two of them are made inner, once you have confirmed the rows really cannot be missing. The Query optimisation lesson covers how to read what it chose.

LATERAL (PostgreSQL; MySQL 8.0.14 as JOIN LATERAL) is the join that can reference the row on its left, which makes "the latest three orders for each customer" a single readable query instead of a window function or a correlated subquery. It is the join to reach for when the right side depends on the left.

#Under the hood: how the engine runs a join

The planner never builds the cross product; it picks one of three physical algorithms per join, and which one it picks decides whether a query takes milliseconds or minutes. A nested loop takes each row of the outer input and probes the inner for matches; with an index on the inner's join column each probe is a few page reads, and with no index it is a full scan per outer row. A hash join reads the smaller input once into an in-memory hash table keyed by the join column, then streams the larger input through it; it needs an equality condition and enough work_mem (PostgreSQL) or join_buffer_size (MySQL, which gained hash joins in 8.0.18) to hold the build side, and spills to disk in batches when it does not. A merge join walks two inputs already sorted on the join key in lockstep, which is the choice when an index or an earlier sort has produced that order.

An outer join changes what the algorithm must do, not just what it returns. A hash left join marks each build-side entry as it is matched and, when the probe finishes, emits the unmatched ones padded with NULLs; that extra pass is why a FULL JOIN needs both sides tracked and is rarer in plans. It also constrains join reordering: for inner joins the planner may join A, B and C in any order (six orders for three tables, and a search over them is where planning time goes on a twelve-table query), but A LEFT JOIN B must be evaluated with A preserved, so an outer join sits where you wrote it unless the planner can prove the rewrite is safe. PostgreSQL's join_collapse_limit (default 8) caps the reordering search; above that many tables it takes your written order as a hint, which is one reason a long report query can improve simply from reordering its FROM clause.

The NULL trap in NOT IN is not a planner quirk but a consequence of how it is evaluated: x NOT IN (subquery) becomes a hashed subplan that returns unknown if the hash contains a NULL and x is not otherwise found, and a WHERE keeps only true. NOT EXISTS becomes an anti-join node, which asks a different question, "is there no matching row", and never touches the value comparison that goes unknown. Both engines plan NOT EXISTS and LEFT JOIN ... IS NULL to the same anti-join; only NOT IN is different, and it is different in semantics, not in speed.

#Walkthrough: the revenue report that was 40% high

A finance dashboard showed monthly revenue that did not match the payment provider's settlement by roughly 40%, always high, never low.

SELECT date_trunc('month', o.placed_at) AS month, SUM(o.total) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN customer_tags t ON t.customer_id = c.id      -- added for a "segment" filter
WHERE t.tag IN ('retail', 'wholesale')
GROUP BY 1;

1. The query had been correct for a year: orders joined to customers, summed by month. A segment filter was added by joining customer_tags, a one-to-many: most customers carried one tag, a growing minority carried both retail and wholesale.
2. Every order for a two-tag customer now appeared twice, once per matching tag row, and SUM counted both. The grain of the result had silently changed from one row per order to one row per order per matching tag.
3. Nobody caught it in review because the SQL was valid, the fixture customers had one tag each, and the number moved in the direction that pleased everyone.
4. The 40% was the share of revenue from dual-tagged customers, which is why the discrepancy grew month on month as the wholesale programme expanded, and why it was never exactly a round factor.
5. The fix was to make the filter a semi-join, which cannot fan out: WHERE EXISTS (SELECT 1 FROM customer_tags t WHERE t.customer_id = c.id AND t.tag IN (...)). The report gained a comment stating its grain, and a fixture customer with two tags whose order total is asserted once.

A join that adds a table to filter by it is the shape to distrust. If the table's columns are not in the SELECT, it should be an EXISTS, and then it cannot multiply anything.

#Try it yourself

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

#Misconceptions

• "The engine builds the cross product and filters it." It picks a nested loop, hash join or merge join; the cross product is the definition of the result, not the method.
• "LEFT JOIN keeps all left rows no matter what." Only until a WHERE on the right table's columns turns it into an inner join. Outer-side conditions belong in ON.
• "NOT IN is just slower than NOT EXISTS." It is wrong in the presence of a NULL, returning no rows; the planner turns both into anti-joins otherwise.
• "Join order in the FROM clause is what runs." Inner joins are reordered freely up to join_collapse_limit; outer joins pin the order.
• "Adding a join to filter is harmless." A one-to-many filter join fans rows out and inflates every aggregate above it; use EXISTS.

#Going deeper

• PostgreSQL manual, "Planner/Optimizer" and "Controlling the Planner with Explicit JOIN Clauses" (join_collapse_limit, from_collapse_limit).
• MySQL reference, "Hash Join Optimization" and "Nested-Loop Join Algorithms".
• Markus Winand, SQL Performance Explained, the join chapter, and use-the-index-luke.com.
• PostgreSQL manual, "Subquery Expressions", for the three-valued logic of IN and NOT IN.
• The LATERAL section of the PostgreSQL SELECT reference, with the top-N-per-group example.