Joins

The relational model splits data into focused tables: book titles live in books, author names in authors, who-bought-what in order_items. Joins put those pieces back together at query time. They are the single most important skill in SQL, and the source of the most common bugs — missing rows, duplicated rows, and filters that quietly turn one kind of join into another. This lesson builds a precise mental model of what each join produces.

#How a join works

A join takes two tables and produces rows made of a row from the left table glued to a row from the right table. The ON condition decides which pairs are kept:

SELECT b.title, a.name AS author
FROM books AS b
JOIN authors AS a ON a.author_id = b.author_id;

           title           |      author
---------------------------+-------------------
 A Wizard of Earthsea      | Ursula K. Le Guin
 The Left Hand of Darkness | Ursula K. Le Guin
 The Dispossessed          | Ursula K. Le Guin
 Guards! Guards!           | Terry Pratchett
 Small Gods                | Terry Pratchett
 Going Postal              | Terry Pratchett
 Kindred                   | Octavia E. Butler
 Parable of the Sower      | Octavia E. Butler
 Norwegian Wood            | Haruki Murakami
 Kafka on the Shore        | Haruki Murakami
(10 rows)

Conceptually, the database considers every (book, author) pair — 10 × 5 = 50 of them — and keeps the 10 where the IDs match. (The planner uses much faster algorithms in practice, but the result is always defined this way.) The table aliases b and a keep the query short and make it unambiguous which table each column comes from.

#INNER JOIN: only matching pairs

JOIN is short for INNER JOIN. It keeps only pairs that satisfy the condition; a row with no partner disappears. Here is how customers match orders:

 customers                    orders
 ─────────────                ──────────────────
 1 Ada Park      ──────────►  1, 3, 8
 2 Ben Okafor    ──────────►  2, 6, 10
 3 Chloe Martin  ──────────►  4
 4 Dev Raman     ──────────►  5, 9
 5 Elena Rossi   ──────────►  7
 6 Farid Haddad  (no orders)

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

     name     | order_id | ordered_on
--------------+----------+------------
 Ada Park     |        1 | 2025-01-05
 Ada Park     |        3 | 2025-02-02
 Ada Park     |        8 | 2025-03-15
 Ben Okafor   |        2 | 2025-01-12
 Ben Okafor   |        6 | 2025-03-01
 Ben Okafor   |       10 | 2025-03-30
 Chloe Martin |        4 | 2025-02-14
 Dev Raman    |        5 | 2025-02-20
 Dev Raman    |        9 | 2025-03-22
 Elena Rossi  |        7 | 2025-03-08
(10 rows)

Two things to notice. Ada appears three times — once per matching order. And Farid is gone, because he has no orders to pair with.

#Join cardinality

You can predict the row count of any join by asking "how many partners does each row have?":

• One-to-one match (each book has exactly one author): the result has one row per book.
• One-to-many (customer → orders): each customer row is repeated once per order. Rows with zero partners vanish from an inner join.
• Many-to-many through a junction table: joining 10 orders to their 16 order_items produces 16 rows, and every order-level column (date, status) is repeated on each of its lines.

That repetition is correct and expected, but it has consequences. If you join orders to items and then add up an order-level value, each order is counted once per line. Always know which table's rows your result is "one row per" before you aggregate — the aggregation lesson returns to this.

#LEFT JOIN: keep everything on the left

A LEFT JOIN (or LEFT OUTER JOIN) keeps every row from the left table. Where there is no match, the right-hand columns are filled with NULL:

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

     name     | order_id | ordered_on
--------------+----------+------------
 Ada Park     |        1 | 2025-01-05
 Ada Park     |        3 | 2025-02-02
 Ada Park     |        8 | 2025-03-15
 Ben Okafor   |        2 | 2025-01-12
 Ben Okafor   |        6 | 2025-03-01
 Ben Okafor   |       10 | 2025-03-30
 Chloe Martin |        4 | 2025-02-14
 Dev Raman    |        5 | 2025-02-20
 Dev Raman    |        9 | 2025-03-22
 Elena Rossi  |        7 | 2025-03-08
 Farid Haddad |   (null) | (null)
