Joins: Combining Tables

In lesson 0 we split the shop into four tables. That is on purpose: a customer's name is stored once, not copied into every order. But now the data you want is in two or three places at once, and you need a way to put it back together. That is what joins do.

This lesson uses the shop from lesson 0. If your data is changed, run setup.sql again.

Words used in this lesson

• A primary key (PK) is a column whose value is unique and not NULL in every row. It names one row. Here that is customers.id.
• A foreign key (FK) is a column that must match a primary key in another table. orders.customer_id is a foreign key to customers.id. The database refuses a value that has no match.
• A join combines rows of two tables into one result.
• An alias is a short name for a table, such as c for customers, so you can write c.name.
• One-to-many: one customer has many orders. Many-to-many: one order has many products, and one product is in many orders. A third table (here order_items) sits between them.
• A self join joins a table to itself.
• An anti-join finds rows that have no match in another table.

Keys: how tables point at each other

customers (id PK) <----- orders (customer_id FK) <----- order_items (order_id FK, product_id FK) -----> products (id PK)

The foreign key is a promise that the database keeps for you. Try to make an order for a customer that does not exist, or to delete a customer who still has orders:

INSERT INTO orders (customer_id, ordered_at) VALUES (99, now());DELETE FROM customers WHERE id = 4;

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"DETAIL:  Key (customer_id)=(99) is not present in table "customers".ERROR:  update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"DETAIL:  Key (id)=(4) is still referenced from table "orders".

So in this schema every order has a real customer. Lesson 4 covers what else a foreign key can do on delete.

INNER JOIN

Question: which customer placed each order? Each order row has a customer_id. Match it to customers.id:

SELECT o.id, c.name, o.statusFROM orders oJOIN customers c ON c.id = o.customer_idORDER BY o.id;

 id | name |  status   ----+------+-----------  1 | Asha | paid  2 | Asha | shipped  3 | Ben  | paid  4 | Chen | cancelled  5 | Chen | shipped  6 | Asha | paid  7 | Dara | new(7 rows)

JOIN alone means INNER JOIN. Think of it as: for every order row, find the customer rows where ON is true, and glue them side by side. Eli and Fay have no orders, so they appear nowhere. Inner join keeps only matches.

The join types, drawn

To see all the types clearly, use two tiny tables. a has x = 1, 2, 3 and b has x = 2, 3, 4. Only 2 and 3 exist in both.

  a            b x | la       x | lb---+----     ---+---- 1 | a1       2 | b2 2 | a2       3 | b3 3 | a3       4 | b4

INNER JOIN: only rows that match.

SELECT a.x AS ax, a.la, b.x AS bx, b.lb FROM a JOIN b ON a.x = b.x;

 ax | la | bx | lb ----+----+----+----  2 | a2 |  2 | b2  3 | a3 |  3 | b3

LEFT JOIN (also LEFT OUTER JOIN): every row of the left table (the one written before JOIN); the right side is filled with NULL when nothing matches.

 ax | la | bx | lb ----+----+----+----  1 | a1 |    |   2 | a2 |  2 | b2  3 | a3 |  3 | b3

RIGHT JOIN: the mirror image: every row of the right table.

 ax | la | bx | lb ----+----+----+----  2 | a2 |  2 | b2  3 | a3 |  3 | b3    |    |  4 | b4

FULL JOIN: every row from both sides, matched where possible.

 ax | la | bx | lb ----+----+----+----  1 | a1 |    |   2 | a2 |  2 | b2  3 | a3 |  3 | b3    |    |  4 | b4

(The last three are the same query with LEFT JOIN, RIGHT JOIN and FULL JOIN in place of JOIN.) A simple picture: inner is the overlap; left is the overlap plus the left-only rows; right is the overlap plus the right-only rows; full is everything.

In practice most people write LEFT JOIN and almost never RIGHT JOIN, because you can always swap the table order. FULL JOIN is rare: it is for comparing two lists ("what is in one but not the other").

LEFT JOIN on the shop

Which customers placed orders, and who did not?

