Part 2 — Querying › Chapter 08

JOINs, semi-joins and anti-joins

INNER, LEFT, FULL, CROSS, self — plus the two joins that have no keyword and are asked about constantly.

From the advert

“Ottima conoscenza del linguaggio SQL” — if there is one thing every SQL interview tests, it is whether you understand joins beyond “INNER keeps matches”.

A join is a filtered cross product

🧒 Simple

Put every customer card next to every order card — every possible pairing, which is a huge pile. Now throw away every pair where the names do not match. What is left is the join. The database does not literally do it that way, but if you think of it that way you will never be surprised by the number of rows you get back.

🎓 Professional

Logically, A JOIN B ON p is the Cartesian product of A and B restricted to rows satisfying p. Outer joins then add back the rows from the preserved side that found no partner, padded with NULL. That is the whole definition, and it explains both the row counts and why NULL behaves the way it does in the result.

Join	Keeps	Typical use
`INNER JOIN`	Only matching pairs	The default. Orders with their customer
`LEFT JOIN`	All left rows; nulls where no match	“…and their orders, if any”
`RIGHT JOIN`	All right rows	Rare. Rewrite as a `LEFT` and swap the tables — it reads better
`FULL OUTER JOIN`	All rows from both sides	Reconciliation: what is in one system and not the other
`CROSS JOIN`	Every pairing	Deliberate: a calendar table × every product, to make gaps visible
self join	A table joined to itself	Employee → manager, hierarchies, comparing a row to its neighbour

The two joins with no keyword

These are asked about constantly and have no dedicated syntax, which is exactly why they separate candidates.

🎓 Semi-join — “does a match exist?”

Returns rows from the left table that have at least one match, without duplicating them and without returning any right-hand columns. Written as EXISTS. A plain INNER JOIN is not the same thing: a customer with three orders comes back three times.

🎓 Anti-join — “where is there no match?”

Rows from the left table with no match. Written as NOT EXISTS, or as a LEFT JOIN … WHERE right.key IS NULL. Do not write it as NOT IN over a nullable column — see the trap below.

The two joins with no keyword: “has at least one” and “has none”, written three ways.

-- SEMI-JOIN: customers who have ordered. One row each, however many orders.
SELECT c.* FROM customers c                 -- only the LEFT table's columns come back
WHERE EXISTS (SELECT 1 FROM orders o        -- SELECT 1 is never evaluated: only existence matters
              WHERE o.customer_id = c.id);  -- correlated: c.id comes from the outer query

-- ANTI-JOIN: customers who have never ordered.
SELECT c.* FROM customers c                 -- same shape…
WHERE NOT EXISTS (SELECT 1 FROM orders o    -- …with the existence test inverted
                  WHERE o.customer_id = c.id);   -- null-safe, unlike NOT IN

-- The same anti-join, the older way. Identical plan in most engines.
SELECT c.* FROM customers c                 -- the same question, no subquery this time
LEFT JOIN orders o ON o.customer_id = c.id  -- pad unmatched customers with NULLs…
WHERE o.id IS NULL;                         -- …then keep only the padded ones

Why NOT EXISTS and not NOT IN

NOT IN (…) expands to a chain of <> joined by AND. One NULL in the subquery makes the last term UNKNOWN, so the whole chain can never be TRUE and the result is empty — not an error, not a warning, just no rows.
NOT EXISTS asks a two-valued question — does a row exist — so NULLs cannot make it UNKNOWN. This is the single most-tested fact in SQL interviews, and it has caused silent data loss in reports that “worked yesterday”.
The LEFT JOIN … IS NULL spelling relies on o.id being NOT NULL in the table: the only way it can be NULL in the result is if the padding put it there. Test a nullable column instead and the anti-join is wrong.

⚠ Trap — NOT IN and a single NULL

If the subquery returns even one NULL, NOT IN returns no rows at all. Not an error — an empty result. The reason is three-valued logic: x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL, and the last term is UNKNOWN, so the whole conjunction can never be TRUE.

NOT EXISTS does not have this behaviour, handles the null case correctly, and usually produces the same or a better plan. Use NOT EXISTS. This is one of the highest-value facts in the whole course: it is asked often, and it has caused silent data loss in a report that "worked yesterday".

Fan-out: the duplicate-row bug

Two one-to-many joins from the same parent multiply, they do not add.

⚠ Trap — the report that is exactly 3× wrong

This is the classic "the totals on the invoice report are wrong" ticket. The instinct is to add DISTINCT, which removes the duplicate rows but does nothing for a SUM, and hides the real bug. The fix is to aggregate each branch before joining, so each side contributes exactly one row per order.

The fix for fan-out: aggregate each child branch first, then join one row to one row.

-- Correct: pre-aggregate each branch, then join one-to-one.
SELECT o.id,                                              -- one row per order, guaranteed
       l.line_total_cents,                                -- already summed, cannot be doubled
       COALESCE(p.paid_cents, 0) AS paid_cents            -- no payments → NULL → show 0
