JOINs

Why Joins Exist
INNER JOIN: Only the Matches
Table Aliases and Reading a Join
LEFT JOIN: Keep Everything From the Left
Finding Rows With No Match (Anti-Join)
RIGHT JOIN and FULL JOIN
CROSS JOIN
Self Join
Joining Three or Four Tables
ON vs USING
Mistake 1: Row Multiplication
Mistake 2: Filtering a LEFT JOIN in WHERE
Try It Yourself

1. Why Joins Exist

In the last chapter you split the shop's data into five tables. An order does not store the customer's name. It stores customer_id, a number that points to a row in customers. An order line does not store the product name. It stores product_id.

That split is deliberate. The name "Sokha Chan" lives in exactly one place, so changing it is a one-row update. But it creates a new need: to print "Sokha Chan ordered a Wireless Mouse" you must bring rows from several tables back together.

That is what a join does. A join takes two tables and produces one combined result, pairing up rows according to a rule you write. The rule is nearly always "the foreign key equals the primary key".

Everything in this chapter uses the shopdb data from earlier chapters. Put the queries in a new file, shopdb/queries/07_joins.sql, and run them with:

docker exec -i shopdb-pg psql -U postgres -d shopdb < queries/07_joins.sql

Or paste them into an interactive psql session. As a reminder, here are the two small tables we start with:

customers                         orders
 id | name        | city           id | customer_id | status
----+-------------+------------   ----+-------------+----------
  1 | Sokha Chan  | Phnom Penh      1 |           1 | paid
  2 | Dara Kim    | Siem Reap       2 |           1 | shipped
  3 | Linda Vong  | Phnom Penh      3 |           2 | pending
  4 | Vuthy Sok   | Battambang      4 |           3 | paid
  5 | Maly Ros    | (null)          5 |           2 | cancelled

Notice that customers 4 and 5 (Vuthy and Maly) have no orders. They will matter a lot in this chapter.

2. INNER JOIN: Only the Matches

An INNER JOIN keeps only the pairs of rows where the ON condition is true. Rows with no partner on the other side disappear.

SELECT c.name, o.id AS order_id, o.status
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
ORDER BY o.id;

    name    | order_id |  status
------------+----------+-----------
 Sokha Chan |        1 | paid
 Sokha Chan |        2 | shipped
 Dara Kim   |        3 | pending
 Linda Vong |        4 | paid
 Dara Kim   |        5 | cancelled
(5 rows)

Read the query out loud: "take every customer, and for each one find the orders whose customer_id equals the customer's id." Sokha has two orders, so Sokha appears twice. Vuthy and Maly have none, so they are missing from the result.

The word INNER is optional. JOIN alone means INNER JOIN, and most people write it that way.

Here is the picture to keep in your head. Think of each table as a set of rows, and the join as the overlap:

customers          orders
 (1)  --------------- (order 1, 2)
 (2)  --------------- (order 3, 5)
 (3)  --------------- (order 4)
 (4)   (no match)
 (5)   (no match)

An inner join keeps only the lines in the middle.

3. Table Aliases and Reading a Join

Look at what we typed: FROM customers c. The c is an alias, a short name for the table valid only inside this query. Aliases save typing and, more importantly, remove ambiguity. Both customers and orders have a column called id. If you wrote just id, PostgreSQL would stop with:

ERROR:  column reference "id" is ambiguous
LINE 1: SELECT id FROM customers c JOIN orders o ON o.customer_id = ...

Writing c.id or o.id says exactly which one you mean. The habit to build: always prefix columns with the alias in any query with a join, even when it is not strictly required. Six months later you will thank yourself.

A reliable way to read a join is left to right, one table at a time:

Start with customers (call it c).
For each of its rows, look in orders (call it o) for rows where o.customer_id = c.id.
Output one result row per matching pair.

Aliases also let you rename output columns with AS, as we did with o.id AS order_id, so the result does not show two columns both called id.

4. LEFT JOIN: Keep Everything From the Left

What if you want a list of all customers, with their orders when they have any? An inner join drops Vuthy and Maly. A LEFT JOIN keeps every row from the left table, and fills the right side's columns with NULL when there is no match.

SELECT c.name, o.id AS order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.id;

    name    | order_id |  status