SELECT c.name, o.id AS order_id, o.statusFROM customers cLEFT JOIN orders o ON o.customer_id = c.idORDER BY c.id, o.id;

 name | order_id |  status   ------+----------+----------- Asha |        1 | paid Asha |        2 | shipped Asha |        6 | paid Ben  |        3 | paid Chen |        4 | cancelled Chen |        5 | shipped Dara |        7 | new Eli  |          |  Fay  |          | (9 rows)

Nine rows from six customers: Asha appears three times (one row per order). This is the key fact about joins: the result has one row for every matching pair. A one-to-many join repeats the "one" side. Eli and Fay stay in because of LEFT, with NULL for the order columns.

CROSS JOIN

CROSS JOIN has no condition: it pairs every left row with every right row (a "Cartesian product"). It is useful for making combinations:

SELECT * FROM sizes CROSS JOIN colors;

 size | color ------+------- S    | red S    | blue S    | green M    | red M    | blue M    | green(6 rows)

2 sizes times 3 colours is 6 rows. On real tables this explodes: 10,000 times 10,000 is 100 million rows.

A forgotten join condition is a cross join. If a join query returns far too many rows, check the ON first.

Self joins: a table joined to itself

customers.referred_by holds the id of the customer who brought someone in. To show the name of the referrer, join customers to itself. You must use two different aliases, c for the customer and r for the referrer:

SELECT c.name AS customer, r.name AS referred_byFROM customers cLEFT JOIN customers r ON r.id = c.referred_byORDER BY c.id;

 customer | referred_by ----------+------------- Asha     |  Ben      | Asha Chen     | Asha Dara     | Ben Eli      |  Fay      | (6 rows)

It is a LEFT JOIN so customers who were not referred (NULL) are kept. Any tree-like data (employee and manager, category and parent category) works this way. Lesson 3 shows how to walk a whole tree with a recursive CTE.

Join condition (ON) versus WHERE

For an inner join, ON and WHERE give the same rows, so people mix them up. For a left join they are very different:

• ON decides which right-side rows match. Left rows with no match are still kept.
• WHERE runs after the join and removes rows from the result.

Say we want each customer and their paid orders. First, the filter in ON:

SELECT c.name, o.id AS order_id, o.statusFROM customers cLEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'ORDER BY c.id, o.id;

 name | order_id | status ------+----------+-------- Asha |        1 | paid Asha |        6 | paid Ben  |        3 | paid Chen |          |  Dara |          |  Eli  |          |  Fay  |          | (7 rows)

All customers stay. Chen and Dara have orders, but none are paid, so their order columns are NULL. Now the same filter moved to WHERE:

SELECT c.name, o.id AS order_id, o.statusFROM customers cLEFT JOIN orders o ON o.customer_id = c.idWHERE o.status = 'paid'ORDER BY c.id, o.id;

 name | order_id | status ------+----------+-------- Asha |        1 | paid Asha |        6 | paid Ben  |        3 | paid(3 rows)

Eli, Fay, Chen and Dara are gone. The join did produce rows for them with o.status NULL, but NULL = 'paid' is unknown (lesson 0), and WHERE drops unknown rows. The LEFT JOIN now behaves like an inner join.

Anti-join: rows with no match

"Which customers have never ordered?" There are three common ways. The first is NOT EXISTS:

SELECT c.nameFROM customers cWHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)ORDER BY c.name;

 name ------ Eli Fay(2 rows)

For each customer, the subquery asks "is there at least one order?". NOT EXISTS is true when there is none. (The SELECT 1 is just a placeholder: EXISTS only cares whether any row comes back.) PostgreSQL turns this into an efficient "anti join".

The second is a LEFT JOIN followed by WHERE ... IS NULL on a column that cannot be NULL when a match exists (the primary key):

SELECT c.name FROM customers cLEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL;

It returns the same two rows, Eli and Fay. This is the one time a right-table condition in WHERE is correct: you want the unmatched rows.

The third way, NOT IN, is the trap from lesson 0. Say we want customers who have not referred anyone. A customer who referred nobody is one whose id never appears in referred_by:

SELECT name FROM customers WHERE id NOT IN (SELECT referred_by FROM customers);

 name ------(0 rows)

Nothing, which is wrong. The referred_by column contains NULLs, and id NOT IN (..., NULL) is never true. With NOT EXISTS the answer is right:

