The sale that names no product
The sales list says product 5 sold three times. A pupil does not know what product 5 is.
The name is in another table. To read both together, the computer has to match the two tables up.
Key words
| Word | What it means |
|---|---|
| Database | an organised store of tables that you can ask questions |
| SQL | the language used to ask a database for data |
| Query | one question written in SQL |
| Join | puts rows from two tables side by side where a key matches |
| Primary key | the column that gives every row in a table its own id |
| Foreign key | a column that holds the primary key of a row in another table |
| ON | the part of a join that says which two columns must match |
| NULL | a gap: the join found no matching row |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can write an inner join, a left join and a right join, and say which rows each one keeps.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Why tables are joined
The products table gives each product a primary key, product_id. The sales table keeps that same number in its own product_id column, as a foreign key.
A join matches the foreign key to the primary key, so each sale can show the product name.
Try it: an inner join
SELECT sales.sale_id, products.name, sales.qty
FROM sales
INNER JOIN products
ON sales.product_id = products.product_id
ORDER BY sales.sale_id;
-- Count the rows. Sale 11 is missing. Why?
INNER JOIN keeps matches only
An inner join keeps a row only when both sides match. Sale 11 points at product 99, which is not in the products table. So sale 11 is dropped. So are products that never sold.
Write table.column so SQL knows which table a column belongs to.
Three joins side by side
| Join | Keeps | Gaps (NULL) appear for | Rows for the shop data |
|---|---|---|---|
| INNER JOIN | Only rows that match on both sides | Nothing | 11 (sale 11 is dropped) |
| LEFT JOIN | Every row of the first table, with matches | Rows of the first table with no match | 14 for products first |
| RIGHT JOIN | Every row of the second table, with matches | Rows of the second table with no match | 12 for sales second |
The cells students fill in are left blank in this preview.
Try it: a left join
SELECT products.name, sales.sale_id
FROM products
LEFT JOIN sales
ON products.product_id = sales.product_id
ORDER BY products.name;
-- Which products have NULL for the sale?