mindixx
mindixx

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.

Back to the map