sushi&syntax
← SQL
rice · fundamentalslesson 04

JOINs, explained at the counter

What INNER and LEFT JOIN actually keep, shown with two tiny tables.

Two tables. orders is what customers asked for. menu is what the kitchen sells, with prices. A JOIN answers one question: which rows belong together? Everything else is detail.

SELECT orders.item, menu.price
FROM orders
INNER JOIN menu ON orders.item = menu.item;

INNER JOIN: only the matches survive

Watch what happens to each row. maki and nigiri exist in both tables, so they pair up and reach the result. temaki was ordered but is not on the menu; gunkan is on the menu but nobody ordered it. Both get dropped.

ordersmakinigiritemakimenumaki · €4nigiri · €5gunkan · €6INNER JOINmaki · €4nigiri · €5(temaki dropped)INNER JOIN keeps only rows that exist in both tables. No match, no row.ordersmakinigiritemakimenumaki · €4nigiri · €5gunkan · €6INNER JOINmaki · €4nigiri · €5(temaki dropped)INNER JOIN keeps only rows that exist inboth tables. No match, no row.
fig 1 · INNER JOIN: rows without a partner never reach the result

That is the whole idea: an INNER JOIN is an intersection. Only pairs make it through.

LEFT JOIN: the left table always survives

Same query, one word changed:

SELECT orders.item, menu.price
FROM orders
LEFT JOIN menu ON orders.item = menu.item;

Now every row of orders (the left table) is guaranteed a seat. temaki still has no menu match, so its price comes back as null.

ordersmakinigiritemakimenumaki · €4nigiri · €5gunkan · €6LEFT JOINmaki · €4nigiri · €5temaki · nullLEFT JOIN keeps every order, even without a menu match: missing values become null.ordersmakinigiritemakimenumaki · €4nigiri · €5gunkan · €6LEFT JOINmaki · €4nigiri · €5temaki · nullLEFT JOIN keeps every order, even withouta menu match: missing values become null.
fig 2 · LEFT JOIN: unmatched left rows survive, their missing columns become null

How to choose

Ask yourself one question: what should happen to rows without a partner?

  • They should disappear: use INNER JOIN. You only want complete pairs.
  • They should stay: use LEFT JOIN, and put the table you want to keep on the left. Their missing values come back as null.

And one trick for later: after a LEFT JOIN, the rows with null in them are exactly the ones that found no partner. So WHERE menu.price IS NULL finds every order that is not on the menu.

Try this yourself

Sketch (on paper!) what RIGHT JOIN and FULL OUTER JOIN would look like in fig 2. Which plates survive, and where do the nulls end up? Then check your sketch against a real database.