SQL Café · Lesson 5 of 8

Please look it up on the menu.

So far: SELECTFROMWHEREGROUP BYHAVINGORDER BYLIMITCOUNT SUM AVG MIN MAX Lesson 1 · Lesson 2 · Lesson 3 · Lesson 4

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.

You say to the waiter “Please could you show me each customer and the name of what they ordered, looking up the item numbers on the menu? Thank you!”
↓ same thing in SQL
SELECT customer, item FROM orders JOIN menu ON orders.item_id = menu.id;
Meet the second table

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.

Why not write the name in every order? If the café renamed “Iced Latte” to “Iced Coffee”, you would have to fix it in every single order. Keeping each fact in one place, and connecting tables with a number, keeps data tidy. That is why real databases have many small tables, and why JOIN is one of the most used words in SQL.
The one thing to remember

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.

Order 1 (orders table)customer: Amiraitem_id: 2
Menu row with id 2id: 2item: Cappuccinoprice: £3.50
One joined rowAmira · Cappuccino · £3.50
The new symbol

The dot means “'s”

orders.item_id Say it as “the orders' item_id”, or “the item_id column of the orders table”.

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.

When in doubt, add the table name. menu.price always works, even where price alone would too.
What about rows with no match?

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”.

JOIN Only rows that match 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.

LEFT JOIN Every row of the LEFT table 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.

Which side is “left”? The table you wrote first, straight after FROM. Read the line from left to right: FROM menu LEFT JOIN orders keeps every menu row.
Finding the empty ones: write 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.
Where JOIN goes

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.

SELECT→FROM … JOIN … ON …→WHERE→GROUP BY→HAVING→ORDER BY→LIMIT

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.

Read the code one word at a time

Every piece has a meaning, even the dot

Please press Next (or click any piece) and watch the sentence grow underneath.

Say it out loud
Today's words and symbols

Say each one out loud

Swap each word for what you say, and the code becomes a normal sentence again.

WriteSay it asExampleWhy it's there
Joining tables
JOIN“looking each one up in…”JOIN menuBrings in a second table. Each matching pair of rows becomes one wider row.
ON“where … matches …”ON orders.item_id = menu.idThe matching rule. Without it SQL doesn't know which rows belong together.
LEFT JOIN“keeping every row of the first table”menu LEFT JOIN ordersRows with no match stay in, with the other side empty.
Symbols
.“'s”menu.idSays 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 NULLFinds the rows where a LEFT JOIN found no match. Never write = NULL.
IS NOT NULL“is not empty”WHERE orders.id IS NOT NULLKeeps only the rows that did find a match.
Some people write INNER JOIN. INNER JOIN and plain JOIN mean exactly the same thing: only rows that match.
Your turn

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.

Mission

Ctrl + Enter also runs
Or try one of these
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 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.

Lock it in

Five quick questions

Answering from memory is the fastest way to make something stick. Wrong answers explain themselves, so please don't worry.

Take this with you

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 write orders.id or menu.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.