------------+----------+-----------
 Sokha Chan |        1 | paid
 Sokha Chan |        2 | shipped
 Dara Kim   |        3 | pending
 Dara Kim   |        5 | cancelled
 Linda Vong |        4 | paid
 Vuthy Sok  |          |
 Maly Ros   |          |
(7 rows)

The blank cells are NULL. (By default psql prints NULL as an empty string. You can change that with \pset null '(null)', which is handy while learning.) Seven rows: five matched pairs, plus one row each for the two customers without orders.

"Left" simply means the table written before the JOIN keyword. Here that is customers. Swap the order of the tables and you get a different query.

LEFT JOIN is also written LEFT OUTER JOIN. They are the same thing.

The rule of thumb for choosing: if the question is "for each X, show its Ys, and I still want X even if it has no Ys", use LEFT JOIN with X on the left. If you only care about X rows that have Ys, use JOIN.

5. Finding Rows With No Match (Anti-Join)

A very common question is the reverse: "which customers have never ordered?" This is called an anti-join: rows from one table that have no partner in another.

The LEFT JOIN gives us a neat way. After a left join, customers without orders have NULL in every o column. So keep only those rows:

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

   name
-----------
 Vuthy Sok
 Maly Ros
(2 rows)

We test o.id IS NULL because id is a primary key and can never be NULL in a real row, so a NULL there can only mean "no match". Always test a column that is guaranteed non-null, never one like o.status that might be NULL for real orders.

The second way is NOT EXISTS with a subquery:

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

It returns the same two customers. Read it as: "customers for whom no order exists with their id." The SELECT 1 is just a convention, since EXISTS only cares whether any row comes back, not what is in it.

Which should you use? Both are correct and PostgreSQL usually plans them the same way. Many people find NOT EXISTS reads more like the English question. Use whichever your team prefers, but avoid NOT IN (SELECT ...) for this job. If the subquery ever returns a NULL, NOT IN returns no rows at all, a nasty surprise from the three-valued logic you saw in chapter 5.

Anti-joins are everywhere in real work: products never sold, users who never logged in, invoices without a payment. Try this one on products:

SELECT p.name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.order_id IS NULL;

 name
------
(0 rows)

With our seed data every product has been ordered at least once, so the result is empty. An empty result is a valid answer, not an error.

6. RIGHT JOIN and FULL JOIN

A RIGHT JOIN is the mirror image of LEFT JOIN: it keeps every row from the table on the right.

SELECT p.name AS product, oi.order_id
FROM order_items oi
RIGHT JOIN products p ON p.id = oi.product_id
ORDER BY p.id, oi.order_id;

This keeps every product, even ones never ordered. With our data every product was ordered, so it looks the same as an inner join:

       product       | order_id
---------------------+----------
 Wireless Mouse      |        1
 Wireless Mouse      |        4
 USB-C Cable         |        1
 Mechanical Keyboard |        2
 SQL Cookbook        |        3
 Clean Code          |        3
 Desk Lamp           |        5
 Coffee Mug          |        4
 Notebook Pack       |        4
(9 rows)

In practice almost nobody writes RIGHT JOIN. Any right join can be rewritten as a left join by swapping the table order, and left-to-right reading is easier. You need to recognise it, not use it.

A FULL JOIN (full outer join) keeps unmatched rows from both sides. It is rare, but useful for comparing two lists: "what is in A only, in B only, and in both?" Let's see it with two tiny throwaway lists, built inline with VALUES:

WITH a(x) AS (VALUES (1), (2), (3)),
     b(x) AS (VALUES (2), (3), (4))
SELECT a.x AS a_x, b.x AS b_x
FROM a
FULL JOIN b ON a.x = b.x
ORDER BY COALESCE(a.x, b.x);

 a_x | b_x
-----+-----
   1 |
   2 |   2
   3 |   3
     |   4
(4 rows)

Row 1 exists only in a, row 4 only in b, and 2 and 3 in both. (The WITH part is a CTE, a named temporary query. We cover those properly in the next chapter.)

Here is the whole family at a glance:

Join	Keeps
`INNER JOIN`	only pairs that match
`LEFT JOIN`	all left rows, matched right rows or `NULL`
`RIGHT JOIN`	all right rows, matched left rows or `NULL`
`FULL JOIN`	all rows from both sides
`CROSS JOIN`	every left row paired with every right row

