Combining tables: JOIN

The basic join

Orders store customer_id, while customer names are stored in customers. To see them together, specify how the two tables relate.

SELECT o.order_number,
       o.ordered_at,
       o.total,
       c.first_name || ' ' || c.last_name AS customer,
       c.country
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.ordered_at DESC
LIMIT 20;

JOIN on its own means INNER JOIN. ON c.id = o.customer_id is the join condition: it specifies how rows from the two tables match. orders o gives the table a short alias, allowing references such as o.total and c.country.

Aliases stop being optional the moment two tables have a column of the same name. Both of these tables have an id; unqualified, id would be ambiguous and the database would refuse the query.

More than two tables

Joins can be chained. An order reaches its products through two steps: orders → order_items → products.

SELECT o.order_number,
       p.name AS product,
       oi.quantity,
       oi.unit_price
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'delivered'
ORDER BY o.ordered_at DESC
LIMIT 25;

Each JOIN adds one more table to the result, so a four-table join follows the same pattern with another line. This query reads as orders, their items, and each item's product.

Keeping rows that have no match

An inner join drops anything without a partner. A customer who has never ordered simply disappears from the result. LEFT JOIN keeps every row of the left table and fills the right side with NULL where there was nothing to match.

SELECT c.id,
       c.first_name,
       c.last_name,
       o.order_number,
       o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id
LIMIT 25;

This makes “find what is missing” queries possible: keep the left-side rows, then select only those where the right side is NULL.

-- The 95 customers who have never ordered anything.
SELECT c.id, c.first_name, c.last_name, c.country
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.id
LIMIT 25;

WHERE o.id IS NULL may look odd because orders.id is a primary key and cannot be NULL in orders. In this result, however, LEFT JOIN supplies NULL for the unmatched right side. Those rows identify the 95 customers who have never ordered.

ON or WHERE: not the same question

Both queries use status = 'cancelled', but they produce different LEFT JOIN results.

-- The condition is part of the join: every customer is kept,
-- and only cancelled orders are attached to them.
SELECT c.id, c.first_name, o.order_number, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'cancelled'
ORDER BY c.id
LIMIT 25;

The condition sits in ON, so it is part of the matching: every customer is kept, and a cancelled order is attached where one exists. Customers with no cancelled order still appear, with NULLs.

-- The same condition in WHERE runs after the join and throws away
-- every row where it is not true — including the NULLs the LEFT JOIN made.
SELECT c.id, c.first_name, o.order_number, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'cancelled'
ORDER BY c.id
LIMIT 25;

Here the condition is in WHERE, which runs after the join and removes rows for which it is not true. The NULL rows created by LEFT JOIN fail NULL = 'cancelled', so they are discarded. The result is effectively an INNER JOIN.

As a rule of thumb, put a condition on the right-hand table of a LEFT JOIN in ON, and a condition on the left-hand table in WHERE. With an INNER JOIN the two placements give the same result, which can make this distinction easy to miss.

Joining a table to itself

Nothing says the two sides have to be different tables. Employees have a manager_id pointing at another employee, so the table joins to itself under two different aliases.

SELECT e.first_name || ' ' || e.last_name AS employee,
       e.position,
       m.first_name || ' ' || m.last_name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY manager, employee
LIMIT 25;

e and m are the same table seen twice, and the aliases are what make it work at all. It is a LEFT JOIN because the ten people at the top have no manager, and an inner join would quietly leave them out.

What you can do now

You can ask questions across the whole schema. What you cannot do yet is summarize the answer: counting the orders, totalling the revenue and averaging are covered in the next lesson.