# Joins > Combine rows from several tables with inner, outer, self and cross joins, and predict exactly how many rows each join returns. SQL & Databases roadmap · Querying · 18 min read · https://levelcode.org/roadmaps/sql/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: ```sql SELECT b.title, a.name AS author FROM books AS b JOIN authors AS a ON a.author_id = b.author_id; ``` ```text 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: ```text 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) ``` ```sql 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; ``` ```text 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. > [!TIP] > Before running a join, predict its row count from the relationships, then check with `SELECT count(*)`. If 10 orders joined to their items gives 16 rows, that is the 16 lines; if it gives 160 (10 × 16), the `ON` condition is wrong and you have built a cross join. ## 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`: ```sql 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; ``` ```text 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 `NULL`s 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: ```sql 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; ``` ```text 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 JOIN`s, 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: ```sql 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; ``` ```text 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*: ```sql 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; ``` ```text 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) ``` > [!IMPORTANT] > With an outer join, conditions on the **optional** (right-hand) table belong in `ON`; conditions on the **preserved** (left-hand) table belong in `WHERE`. For inner joins the two placements give identical results. ## 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: ```sql 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; ``` ```text 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: ```sql 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; ``` ```text 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. | Join | Keeps | Unmatched rows | | --- | --- | --- | | `[INNER] JOIN` | matching pairs only | dropped from both sides | | `LEFT [OUTER] JOIN` | all left rows | right columns become `NULL` | | `RIGHT [OUTER] JOIN` | all right rows | left columns become `NULL` | | `FULL [OUTER] JOIN` | all rows from both | missing side becomes `NULL` | | `CROSS JOIN` | every combination | not applicable — there is no condition | 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`. > **Interactive: Five joins, one pair of tables.** Notice that the outer joins only ever add rows to the inner join's result, and that CROSS is simply every pairing. Try it: https://levelcode.org/labs/join-visualizer ## 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": ```sql 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; ``` ```text 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: ```sql 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; ``` ```text 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 JOIN`s 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. **Solution** ```sql 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; ``` ```text 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) ``` Key decisions: the status filter goes in `ON`, so Chloe (whose only order was cancelled) is kept rather than dropped by a `WHERE`. Every join after the first is also a `LEFT JOIN`, so the padded rows survive. Without `DISTINCT` you get 17 rows, because Ada bought *The Left Hand of Darkness* twice and Ben and Dev each bought one title in two separate orders. ```sql 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; ``` ```text 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. ## Quiz 1. customers has 6 rows and orders has 10. Every order belongs to a customer, and one customer has no orders. How many rows does customers LEFT JOIN orders ON o.customer_id = c.customer_id return? A. 6 B. 10 C. 11 D. 60 **Answer: C.** Each of the 10 orders matches exactly one customer, giving 10 rows, and the customer with no orders is kept once with NULLs. 60 would be a cross join. 2. You write customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.status = 'paid'. What happens to customers with no paid orders? A. They are dropped, so the query behaves like an inner join B. They appear with NULL order columns C. They appear once per unpaid order D. The query raises an error **Answer: A.** WHERE runs after the join. Padded rows have o.status = NULL, and NULL = 'paid' is not true, so they are filtered out. Put o.status = 'paid' in the ON clause to keep them. 3. Which query finds books that have never been ordered? A. books b JOIN order_items oi ON oi.book_id = b.book_id WHERE oi.book_id IS NULL B. books b CROSS JOIN order_items oi WHERE oi.book_id <> b.book_id C. books b RIGHT JOIN order_items oi ON oi.book_id = b.book_id WHERE b.book_id IS NULL D. books b LEFT JOIN order_items oi ON oi.book_id = b.book_id WHERE oi.book_id IS NULL **Answer: D.** The LEFT JOIN keeps every book and pads unmatched ones with NULLs; filtering for a NULL join key keeps only those. An inner join never produces NULL there, and the RIGHT JOIN version looks for order items with no book, which foreign keys make impossible. 4. books has 10 rows. How many rows does books CROSS JOIN (VALUES ('paperback'), ('hardback')) AS f (format) return? A. 10 B. 20 C. 12 D. 2 **Answer: B.** A cross join returns every combination: 10 books times 2 formats is 20 rows.