Joins and keys

Real questions need more than one table: "what did Ben order?" lives partly in customers and partly in orders. By the end of this page you can link tables with keys, combine them with JOIN, and check that a lookup uses an index.

The shop's tables and how they link

Lesson 1 introduced primary keys. A foreign key is a column that holds another table's primary key: a pointer to one row over there.

customers               orders                  order_items                products
┌─────────────┐         ┌───────────────┐       ┌──────────────────┐       ┌──────────────┐
│ id       PK │◄──┐     │ id         PK │◄──┐   │ order_id   PK FK │       │ id        PK │
│ name        │   └─────│ customer_id FK│   └───│ product_id PK FK │──────►│ name         │
│ email       │         │ ordered_at    │       │ quantity         │       │ category     │
│ city        │         │ status        │       │ unit_price_cents │       │ price_cents  │
│ joined      │         └───────────────┘       └──────────────────┘       │ stock        │
└─────────────┘                                                            └──────────────┘
PK = primary key   FK = foreign key (points at the PK it is joined to)

In the schema, the link is written like this:

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  ordered_at  TEXT NOT NULL,
  status      TEXT NOT NULL
);

REFERENCES customers(id) says: every customer_id must be the id of a real customer.

One-to-many

One customer can have many orders; each order belongs to one customer. That is a one-to-many relationship, and the foreign key always sits on the "many" side (orders.customer_id).

Here are customer 2's orders:

SELECT id, customer_id, ordered_at, status FROM orders WHERE customer_id = 2 ORDER BY id;

id	customer_id	ordered_at	status
1050	2	2026-04-28 15:38	placed
1087	2	2026-04-22 21:22	delivered
1093	2	2026-03-27 21:51	shipped
1108	2	2026-05-18 15:53	placed
1112	2	2026-05-13 20:30	cancelled

Many-to-many

An order can contain many products, and a product can be in many orders. A many-to-many relationship needs a table in the middle: order_items. Each of its rows says "this order contains this product, this many times, at this price". Its primary key is the pair (order_id, product_id), so the same product cannot appear twice in one order.

The item stores unit_price_cents even though products has a price, because the price can change later. The order must remember what the customer actually paid.

INNER JOIN: rows that match on both sides

JOIN (short for INNER JOIN) puts rows from two tables side by side wherever the ON condition is true.

SELECT o.id, c.name, o.ordered_at, o.status
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE c.id = 2
ORDER BY o.id;

id	name	ordered_at	status
1050	Ben Carter	2026-04-28 15:38	placed
1087	Ben Carter	2026-04-22 21:22	delivered
1093	Ben Carter	2026-03-27 21:51	shipped
1108	Ben Carter	2026-05-18 15:53	placed
1112	Ben Carter	2026-05-13 20:30	cancelled

• orders AS o gives the table a short nickname, an alias. o.id means "the id column of orders". Both tables have an id, so you must say which.
• ON c.id = o.customer_id is the join condition: the foreign key equals the primary key.

What the database does, in pictures:

orders                         customers
1050  customer_id 2 ──────────► 2  Ben Carter   → 1050 | Ben Carter | placed
1087  customer_id 2 ──────────► 2  Ben Carter   → 1087 | Ben Carter | delivered
…

A worked example: what is in order 1001?

The items are in order_items, the product names in products:

SELECT p.name, oi.quantity, oi.unit_price_cents
FROM order_items AS oi
JOIN products AS p ON p.id = oi.product_id
WHERE oi.order_id = 1001;

name	quantity	unit_price_cents
Pencil sharpener	2	149
Metal ruler 30 cm	1	299

Joining three tables

Joins chain. Who are the five best customers, counting only orders that were not cancelled?

SELECT c.name, SUM(oi.quantity * oi.unit_price_cents) AS spent_cents
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY c.id
ORDER BY spent_cents DESC
LIMIT 5;

name	spent_cents
Farid Martin	44319
Dev Byrne	34395
Wen Carter	33993
Isla Nair	30685
Asha Raman	27365

LEFT JOIN: keep rows with no match

An inner join drops rows that have no partner. Four customers in the shop have never ordered, so they never appear in the joins above.

A LEFT JOIN keeps every row from the left table. Where there is no match, the right table's columns are NULL:

SELECT c.name, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.id IN (2, 27)
ORDER BY c.id, o.id;

name	order_id
Ben Carter	1050
Ben Carter	1087
Ben Carter	1093
Ben Carter	1108
Ben Carter	1112
Bea Mehta	NULL

INNER JOIN                       LEFT JOIN
customers ∩ orders               all customers, orders where they exist
  Ben + his 5 orders               Ben + his 5 orders
  (Bea is dropped)                 Bea + NULL

That makes LEFT JOIN the tool for "who has none?". Keep the rows where the right side is NULL:

SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;

name
Bea Mehta
Carlos Okafor
Dina Nair
Eli Raman

The same pattern finds products nobody has ordered (Brush set and Washi tape, 5 rolls).

Count with LEFT JOIN

To count orders per customer including those with none, use COUNT(o.id), not COUNT(*). COUNT(o.id) skips NULL, so a customer with no orders gets 0. COUNT(*) counts the row itself and would give them 1.

Does the database check foreign keys?

MySQL (with its usual InnoDB storage engine) and PostgreSQL refuse a foreign key that points at nothing. SQLite only does so when the connection asks for it. Foreign-key checks are off by default in SQLite, for compatibility with old files, and are turned on per connection:

PRAGMA foreign_keys = ON;

With checks off, this insert succeeds even though there is no customer 999:

INSERT INTO orders (customer_id, ordered_at, status) VALUES (999, '2026-07-01 10:00', 'placed');

With checks on, it fails with FOREIGN KEY constraint failed, and so does deleting a customer who still has orders. You will try this in the lab.

Indexes: why some lookups are fast

To answer WHERE customer_id = 2, the database has two choices:

SCAN: read every row and check it          SEARCH: look it up in an index

orders                                     index on orders.customer_id
1001  6   ✗                                1  → 1006, 1022, 1025, …
1002  19  ✗                                2  → 1050, 1087, 1093, 1108, 1112   ◄ jump here
1003  15  ✗                                3  → …
…     …   (all 120 rows)                   …

An index is a sorted copy of one or more columns, with a pointer back to each row, like the index at the back of a book. The database can jump straight to the right place instead of reading every page. With 120 rows the difference is invisible. With ten million rows it is the difference between milliseconds and minutes.

See the plan with EXPLAIN QUERY PLAN

SQLite tells you how it will run a query if you put EXPLAIN QUERY PLAN in front:

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 2;

The result has four columns. The first three are internal numbers; read the last one, detail:

SCAN orders

SCAN means it reads the whole table. Now add an index and ask again:

CREATE INDEX idx_orders_customer ON orders(customer_id);
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 2;

SEARCH orders USING INDEX idx_orders_customer (customer_id=?)

SEARCH … USING INDEX means it jumps to the matching rows. (Older SQLite versions write SCAN TABLE orders and SEARCH TABLE orders; the meaning is the same.) MySQL, MariaDB and PostgreSQL have the same idea under the keyword EXPLAIN, with their own output.

Some indexes exist without you asking:

• The primary key: WHERE id = 2 on customers gives SEARCH customers USING INTEGER PRIMARY KEY (rowid=?).
• UNIQUE columns: customers.email is UNIQUE, so SQLite made an index for it (sqlite_autoindex_customers_1).

Indexes are not free

• Every INSERT, UPDATE and DELETE must update each index too, so writes get a little slower.
• Each index takes disk space.

Add an index for columns you search, join or sort on often, such as foreign keys. Do not index every column by default. Check with EXPLAIN QUERY PLAN that the index is really used.

Check yourself

QuizJoins and keys

Question 1 of 6

One customer can have many orders. Where does the foreign key go?

Score: 0

What you learned

• A foreign key holds another table's primary key. In one-to-many, it sits on the "many" side; many-to-many needs a table in the middle.
• JOIN … ON combines rows that match. LEFT JOIN keeps every left row, with NULL where nothing matches, which finds "who has none".
• SQLite checks foreign keys only after PRAGMA foreign_keys = ON.
• An index turns a SCAN of every row into a SEARCH. EXPLAIN QUERY PLAN shows which one you get.

Next steps

• Transactions: change several rows safely, all or nothing
• Run queries in Database Manager