(11 rows)

Eleven rows: the ten matches plus one for Farid. Use a LEFT JOIN whenever the question says "all customers", "every author", "including those with none".

#Anti-joins: finding rows with no match

Because unmatched rows get NULLs on the right, you can find them by filtering for a NULL in a right-hand column that can never be NULL in a real match — usually its primary or join key:

SELECT b.title
FROM books AS b
LEFT JOIN order_items AS oi ON oi.book_id = b.book_id
WHERE oi.book_id IS NULL;

      title
------------------
 The Dispossessed
(1 row)

This "books that have never been ordered" pattern is called an anti-join. (The subqueries lesson shows an equivalent NOT EXISTS form.)

#RIGHT JOIN

A RIGHT JOIN B is exactly B LEFT JOIN A: it keeps every row of the right table. Most teams write only LEFT JOINs, reordering the tables, because queries read more naturally when the "main" table comes first.

#The ON-versus-WHERE trap

Suppose you want every customer, with their paid orders if they have any. This looks right but is not:

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

    name     | order_id | status
-------------+----------+--------
 Ada Park    |        8 | paid
 Ben Okafor  |       10 | paid
 Dev Raman   |        9 | paid
 Elena Rossi |        7 | paid
(4 rows)

Chloe and Farid vanished. WHERE runs after the join: Farid's padded row has o.status = NULL, and NULL = 'paid' is not true, so WHERE throws it away. The LEFT JOIN has silently become an inner join. Move the condition into ON, so it decides which orders match rather than which result rows survive:

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

     name     | order_id | status
--------------+----------+--------
 Ada Park     |        8 | paid
 Ben Okafor   |       10 | paid
 Chloe Martin |   (null) | (null)
 Dev Raman    |        9 | paid
 Elena Rossi  |        7 | paid
 Farid Haddad |   (null) | (null)
(6 rows)

#FULL OUTER JOIN and CROSS JOIN

#FULL OUTER JOIN: keep both sides

A FULL JOIN keeps unmatched rows from both tables. Our foreign keys guarantee every book has an author, so let's match customers against a small inline list of cities where the shop has a store. A VALUES list in FROM acts as a temporary table, with column names given after the alias:

SELECT s.city AS store_city, c.name AS customer
FROM (VALUES ('Leeds'), ('Manchester')) AS s (city)
FULL JOIN customers AS c ON c.city = s.city
ORDER BY s.city, c.name;

 store_city |   customer
------------+--------------
 Leeds      | Ada Park
 Leeds      | Dev Raman
 Manchester | (null)
 (null)     | Ben Okafor
 (null)     | Chloe Martin
 (null)     | Elena Rossi
 (null)     | Farid Haddad
(7 rows)

The Manchester store has no local customers, and four customers have no local store. Full joins are most useful for reconciliation: comparing two lists and seeing what is missing from each.

#CROSS JOIN: every combination

A CROSS JOIN has no condition and returns every pairing — the Cartesian product. Ten books in two formats give twenty rows:

SELECT b.title, f.format
FROM books AS b
CROSS JOIN (VALUES ('paperback'), ('hardback')) AS f (format)
ORDER BY b.book_id, f.format
LIMIT 4;

           title           |  format
---------------------------+-----------
 A Wizard of Earthsea      | hardback
 A Wizard of Earthsea      | paperback
 The Left Hand of Darkness | hardback
 The Left Hand of Darkness | paperback
(4 rows)

Cross joins are useful for generating combinations (every product in every size, every store for every day). An accidental cross join — forgetting the ON condition in old-style FROM a, b syntax — is a classic way to produce millions of nonsense rows.

Try all five on a few rows of the bookstore. Predict each row count before you switch, then watch which source rows match, which survive padded with NULL, and which are dropped. One extra draft book with a NULL author_id shows that NULL never matches anything in ON.

#Self joins and multi-table joins

#Joining a table to itself

customers.referred_by points at another customer. To show names on both sides, join customers to itself under two different aliases — one playing "the customer", one playing "the referrer":

