SQL Joins

Joins are where most wrong SQL answers come from, because the query runs fine and returns plausible numbers.

The types. INNER keeps only rows matching on both sides. LEFT keeps every row from the left table, filling NULLs where the right has no match. RIGHT is the mirror and is rarely used, since swapping the tables reads better. FULL OUTER keeps unmatched rows from both. CROSS produces every combination, which is occasionally what you want — generating a date spine, for instance — and otherwise a mistake.

A self join joins a table to itself: employees to their managers, or a row to the previous row before window functions existed.

Mistake one: filtering a left-joined table in WHERE. This is the single most common SQL bug in interviews.

FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.status = 'paid'

Users with no orders get NULL for o.status, and NULL = 'paid' is not true, so those rows are discarded — the LEFT JOIN has silently become an INNER JOIN. If the condition describes which rows to join, it belongs in ON. If it describes which results to keep, it belongs in WHERE.

Mistake two: fan-out. Joining one-to-many multiplies rows. Join orders to line items and each order appears once per item, so SUM(orders.total) now counts that total several times. The result looks like a plausible revenue figure and is wrong. Aggregate the many side first — in a CTE or subquery — then join the single row back.

Anti-joins find rows with no match: LEFT JOIN ... WHERE right.id IS NULL, or NOT EXISTS. Prefer NOT EXISTS over NOT IN, because NOT IN against a set containing a single NULL returns no rows at all — a trap that produces an empty result set with no error.