lesson 18 Joins & sets · intermediate

Connect tables with JOIN

Connect rows from related tables using matching keys.

the idea

INNER JOIN pairs rows whose join condition matches. Aliases keep column references short. One customer can match several orders, so joins can increase row counts.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

customers4 columns · 8 rows

idinteger · key	nametext	citytext	joined_attext
1	Alice	Chicago	2024-01-10
2	Ben	Austin	2024-02-12
3	Cara	Seattle	2024-03-05
4	Dev	Chicago	2024-04-20
5	Elena	NULL	2024-05-01

showing 5 of 8 rows

orders6 columns · 12 rows

idinteger · key	customer_idinteger	product_idinteger	quantityinteger	statustext	ordered_attext
101	1	1	2	paid	2025-01-01
102	2	3	1	paid	2025-01-02
103	1	2	1	paid	2025-01-05
104	3	5	2	pending	2025-01-08
105	4	4	5	paid	2025-02-01

showing 5 of 12 rows

products4 columns · 6 rows

idinteger · key	nametext	categorytext	pricereal
1	Notebook	Stationery	12
2	Desk lamp	Home	45
3	Keyboard	Tech	80
4	Pen set	Stationery	8
5	Headphones	Tech	120

showing 5 of 6 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;

Watch for: Joining without the correct ON condition can multiply unrelated rows.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
Find the matching keys. orders.customer_id references customers.id. Read both columns so the JOIN condition is concrete before you write it.
step-1.sql
sql›
```
SELECT id AS order_id, customer_id
FROM orders
ORDER BY id
LIMIT 8;
```
2
Combine matching rows with JOIN. INNER JOIN keeps only pairs that satisfy the ON condition. Aliases keep the two id columns clear.
step-2.sql
sql›
```
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;
```