7. CROSS JOIN

A CROSS JOIN has no ON clause. It pairs every row of one table with every row of the other. If the left has 3 rows and the right has 4, you get 12.

SELECT cat.name AS category, s.status
FROM categories cat
CROSS JOIN (SELECT DISTINCT status FROM orders) s
ORDER BY cat.name, s.status;

  category   |  status
-------------+-----------
 Books       | cancelled
 Books       | paid
 Books       | pending
 Books       | shipped
 Electronics | cancelled
 Electronics | paid
 Electronics | pending
 Electronics | shipped
 Home        | cancelled
 Home        | paid
 Home        | pending
 Home        | shipped
(12 rows)

Three categories times four statuses is twelve combinations. This is useful to build a "grid" of every possibility, for instance every category and status so a report can show zeros instead of missing rows.

It is also the cautionary tale of accidental joins. If you forget the ON condition in an old-style comma join (FROM customers, orders), you silently get a cross join: 5 customers times 5 orders is 25 rows of nonsense. With two big tables that can be millions of rows and a frozen terminal. Always use explicit JOIN ... ON.

8. Self Join

A table can be joined to itself. You give it two different aliases and treat it as two copies. A natural use is "find pairs of rows in the same table that share something". Which customers live in the same city?

SELECT a.name AS customer, b.name AS neighbour, a.city
FROM customers a
JOIN customers b
  ON a.city = b.city
 AND a.id < b.id;

  customer  | neighbour  |    city
------------+------------+------------
 Sokha Chan | Linda Vong | Phnom Penh
(1 row)

Two things to notice. First, the aliases a and b are required, otherwise PostgreSQL cannot tell the two copies apart. Second, a.id < b.id is a trick to avoid noise. Without it, every customer matches themselves (Sokha with Sokha), and each real pair appears twice (Sokha with Linda, then Linda with Sokha). Requiring a.id < b.id keeps each pair exactly once.

Also notice that Maly, whose city is NULL, never matches anyone. NULL = NULL is not true, it is unknown, so the join condition fails.

Self joins are how you model hierarchies, such as employees and their managers stored in one table with a manager_id column. Our shop does not need that, but you will meet it.

9. Joining Three or Four Tables

Real questions usually span more than two tables. You just keep chaining joins. Each JOIN takes the result so far and attaches one more table.

"For each paid order, list who bought what, with the category":

SELECT c.name AS customer,
       o.id AS order_id,
       p.name AS product,
       cat.name AS category,
       oi.quantity
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status = 'paid'
ORDER BY o.id, p.id;

  customer  | order_id |    product     |  category   | quantity
------------+----------+----------------+-------------+----------
 Sokha Chan |        1 | Wireless Mouse | Electronics |        1
 Sokha Chan |        1 | USB-C Cable    | Electronics |        2
 Linda Vong |        4 | Wireless Mouse | Electronics |        2
 Linda Vong |        4 | Coffee Mug     | Home        |        3
 Linda Vong |        4 | Notebook Pack  | Books       |        1
(5 rows)

The five-table path is the whole shop: customer, order, line, product, category. Look at how each ON connects the new table to one already in the chain: the foreign key on one side, the primary key on the other. That regularity is the payoff of good key design from chapter 6.

Tips for long joins:

Write one JOIN per line and align the ON parts. Your eyes will thank you.
Build it up step by step. Start with two tables, check the result, add the next table, check again.
Order of JOINs rarely matters for correctness with inner joins. PostgreSQL's planner chooses the efficient order itself.
Watch the row count after each step. If a step multiplies rows unexpectedly, see section 11.

Mixing is allowed too. If you want every customer even without orders, make the first join a LEFT JOIN, and remember that later joins in the chain must also be LEFT JOINs if you still want to keep those customers (an inner join afterwards would throw the empty rows away again).

10. ON vs USING

When the joined columns have the same name in both tables, you can use USING as shorthand:

-- with ON
SELECT * FROM orders o JOIN customers c ON c.id = o.customer_id;

USING needs the same column name on both sides, and it merges them into one output column. For example, if order_items had a column named id that matched orders.id, you could write JOIN order_items USING (id). In shopdb the names differ (orders.id versus order_items.order_id), so we use ON.

