Reference · module 5
Joining tables
Everything this module explains, on one page.
JOIN
Put split tables back together.
- Data is split across tables so each fact is stored once: customers holds names, orders holds a customer_id pointing at a customer. Change a name once, and every order sees it.
- JOIN puts the tables back together: FROM orders JOIN customers ON orders.customer_id = customers.id pairs each order with the customer whose id matches.
- When two tables share a column name, such as id, prefix it with the table: customers.id. Short aliases save typing: FROM orders o JOIN customers c ON o.customer_id = c.id.
The mistake you are about to make
SELECT id, name FROM orders JOIN customers ON orders.customer_id = customers.id;
SELECT orders.id, name FROM orders JOIN customers ON orders.customer_id = customers.id;
Inside one query id seems unambiguous. Both tables have it, and the database will not guess which one you mean: ambiguous column name: id.
LEFT JOIN
Keep rows that have no match, and find exactly those rows.
- A plain JOIN keeps only pairs that match. A customer with no orders has nothing to pair with, so they vanish from the result without any warning.
- LEFT JOIN keeps every row of the left table, the one named in FROM. Where there is no match, the right table's columns come back as NULL.
- That NULL is useful: FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL finds exactly the customers who have never ordered.
The mistake you are about to make
FROM customers c JOIN orders o ON o.customer_id = c.id
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
A per-customer orders report looks complete. A plain JOIN silently drops everyone with zero orders — and the report never shows a single zero.
Subqueries
Use the result of one query inside another.
- A query can sit inside another. SELECT name FROM products WHERE price > (SELECT AVG(price) FROM products); first works out the average, then keeps products above it.
- IN takes a whole column from a subquery: WHERE id IN (SELECT customer_id FROM orders) keeps customers who appear in orders. NOT IN keeps the rest.
- A subquery used as a value should return one row with one column. If it returns several rows, SQLite quietly takes the first one, which is a bug waiting to happen.
The mistake you are about to make
WHERE price > AVG(price)
WHERE price > (SELECT AVG(price) FROM products)
Comparing with the average right in WHERE feels natural. An aggregate has nothing to work on in WHERE yet. Work the average out in its own query, in brackets.