SELECT c.name FROM customers cWHERE NOT EXISTS (SELECT 1 FROM customers r WHERE r.referred_by = c.id)ORDER BY c.id;

 name ------ Chen Dara Eli Fay(4 rows)

Many-to-many and joining three or four tables

An order has many products, and a product is in many orders. The table order_items is the junction table (or link table) that holds one row per pair. Its primary key is (order_id, product_id), so the same product cannot appear twice in one order. To see what was in orders 1 and 3 with the customer name, chain the joins:

SELECT o.id AS order_id, c.name, p.name AS product, oi.quantity, oi.unit_priceFROM orders oJOIN customers   c  ON c.id = o.customer_idJOIN order_items oi ON oi.order_id = o.idJOIN products    p  ON p.id = oi.product_idWHERE o.id IN (1, 3)ORDER BY o.id, p.id;

 order_id | name | product  | quantity | unit_price ----------+------+----------+----------+------------        1 | Asha | Notebook |        2 |        1 | Asha | Pen      |        5 |       1.20        3 | Ben  | Notebook |        1 |        3 | Ben  | Backpack |        1 |      45.00 |(4 rows)

Read the joins from top to bottom: each JOIN ... ON adds one table to the result built so far. Notice unit_price is stored on the line, not only on the product. This is on purpose: it records the price at the time of sale, even if the product's price changes later.

Join fan-out: the double-counting bug

Each join can multiply rows. We have 7 orders. After joining their items:

SELECT count(*) AS joined_rows FROM orders o JOIN order_items oi ON oi.order_id = o.id;

 joined_rows -------------          12

Seven orders became 12 rows, because an order with 3 lines appears 3 times. If you then add up something that lives on the order (such as a shipping fee), you count it once per line. Rule: when you join a "one" table to a "many" table, summing a column from the "one" side gives wrong totals. Aggregate first, or sum only columns from the "many" side. Lesson 2 shows both ways.

Useful details

• USING (col) is a shortcut when both columns have the same name. Careful with orders.id and customers.id: they have the same name but mean different things, so JOIN customers c USING (id) compiles and gives nonsense. I ran it and got six meaningless "matches", such as Asha with order 1 and Fay with order 6.
• NATURAL JOIN joins on all same-named columns. It breaks the day someone adds a column. Avoid it.
• Foreign key columns get no index automatically in PostgreSQL (primary keys do). Lesson 5 shows how to add them.

Try it

1. List each order with the customer's city (inner join).
2. List every product with how many order lines it appears in, including products never sold. (Hint: LEFT JOIN and count(oi.order_id).)
3. Find customers with no paid orders.
4. For each customer who was referred, show the referrer's name and city.

Answers

1. SELECT o.id, c.name, c.city FROM orders o JOIN customers c ON c.id = o.customer_id;
2. SELECT p.name, count(oi.order_id) FROM products p LEFT JOIN order_items oi ON oi.product_id = p.id GROUP BY p.name; gives Gift card 0. count(*) would wrongly give 1 here, because the unmatched row still counts as a row. Lesson 2 explains why.
3. SELECT c.name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'paid'); gives Chen, Dara, Eli, Fay. The status test belongs inside the subquery.
4. SELECT c.name, r.name AS referrer, r.city FROM customers c JOIN customers r ON r.id = c.referred_by; gives Ben (Asha, Pune), Chen (Asha, Pune), Dara (Ben, London).

Interview questions

Key takeaways

• A foreign key points to a primary key. A join puts the pieces back together with ON a.fk = b.pk.
• Inner keeps matches only. Left keeps every left row. Full keeps both sides. Cross has no condition and multiplies.
• A join returns one row per matching pair, so a one-to-many join repeats the "one" side. Watch for double counting.
• With LEFT JOIN, right-table filters go in ON, not WHERE, unless you are looking for IS NULL.
• For "has no match", use NOT EXISTS, not NOT IN.
• Many-to-many needs a junction table. Self joins use two aliases.

What's next

Lesson 2: Aggregates, GROUP BY and HAVING. Now that you can combine tables, you will count, sum and average them: orders per customer, revenue per month, and the difference between WHERE and HAVING. For more on Prisma and SQL questions, see Full-Stack Q&A, Lesson 08.