Be careful: in our schema both orders and customers have a column named id, but they mean different things. customers JOIN orders USING (id) would be valid SQL and would silently pair customer 1 with order 1, customer 2 with order 2, and so on, a meaningless result. That is exactly why the surrogate-key style id plus customer_id is helpful: only genuinely related columns share a name.

My advice: prefer ON. It is explicit, handles any column names and any condition (a.id < b.id), and cannot be fooled by columns that share a name by accident. Use USING only when you like its brevity and the names truly mean the same thing.

11. Mistake 1: Row Multiplication

A join pairs every matching row. When one side has several matches, the other side's row is repeated. That is correct, but it can surprise you.

The orders table has 5 rows. Join it to its lines and count:

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

 count
-------
     9
(1 row)

Nine, not five. Order 1 has two lines, so it now appears twice. If you then sum an order-level value such as a shipping fee stored on orders, you would count it once per line and overstate the total. If you count orders after this join, you would get 9 instead of 5.

Remember this checklist whenever a number looks too big after a join:

Which join in the chain is one-to-many?
Am I counting or summing a column that belongs to the "one" side?
Should I aggregate before joining, or use COUNT(DISTINCT ...)?

Next chapter, on aggregation, shows how to count correctly across joins. For now, the lesson is: after each join, ask "how many rows should this have?" and run SELECT count(*) to check.

The opposite problem is row loss: an inner join silently drops rows that have no partner. If your report is missing customers, a JOIN that should have been a LEFT JOIN is the first suspect.

12. Mistake 2: Filtering a LEFT JOIN in WHERE

This is the most common LEFT JOIN bug. Suppose you want all customers, and for each, only their paid orders. You might write:

SELECT c.name, o.id AS order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
ORDER BY c.id;

    name    | order_id | status
------------+----------+--------
 Sokha Chan |        1 | paid
 Linda Vong |        4 | paid
(2 rows)

Dara, Vuthy and Maly vanished. Why? WHERE runs after the join. For Vuthy the joined o.status is NULL, and NULL = 'paid' is not true, so the row is thrown away. The WHERE condition turned the left join into an inner join, and you got the wrong answer without any error.

The fix is to move the condition into the ON clause, where it decides which rows count as a match, instead of which result rows survive:

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

    name    | order_id | status
------------+----------+--------
 Sokha Chan |        1 | paid
 Dara Kim   |          |
 Linda Vong |        4 | paid
 Vuthy Sok  |          |
 Maly Ros   |          |
(5 rows)

Now all five customers appear. Dara has orders, but none paid, so her order columns are NULL. Sokha's shipped order 2 is not a match, so it is not shown.

The rule: for the right table of a LEFT JOIN, put its filters in ON. Filters on the left table can safely live in WHERE. (There is one deliberate exception, the anti-join from section 5, where WHERE o.id IS NULL is exactly the point.)

Save everything you tried into shopdb/queries/07_joins.sql so you can run it again.

13. Try It Yourself

Run these against shopdb. Check each answer by reasoning about the data first, then run the query.

List each product name together with its category name (inner join between products and categories), ordered by category then product name. How many rows do you get? Why is it equal to the number of products?
Show every order with the customer's name and city. Then change the query to show only orders placed by customers from Phnom Penh. Which orders remain?
Write a LEFT JOIN query that lists every product with the order ids it appears in (NULL when never ordered). Then rewrite it as an anti-join to find products that were never ordered. What do you expect, and what do you get?
List the order lines of order 3 with product name, quantity, unit_price, and a computed line_total (quantity * unit_price). Add a fifth join to include the customer name.
Use a self join on products to find all pairs of products in the same category where the first is cheaper than the second. How many pairs are there for Electronics? (Hint: three products, so think about how many pairs of three.)
Predict the row count of customers CROSS JOIN categories, then run SELECT count(*) on it to check. Finally, rewrite the "paid orders only" query from section 12 so it also shows Maly Ros's row and explain, in one sentence, why a WHERE filter on o.status could never do that.

Where to go next. You can now read and write queries across all the shop's tables. The natural next question is "how much?" and "how many?" In Aggregation and Grouping you will learn COUNT, SUM, AVG, GROUP BY and HAVING to turn all these joined rows into revenue per customer, sales per category and orders per month.