FROM orders o                                             -- the parent: read once
LEFT JOIN (SELECT order_id, SUM(quantity * unit_price_cents) AS line_total_cents  -- branch 1
           FROM order_lines GROUP BY order_id) l          -- ONE row per order_id, by construction
       ON l.order_id = o.id                               -- so this join cannot multiply rows
LEFT JOIN (SELECT order_id, SUM(amount_cents) AS paid_cents  -- branch 2, aggregated the same way
           FROM payments GROUP BY order_id)    p          -- likewise: one row per order_id
       ON p.order_id = o.id;                              -- two one-to-one joins, no fan-out

The arithmetic that goes wrong without this

Joining orders to two one-to-many children at once produces lines × payments rows per order. Two lines and three payments give six rows, so SUM(line_total) counts each line three times and SUM(amount) counts each payment twice. Both totals are wrong, in different directions, and the query returns cheerfully.
SUM(quantity * unit_price_cents) — INTEGER × INTEGER, so INTEGER cents. Watch the width: in SQL Server this sum is an INT and can overflow, and SUM(CAST(… AS BIGINT)) is the fix.
COALESCE(p.paid_cents, 0) — the padded side of a LEFT JOIN is NULL, and NULL is not zero. Without the COALESCE, “unpaid” renders as an empty cell and any arithmetic on it produces NULL.
DISTINCT would remove the duplicate rows and leave the sums wrong. It is the fix that hides the bug.

The three join algorithms

You do not choose these — the optimiser does — but recognising them in a plan is most of what plan reading is.

Algorithm	How it works	Good when	Bad when
Nested loops	For each outer row, seek the inner	Outer input is small and the inner side is indexed	Outer is large — you get millions of seeks
Hash join	Build a hash table on the smaller input, probe with the larger	Big unsorted inputs, equality joins	Needs memory; spills to disk if the estimate was wrong
Merge join	Walk both inputs in sorted order	Both already sorted on the join key	Requires a sort if they are not

✔ The diagnosis this gives you

A nested loops join whose outer input was estimated at 10 rows and actually produced 400 000 is the single most common shape of “this query used to be fast”. The engine chose loops because it expected a small input; the estimate was wrong, so it is now doing 400 000 index seeks. The fix is upstream — statistics, or a predicate the optimiser cannot estimate — not the join itself. See chapter 28.

Counting through an outer join — and the one-character difference that decides whether “none” comes back as 0 or 1.

-- Every customer and their order count, INCLUDING those with none.
SELECT c.name,                              -- the preserved side: every customer appears
       COUNT(o.id) AS n                     -- counts non-NULL ids, so no orders → 0
FROM customers c                            -- every customer, matched or not
LEFT JOIN orders o ON o.customer_id = c.id  -- unmatched customers get a NULL-padded order
GROUP BY c.name                             -- one row per customer name
ORDER BY n DESC;                            -- busiest first; n is the SELECT alias

COUNT(o.id) versus COUNT(*)

COUNT(*) counts rows, and a customer with no orders still has one row — the padded one. It would report 1. This is the most common outer-join bug there is.
COUNT(o.id) counts non-NULL values of that column, and the padding is NULL, so it reports 0. Both return BIGINT, and neither is ever itself NULL.
GROUP BY c.name groups by the name, so two different customers with the same name would merge into one row. GROUP BY c.id, c.name is the honest version — a small thing, and exactly the kind of detail an interviewer is listening for.

A join is a cross product filtered by ON; an outer join adds back the unmatched rows, null-padded.
ON defines the match; WHERE filters the result. A right-table predicate in WHERE turns a LEFT JOIN into an inner join.
Use NOT EXISTS for anti-joins. NOT IN returns nothing at all if the subquery yields a single NULL.
EXISTS is a semi-join: it does not duplicate the left row and does not need DISTINCT.
Joining two one-to-many children of the same parent multiplies rows. Pre-aggregate each branch instead.
DISTINCT as a fix for duplicates hides a fan-out and does not fix the aggregates.
In a LEFT JOIN, COUNT(*) counts unmatched rows as one. COUNT(right.col) counts zero.
Nested loops for small outer inputs, hash for large unsorted ones, merge for pre-sorted. A loops join over a badly estimated outer input is the classic regression.

Interview questions this chapter answers

Difference between WHERE and ON in an outer join?

ON decides which rows pair up; unmatched rows are still preserved and null-padded. WHERE runs after the join and filters the result, so a predicate on the non-preserved table eliminates the null-padded rows and silently converts the outer join to an inner one.

Find customers with no orders.

SELECT c.* FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id). Then add why not NOT IN: a single null in the subquery makes the whole predicate UNKNOWN and returns an empty set.

The totals in this report are three times too high. What happened?

A fan-out: two one-to-many joins from the same parent, so the rows multiply and the parent's value is summed once per combination. The fix is to aggregate each child branch in a derived table or CTE first, then join those one-to-one. DISTINCT would remove the duplicate rows but leave the sums wrong.