The four joins, and what each one is for

One mechanism, four decisions about the leftovers

Every join does the same thing: for each row on the left, find rows on the right where the ON condition holds, and emit one output row per match. The four join types differ in a single respect: what happens to rows that found nothing.

• INNER JOIN: unmatched rows on either side are dropped.
• LEFT JOIN: every left row survives; unmatched ones get NULLs on the right.
• RIGHT JOIN: the mirror image. Every right row survives.
• FULL OUTER JOIN: everything from both sides survives, with NULLs wherever there was no match.

That is the whole taxonomy. There is nothing else to memorise.

When each is the right choice

INNER is right when the relationship is guaranteed and you want it enforced. orders joined to customers on a NOT NULL foreign key should match every time; if the row count drops, you have found a data problem and you want to know.

LEFT is right whenever the left table is your population. “All students, with their payments” is a left join, because a student who has never paid is still a student and must appear in the report. This is the join you will write most often, and the reason is worth internalising: the left table defines who is in the answer, and the right table only adds detail.

RIGHT is rare in practice. Anything a right join does, a left join with the tables swapped does more readably. Its main appearance in real code is accidental, when somebody reorders a query and does not adjust the join type.

FULL OUTER has one great use: reconciliation. When you have two systems that should agree and want to see everything that does not line up:

SELECT
  coalesce(a.reference, b.reference) AS reference,
  a.amount AS in_ledger,
  b.amount AS in_bank
FROM ledger a
FULL OUTER JOIN bank b ON b.reference = a.reference
WHERE a.reference IS NULL       -- in the bank, missing from the ledger
   OR b.reference IS NULL       -- in the ledger, missing from the bank
   OR a.amount <> b.amount;     -- present in both, disagreeing

Three failure modes in one result set. MySQL has no FULL OUTER JOIN; the standard workaround is a LEFT JOIN unioned with a RIGHT JOIN.

The join that has no ON clause

CROSS JOIN pairs every left row with every right row. Ten rows against twelve gives 120. It is deliberate and useful for generating grids (every product against every month) and it is also what you get by accident when you write an old-style comma join and forget the WHERE:

SELECT * FROM orders, customers;   -- 31,940 x 8,200 = 261 million rows

Use explicit JOIN ... ON syntax always. It puts the join condition next to the join, so a missing condition is a syntax error instead of a query that runs for an hour.

USING and NATURAL, and why to be careful

USING (customer_id) is shorthand for ON a.customer_id = b.customer_id, and it merges the two columns into one in the output. It is tidy and safe.

NATURAL JOIN joins on every column with a matching name in both tables. It reads beautifully and is a trap: the day somebody adds a created_at column to both tables, your natural join silently starts requiring those timestamps to be equal, and your result becomes empty. Nothing errors. Do not use it in anything that has to keep working.

Reading a join out loud

The habit that makes joins stop being confusing is to narrate each one in a fixed shape:

For each order, find the customer whose id matches, of which there is exactly one, and it is guaranteed to exist.

Three facts: the left thing, the right thing, and the multiplicity. If the multiplicity is “one, guaranteed”, use INNER and expect the row count to stay the same. If it is “at most one”, use LEFT and expect the row count to stay the same. If it is “possibly many”, the row count will grow and you must decide what to do about that before you write an aggregate.

The count that checks your work

SELECT count(*) FROM orders;                    -- 31,940
SELECT count(*) FROM orders o
JOIN customers c ON c.id = o.customer_id;       -- 31,940 → good

Same number: every order matched exactly one customer. Fewer: an inner join dropped orders with a missing or non-matching customer, and you should find out how many and why. More: a customer id appears more than once in customers, which means either the table’s grain is not what you thought or there are duplicates, and either way your revenue total is now too big.

Run this comparison every time. It is two queries, it takes ten seconds, and it turns join bugs from something you discover in a meeting into something you discover while writing.