Who is supplier 5?
A customer asks who makes the kite they are buying. The product list on the till says supplier 5.
That number means nothing to the customer. The name of supplier 5 is in another table.
A database can put the two tables together and answer in one go.
By the end of this lesson
Note
I can join two tables to answer one question.
Key words
| Word | What it means | Example |
|---|---|---|
| Foreign key | a field that holds a primary key from another table | supplier_id |
| JOIN | puts matching records from two tables side by side | |
| ON | says which two fields must match | |
| Dot name | a field named with its table in front | products.name |
The cells students fill in are left blank in this preview.
Why the shop uses two tables
Each supplier is written down once, in the suppliers table, with its own id.
A product does not copy the supplier's name and city. It holds only the supplier's number, in supplier_id. That field is a foreign key.
Sign in, then look at the toys with only their supplier numbers.
Let the terminal trust the server
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make a short name for the shop
alias sql='mariadb -h db -u student -pstudent shop -t -e'Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Toys with their supplier numbers
sql "SELECT name, supplier_id FROM products WHERE category_id = 8 ORDER BY name"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
JOIN puts matching records side by side
JOIN suppliers brings in the second table. ON suppliers.id = products.supplier_id says how to pair them: a product goes with the supplier whose id matches its supplier_id.
Both tables have a field called name, so each field is written with its table in front, such as products.name.
Toys with their supplier names
sql "SELECT products.name, suppliers.name AS supplier FROM products JOIN suppliers ON suppliers.id = products.supplier_id WHERE products.category_id = 8 ORDER BY products.name"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Dear products with their category
sql "SELECT products.name, categories.name AS category FROM products JOIN categories ON categories.id = products.category_id WHERE products.price > 50 ORDER BY products.name"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A foreign key guards the link
The database checks every foreign key. There is no supplier 99, so a product for supplier 99 would point at nothing.
This Run tries to add one. The database refuses it, so the shop is not changed.
Add a product for supplier 99
sql "INSERT INTO products (name, supplier_id, price) VALUES ('Phone Case', 99, 9.99)"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A common mistake
Warning
Many people think JOIN knows which records belong together. Without ON, the database pairs every product with every category. Always say which fields must match.
Join with no ON on purpose
sql "SELECT COUNT(*) AS pairs FROM products JOIN categories"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
The same join with ON
sql "SELECT COUNT(*) AS matched FROM products JOIN categories ON categories.id = products.category_id"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Going further
The join with ON matched 35 products, but the shop has 37. Two products have no category.
Change JOIN to LEFT JOIN in that count. A left join keeps every product, even one with no match. What number comes back now?