Joins

Putting two tables together, and the WHERE clause that silently undoes a LEFT JOIN.

After this lesson you can

Choose between an INNER JOIN and a LEFT JOIN deliberately
Predict which rows survive each one
Explain why a filter on the outer table belongs in ON, not WHERE

A join matches rows in one table against rows in another using a condition you supply.

INNER JOIN keeps only rows that matched on both sides.

LEFT JOIN keeps every row from the left table, and fills the right-hand columns with NULL where nothing matched.

SELECT c.name, o.total_cents
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

A customer with no orders still appears, once, with total_cents null.

Try it

Given — already loaded, nothing to run heresql

1CREATE TABLE customers (2  id   int PRIMARY KEY,3  name text NOT NULL4);5CREATE TABLE orders (6  id          int PRIMARY KEY,7  customer_id int NOT NULL,8  status      text NOT NULL,9  total_cents int NOT NULL10);11INSERT INTO customers VALUES (1, 'Nino'), (2, 'Ana'), (3, 'Luka');12INSERT INTO orders VALUES13  (10, 1, 'paid', 4500),14  (11, 1, 'cancelled', 900),15  (12, 2, 'paid', 12000);

Every customer, with their order totalpostgres-16

1SELECT2  c.name,3  COALESCE(SUM(o.total_cents), 0) AS paid_cents4FROM customers c5LEFT JOIN orders o6  ON o.customer_id = c.id7 AND o.status <> 'cancelled'8GROUP BY c.name9ORDER BY c.name;

What to look for

The trap

This is the mistake that turns up in real code more than any other join question:

SELECT c.name, SUM(o.total_cents)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status <> 'cancelled'   -- the bug
GROUP BY c.name;

Customers with no orders survive the join with every o.* column null. The WHERE clause then evaluates NULL <> 'cancelled', which is unknown rather than true, so those rows are thrown away. The query is now an inner join and nobody changed the word LEFT.

The fix is to move the condition into the ON clause, where it decides which orders are allowed to match rather than which customers survive:

LEFT JOIN orders o
  ON o.customer_id = c.id
 AND o.status <> 'cancelled'

The rule worth carrying out of this lesson: a filter on the outer side of a LEFT JOIN belongs in ON; a filter on the preserved side belongs in WHERE.

Try it yourself

2 visible tests · 2 hidden tests

Tables customers(id, name) and orders(id, customer_id). Return every customer's name and order_count — the number of orders they have placed, 0 if none — ordered by name. Every customer must appear exactly once, including one who has never ordered.

Given — already loaded, nothing to run heresql

1CREATE TABLE customers (2  id   int PRIMARY KEY,3  name text NOT NULL4);5CREATE TABLE orders (6  id          int PRIMARY KEY,7  customer_id int NOT NULL8);9INSERT INTO customers VALUES (1, 'Nino'), (2, 'Ana'), (3, 'Luka');10INSERT INTO orders VALUES (10, 1), (11, 1), (12, 2);

Loading editor…

Previous Next