Please look it up on the menu.
Our café now keeps a second table called orders. Each order only writes down the item's number, not its name or price. To see the name, SQL has to look that number up on the menu. Looking things up in another table is called a JOIN.
Two tables, one shared number
Here are both tables. Look at the orange columns: an order's item_id is the same number as the menu's id. That shared number is how the two tables connect.
JOIN looks it up. ON says what must match.
For each order, SQL finds the menu row with the same number and glues the two rows together into one wider row. The ON part is the matching rule.
The dot means “'s”
With two tables, SQL sometimes needs to know which table a column comes from. Both tables have a column called id, so you must write orders.id or menu.id. A name that is only in one table, like customer or price, can be written on its own.
menu.price always works, even where price alone would too.No match, no row. Unless you say LEFT JOIN.
Nobody has ordered a Hot Chocolate, a Bagel or a Fruit Cup yet. A plain JOIN only keeps rows that found a match, so these three items are left out. If you want to keep every row of the first table anyway, use LEFT JOIN. Where there is no match, the other side is filled with NULL, which means “empty”.
FROM menu JOIN orders ON orders.item_id = menu.id
Gives 7 rows, one for each order. The 3 items nobody ordered are not there.
FROM menu LEFT JOIN orders ON orders.item_id = menu.id
Gives 10 rows: the 7 matches, plus Hot Chocolate, Bagel and Fruit Cup with empty order columns.
FROM menu LEFT JOIN orders keeps every menu row.WHERE orders.id IS NULL. Never = NULL: empty is not equal to anything, not even to another empty, so = NULL never matches a single row.JOIN and ON belong to FROM
FROM answers “Where is it?”. With two tables the answer just gets longer: “from the orders, joined to the menu, on matching numbers”. So JOIN and ON go straight after FROM, and our sentence Some Friendly Waiters Gladly Hand Out Lemonade stays the same.
The database also does the joining first, before WHERE. It builds one big joined table, and then everything you already know works on that table as usual.
Every piece has a meaning, even the dot
Please press Next (or click any piece) and watch the sentence grow underneath.
Say each one out loud
Swap each word for what you say, and the code becomes a normal sentence again.
| Write | Say it as | Example | Why it's there |
|---|---|---|---|
| Joining tables | |||
| JOIN | “looking each one up in…” | JOIN menu | Brings in a second table. Each matching pair of rows becomes one wider row. |
| ON | “where … matches …” | ON orders.item_id = menu.id | The matching rule. Without it SQL doesn't know which rows belong together. |
| LEFT JOIN | “keeping every row of the first table” | menu LEFT JOIN orders | Rows with no match stay in, with the other side empty. |
| Symbols | |||
| . | “'s” | menu.id | Says which table a column comes from. Needed when both tables have a column with that name. |
| Empty values | |||
| IS NULL | “is empty” | WHERE orders.id IS NULL | Finds the rows where a LEFT JOIN found no match. Never write = NULL. |
| IS NOT NULL | “is not empty” | WHERE orders.id IS NOT NULL | Keeps only the rows that did find a match. |
INNER JOIN and plain JOIN mean exactly the same thing: only rows that match.Playground: watch the lookups
Please type a query and press Run. Watch each order find its menu row, then see the joined rows build up. You can use orders, menu, or both.
- 1
- 2
- 3
- 4
- 5
- 6
- 7
Order of work: FROM and JOIN build one joined table first, then WHERE, GROUP BY, HAVING, SELECT, ORDER BY and LIMIT work on it as usual.
Five quick questions
Answering from memory is the fastest way to make something stick. Wrong answers explain themselves, so please don't worry.
Your pocket card
Please take a screenshot of this card. Read it once tomorrow and once next week.
JOINLook it up in another table.ONWhich columns must match?.'s: orders.item_id = “the orders' item_id”LEFT JOINKeep every row of the first table.IS NULLIs empty.- Plain JOIN: no match, no row.
- Both tables have
id, so always writeorders.idormenu.id. - Use IS NULL, never = NULL.
- JOIN … ON goes straight after FROM. The joining happens first.
SQL Café is a free course by MASSH AI. Want SQL or data training for your team? Please get in touch.