In the last lab, one name took two queries. A join gets it in one:
sql
SELECT orders.id, orders.order_date, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.id;SELECT orders.id, orders.order_date, customers.namecolumns from both tablesFROM ordersthe first tableJOIN customersadd a second tableON orders.customer_id = customers.idpair rows when this is true
JOINnames the second table.ONgives the condition that pairs a row of one table with a row of the other.orders.idandcustomers.namesay which table each column comes from. Both tables have anidcolumn, soidalone is not clear.
Only matching rows
The database tests the ON condition on each pair of rows. It keeps the pairs where the condition is true. Each kept pair becomes one result row, with the columns of both tables.
products
| id | name | category_id |
|---|---|---|
| 1 | Kraken Plush | 1 |
| 5 | Root Hoodie | 2 |
| 7 | Linux Sticker Pack | 3 |
| 12 | Mystery Box | NULL |
categories
| id | name |
|---|---|
| 1 | Plush |
| 2 | Apparel |
| 3 | Stickers |
| 5 | Posters |
products JOIN categories ON products.category_id = categories.id
| products.name | categories.name |
|---|---|
| Kraken Plush | Plush |
| Root Hoodie | Apparel |
| Linux Sticker Pack | Stickers |
- Mystery Box has NULL in
category_id.NULL = 1is never true (Chapter 2), so it has no partner. - No product has
category_id5, so Posters has no partner.
This kind of join is an inner join. JOIN and INNER JOIN mean the same thing.
Filter the joined rows
WHERE comes after the join. It can test a column of either table:
sql
SELECT products.name, products.price
FROM products
JOIN categories ON products.category_id = categories.id
WHERE categories.name = 'Mugs';