SELECT c.name AS customer, r.name AS referred_by
FROM customers AS c
LEFT JOIN customers AS r ON r.customer_id = c.referred_by
ORDER BY c.customer_id;

   customer   | referred_by
--------------+-------------
 Ada Park     | (null)
 Ben Okafor   | Ada Park
 Chloe Martin | (null)
 Dev Raman    | Ben Okafor
 Elena Rossi  | Dev Raman
 Farid Haddad | Ada Park
(6 rows)

The aliases are mandatory here — without them, customers.name would be ambiguous. An inner join would drop Ada and Chloe, who were not referred.

#Chaining joins

Joins compose: each one adds another table to the growing result. To list order lines with customer names and book titles, walk the foreign keys from orders outward:

SELECT o.order_id, c.name AS customer, b.title, oi.quantity
FROM orders AS o
JOIN customers   AS c  ON c.customer_id = o.customer_id
JOIN order_items AS oi ON oi.order_id   = o.order_id
JOIN books       AS b  ON b.book_id     = oi.book_id
WHERE o.order_id <= 3
ORDER BY o.order_id, b.title;

 order_id |  customer  |           title           | quantity
----------+------------+---------------------------+----------
        1 | Ada Park   | A Wizard of Earthsea      |        1
        1 | Ada Park   | Guards! Guards!           |        2
        2 | Ben Okafor | Norwegian Wood            |        1
        3 | Ada Park   | Small Gods                |        1
        3 | Ada Park   | The Left Hand of Darkness |        1
(5 rows)

If you start a chain with a LEFT JOIN, keep the joins that follow from it as LEFT JOINs too. An inner join further down the chain discards the NULL-padded rows the earlier outer join preserved.

#USING and NATURAL

When the join columns share a name, JOIN customers USING (customer_id) is shorthand for the equality, and the output contains a single customer_id column instead of two. NATURAL JOIN goes further and joins on every column with a matching name. Avoid it: customers NATURAL JOIN authors joins on name and returns zero rows, and adding an innocent column such as updated_at to two tables would silently change every natural join between them.

#Practice

1. For every customer, list each distinct book title they have bought in orders that were not cancelled. Customers who bought nothing (in non-cancelled orders) must still appear, with a NULL title. Sort by customer name, then title.
2. Find authors who have no books in the catalogue, using an anti-join.

SELECT DISTINCT c.name, b.title
FROM customers AS c
LEFT JOIN orders      AS o  ON o.customer_id = c.customer_id
                           AND o.status <> 'cancelled'
LEFT JOIN order_items AS oi ON oi.order_id = o.order_id
LEFT JOIN books       AS b  ON b.book_id   = oi.book_id
ORDER BY c.name, b.title;

     name     |           title
--------------+---------------------------
 Ada Park     | A Wizard of Earthsea
 Ada Park     | Guards! Guards!
 Ada Park     | Small Gods
 Ada Park     | The Left Hand of Darkness
 Ben Okafor   | Going Postal
 Ben Okafor   | Guards! Guards!
 Ben Okafor   | Norwegian Wood
 Ben Okafor   | Small Gods
 Chloe Martin | (null)
 Dev Raman    | Kindred
 Dev Raman    | Norwegian Wood
 Dev Raman    | Parable of the Sower
 Elena Rossi  | A Wizard of Earthsea
 Farid Haddad | (null)
(14 rows)

SELECT a.name
FROM authors AS a
LEFT JOIN books AS b ON b.author_id = a.author_id
WHERE b.book_id IS NULL;

     name
---------------
 N. K. Jemisin
(1 row)

#Key takeaways

• A join pairs rows from two tables; ON decides which pairs are kept. Each row appears once per matching partner.
• Inner joins drop unmatched rows; LEFT/RIGHT/FULL joins keep them and fill the missing side with NULL.
• With outer joins, filter the optional table in ON, not WHERE — otherwise the outer join silently becomes an inner join.
• LEFT JOIN ... WHERE right_key IS NULL finds rows with no match (an anti-join).
• Self joins need two aliases; cross joins produce every combination; avoid NATURAL JOIN.
• Before aggregating, know what one row of your join represents, because one-to-many joins repeat the "one" side.