Pay by team, in one question
A finance manager asks: how many people work in each team, and what do we pay each team in total?
The people are in one table. The team names are in another. The answer needs both.
Joining and grouping are how a database answers questions like this one.
By the end of this lesson
Note
I can combine tables and summarise them.
Key words
| Word | What it means | Example |
|---|---|---|
| Join | puts rows from two tables side by side, where a key matches | |
| Alias | a short name you give a column or a table in a query | AS payroll |
| Aggregate | a function that turns many rows into one value | SUM(salary) |
| GROUP BY | makes one result row for each different value | |
| HAVING | keeps only the groups that pass a test | |
| Left join | a join that keeps every row of the first table |
The cells students fill in are left blank in this preview.
A join puts two tables side by side
Data is split over tables so nothing is written twice. A join brings it back together.
The ON clause says which columns match. Usually it is a foreign key on one side and a primary key on the other.
When two tables have a column of the same name, write the table first: suppliers.city.
Run the setup lines. They are the same as before.
Accept the server's certificate
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.
Products and where they come from
sql "SELECT products.name, suppliers.city FROM products JOIN suppliers ON suppliers.id = products.supplier_id WHERE suppliers.country = 'Japan' ORDER BY products.name"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Worked example: matching on the key
| staff.name | staff.dept_id | departments.id | departments.name |
|---|---|---|---|
| Ana | 1 | 1 | Sales |
| Cara | 2 | 2 | Repairs |
| Fay | 3 | 3 | Legal |
| A person with dept_id 9 | 9 | no row | Dropped by a plain JOIN |
The cells students fill in are left blank in this preview.
GROUP BY gives one row per value
An aggregate turns many rows into one value. COUNT(*) counts rows. SUM, AVG, MIN and MAX work on a column.
On its own, an aggregate summarises the whole table. With GROUP BY you get one summary for each different value of a column.
Give the answer a name with AS.
Staff and pay for each job title
sql "SELECT job_title, COUNT(*) AS staff, SUM(salary) AS payroll FROM employees GROUP BY job_title ORDER BY job_title"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Join first, then group
sql "SELECT suppliers.country, COUNT(*) AS items, SUM(products.stock) AS units FROM products JOIN suppliers ON suppliers.id = products.supplier_id GROUP BY suppliers.country ORDER BY suppliers.country"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Try it: summarise by department
-- How many people, and what total pay, in each department?
SELECT dept_id, COUNT(*) AS people, SUM(salary) AS payroll
FROM staff
GROUP BY dept_id
ORDER BY dept_id;
-- Add HAVING COUNT(*) > 1 before ORDER BY.
HAVING tests a group
WHERE tests single rows, before they are grouped. A total does not exist yet at that point.
HAVING tests the groups, after they are made. Use it with an aggregate: HAVING COUNT(*) >= 25.
The order is: WHERE, then GROUP BY, then HAVING, then ORDER BY.
Staff who took 25 or more live orders
sql "SELECT employee_id, COUNT(*) AS orders FROM orders WHERE status <> 'cancelled' GROUP BY employee_id HAVING COUNT(*) >= 25 ORDER BY orders DESC"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 WHERE COUNT(*) >= 25 filters the groups. It is an error, because WHERE runs before any group exists. Use WHERE for rows and HAVING for groups.
The mistake, run for real
sql "SELECT employee_id, COUNT(*) AS orders FROM orders WHERE COUNT(*) >= 25 GROUP BY employee_id"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A left join keeps the unmatched rows
A plain join drops a row with no match. Sometimes the missing match is what you want to find.
LEFT JOIN keeps every row of the first table. Where nothing matches, the other columns are NULL.
Add WHERE ... IS NULL to list only the rows with no match.
Products with no category
sql "SELECT products.name, categories.name AS category FROM products LEFT JOIN categories ON categories.id = products.category_id WHERE categories.id IS NULL ORDER BY products.name"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Try it: people beside their team
-- Put each person beside the name of their department.
SELECT staff.name, departments.name AS dept
FROM staff
JOIN departments ON departments.id = staff.dept_id
ORDER BY staff.name;
-- Add WHERE departments.name = 'Repairs' before ORDER BY.
Going further
In employees, the column manager_id points at another row of the same table. Joining a table to itself is called a self-join.
List every employee's first name beside their manager's first name. Give the table two aliases, such as e and m. The managing director has no manager, so choose the join that still shows her.