← All lessons
Querying data · Lesson 2 of 7

JOINs

A database splits data across tables so each fact is stored once. An order row holds customer_id, not the customer's name, so a name change is one update in customers. To see an order with its customer's name, you join the two tables on the column they share.

SELECT o.id, c.name, o.ordered_on
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';
Returns
id  | name | ordered_on
----+------+-----------
107 | Eve  | 2024-10-02
(1 row)

orders o gives the table a short alias, and o.id says which table's id you mean, since both have one. A plain JOIN is an inner join: it keeps only the pairs of rows that match on both sides.

A LEFT JOIN keeps every row of the left table. Where the right table has no match, its columns come back as NULL. Add WHERE <right>.id IS NULL and you get just the rows with no match: customers who never ordered, products nobody bought.

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
Returns
name
----
Gus
Hana
(2 rows)

You can chain joins. Here order_items links each order to its products:

SELECT o.id AS order_id, p.name, i.qty
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN products p ON p.id = i.product_id
WHERE o.customer_id = 1
ORDER BY o.id, p.name;
Returns
order_id | name        | qty
---------+-------------+----
101      | Duck feeder | 1
101      | Pond net    | 2
103      | Field guide | 1
103      | Sketchbook  | 3
108      | Sketchbook  | 1
(5 rows)

Joins multiply rows. Ana has three orders, so joining customers to orders gives her three rows, and joining on to order_items gives one row per item. That's correct, but it means a SUM over a column of the one side gets counted once per match. Before you join, ask: one row per what?

RIGHT JOIN is a left join written the other way round, and FULL OUTER JOIN keeps the unmatched rows from both sides. Most databases have both; SQLite added them in version 3.39.

Practice

Each order's id with the name of the customer who placed it.

The tables
  • customers (id, name, city, joined)
  • products (id, name, category, price)
  • orders (id, customer_id, ordered_on, status)
  • order_items (order_id, product_id, qty)
  • signups (id, email, name, signed_up_at)
  • payments (id, order_id, amount, currency, paid_at)
More info

Quick reference

JOIN t ON …Inner join: only rows that match on both sides.
LEFT JOIN t ON …Every left row; the right side's columns are NULL where nothing matches.
… WHERE t.id IS NULLAfter a LEFT JOIN: the left rows with no match.
FROM orders oo is an alias for the table.
FULL OUTER JOINUnmatched rows from both sides are kept.

The other AI and data engineering courses