Lesson 9
Joining tables: JOIN
For readers who finished lesson 8 and can read a foreign key in a diagram.
- A join pairs rows
- JOIN and ON
- Table aliases
- Three tables
- Grouping joined rows
- A wrong ON
- 5 exercises
Every query runs on real PostgreSQL, in your browser. It downloads once, about 6 MB, then starts in a few seconds.
For a reference page on the same statements, read SQL Joins Explained.
The idea
A join pairs the rows of two tables
An order holds only the id of its customer. A JOIN pairs each order with the customer row whose id matches, so that one row of the answer holds columns of both.
The idea
ON says which rows belong together
Write the second table after join, and after on the condition that pairs a row of one with a row of the other: on c.customer_id = o.customer_id.
That condition is the line in the diagram, the foreign key of lesson 8.
Try it
Show orders with their customer's name
orders o gives the table the short name o, an alias, and o.order_id names the column order_id of that table.
select o.order_id, o.order_date, c.first_name, c.last_name from orders o join customers c on c.customer_id = o.customer_id where o.order_id <= 3;
Each order comes with the name of the customer who placed it.
Watch out
A column that both tables have needs its table
select order_id, customer_id
from orders o
join customers c on c.customer_id = o.customer_id;
ERROR: column reference "customer_id" is ambiguous
orders and customers both have a customer_id, and PostgreSQL will not guess which one you mean. Write o.customer_id or c.customer_id. Here the two are equal anyway, since the join paired them.
The idea
Three tables join one after the other
An order's products sit two steps away: order_items points at the order and at the product. Each extra join … on adds one more table to the rows built so far.
Try it
What did customer 480 buy?
select o.order_id, p.name, oi.quantity from orders o join order_items oi on oi.order_id = o.order_id join products p on p.product_id = oi.product_id where o.customer_id = 480 order by o.order_id, oi.line_no;
16 rows: four orders, four lines each, every line with its product's name.
Try it
Joined rows group like the rows of one table
select d.name, count(*) from employees e join departments d on d.department_id = e.department_id group by d.name;
Six departments with 10 employees each. Legal is missing: it has no employee to pair with, so the join drops it. Lesson 10 keeps such rows.
Watch out
A wrong ON gives wrong rows, not an error
This pairs each order with the customer whose id equals the order's own id:
select count(*)
from orders o
join customers c on c.customer_id = o.order_id;
| count |
|---|
| 500 |
PostgreSQL runs it without complaint: 500 pairs instead of 2,000, each order with somebody else's name. Check that ON follows the foreign key, here o.customer_id.
Exercise 1 of 5
For orders 1 to 5, show the order id and the first and last name of the customer who placed it.
Expected result
It shows once PostgreSQL is ready.
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.
Show answer
select o.order_id, c.first_name, c.last_name from orders o join customers c on c.customer_id = o.customer_id where o.order_id <= 5
Exercise 2 of 5
Show every employee's first and last name, with the name of their department.
Expected result
It shows once PostgreSQL is ready.
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.
Show answer
select e.first_name, e.last_name, d.name from employees e join departments d on d.department_id = e.department_id
Exercise 3 of 5
Row order countsShow the lines of order 7: the line number, the product's name, the quantity and the unit price, sorted by line number.
Expected result
It shows once PostgreSQL is ready.
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.
Show answer
select oi.line_no, p.name, oi.quantity, oi.unit_price from order_items oi join products p on p.product_id = oi.product_id where oi.order_id = 7 order by oi.line_no
Exercise 4 of 5
Show the revenue of each product category: the sum of quantity times unit price over its order lines.
Expected result
It shows once PostgreSQL is ready.
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.
Show answer
select p.category, sum(oi.quantity * oi.unit_price) from order_items oi join products p on p.product_id = oi.product_id group by p.category
Exercise 5 of 5
Row order countsShow the revenue of each customer country, the highest first. Leave out the cancelled orders.
Expected result
It shows once PostgreSQL is ready.
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.
Show answer
select c.country, sum(oi.quantity * oi.unit_price) as revenue from orders o join customers c on c.customer_id = o.customer_id join order_items oi on oi.order_id = o.order_id where o.status <> 'cancelled' group by c.country order by revenue desc