Chapter 5: Joins

A relational database splits information across tables on purpose. products doesn't repeat the category name on every row; it stores a category_id and points to categories for the rest. This keeps data small and consistent: rename a category once, and every product referencing it is automatically up to date.

The cost of that design is that useful answers rarely live in one table. "Which category is each product in?" needs products and categories together. A JOIN is how SQL puts split tables back into a single result.

---

5.1 Why Joins Exist

SELECT product_name, category_id FROM production.products;

product_name              | category_id
----------------------------+-------------
Trek 820 - 2016               | 6
Surly Wednesday Frameset      | 6

category_id is a number, meaningless on its own. The actual name lives in a separate table:

SELECT category_id, category_name FROM production.categories;

category_id | category_name
-------------+-------------------
1            | Children Bicycles
2            | Comfort Bicycles
3            | Cruisers Bicycles
4            | Cyclocross Bicycles
5            | Electric Bikes
6            | Mountain Bikes
7            | Road Bikes

A JOIN matches rows from both tables where a condition is true, usually where a foreign key in one table equals the primary key in another, and returns them as one combined row.

Join type	Returns
INNER JOIN	Only rows that match on both sides
LEFT JOIN	All rows from the left table, matched where possible
RIGHT JOIN	All rows from the right table, matched where possible
FULL OUTER JOIN	All rows from both tables
CROSS JOIN	Every combination of rows from both tables

---

5.2 INNER JOIN

SELECT
    p.product_name,
    c.category_name,
    p.list_price
FROM production.products AS p
INNER JOIN production.categories AS c
    ON p.category_id = c.category_id;

product_name              | category_name    | list_price
----------------------------+-------------------+-----------
Trek 820 - 2016               | Mountain Bikes   | 379.99
Surly Wednesday Frameset      | Mountain Bikes   | 999.99
Heller Shagamaw Frame         | Mountain Bikes   | 1320.99

p and c are table aliases, the same idea as column aliases from Chapter 4, but for tables. They're not optional once two tables share a column name like category_id; SQL needs to know which table's column you mean, and p.category_id is unambiguous where category_id alone is not.

   products              categories
  ┌─────────┐          ┌─────────────┐
  │         │          │             │
  │    ┌────┼──────────┼────┐        │
  │    │  matched rows only │        │
  │    └────┼──────────┼────┘        │
  │         │          │             │
  └─────────┘          └─────────────┘
        INNER JOIN = the overlap

INNER JOIN is the default people mean when they just say "join." If a product's category_id doesn't match any row in categories (which shouldn't happen with a foreign key constraint in place, but could in a less disciplined schema), that product silently disappears from the result. Nothing warns you. This is the main reason LEFT JOIN exists.

Interview Angle "What's the difference between INNER JOIN and LEFT JOIN?" is asked in nearly every SQL interview. The strong answer isn't just naming the keywords; it's explaining which rows are allowed to disappear silently when there's no match, and why that matters for the question being asked.

A second example joins a different pair of tables: every order alongside the store that handled it:

SELECT
    o.order_id,
    o.order_date,
    s.store_name,
    s.city
FROM sales.orders AS o
INNER JOIN sales.stores AS s
    ON o.store_id = s.store_id
WHERE o.order_id IN (1, 2, 3);

order_id | order_date | store_name        | city
---------+------------+--------------------+----------
1        | 2018-01-02 | Santa Cruz Bikes    | Santa Cruz
2        | 2018-01-03 | Baldwin Bikes       | Baldwin
3        | 2018-01-04 | Santa Cruz Bikes    | Santa Cruz

Same mechanism as the products-categories join: match on a foreign key, return only the rows where that match succeeds, applied to a completely different pair of tables.

---

5.3 LEFT JOIN

The merchandising team wants to know which products have never sold, specifically products with zero rows in order_items. An INNER JOIN between products and order_items can't answer this: a product with no order rows simply wouldn't appear, indistinguishable from a product that doesn't exist.

SELECT
    p.product_name,
    oi.order_id
FROM production.products AS p
LEFT JOIN sales.order_items AS oi
    ON p.product_id = oi.product_id
WHERE oi.order_id IS NULL;

product_name                | order_id
------------------------------+----------
Ritchey Timberwolf Frameset    | NULL
Strider Classic 12 Balance Bike| NULL

LEFT JOIN keeps every row from the left table (products) regardless of whether a match exists on the right. Where no match exists, every column from the right table comes back as NULL, which is exactly the signal WHERE oi.order_id IS NULL is filtering for. This pattern, a LEFT JOIN plus a NULL check on the right side, is how you find "things with no related record" in SQL, and it's worth recognizing on sight.

Filtering on the right table's column in WHERE versus in the ON clause changes the result entirely. ON p.product_id = oi.product_id AND oi.quantity > 5 still keeps every product; it only restricts which order_items rows are allowed to match. WHERE oi.quantity > 5 after the same join throws away every product with no orders at all, because NULL > 5 is never true. Put right-table filters in ON when you want to keep every left row; put them in WHERE when you genuinely want to exclude non-matches.

The same pattern answers a different question just as easily: which customers have signed up but never placed a single order?

SELECT
    c.first_name,
    c.last_name,
    o.order_id
FROM sales.customers AS c
LEFT JOIN sales.orders AS o
    ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

first_name | last_name | order_id
-----------+-----------+----------
Aaron      | Knapp     | NULL
Latoya     | Bird      | NULL

Identical shape to the products example; swap the tables and the logic doesn't change. Once you recognize "LEFT JOIN plus IS NULL on the right side," you can point it at any one-to-many relationship in the database and ask the same question: what's on the left side with nothing matching on the right.

Interview Angle "How would you find customers with no orders?", or any close variant of it, is one of the most common SQL interview prompts. Recognizing it instantly as LEFT JOIN plus a NULL check on the right side is the whole answer; no other technique is needed.

---

5.4 RIGHT JOIN

SELECT
    p.product_name,
    oi.order_id
FROM sales.order_items AS oi
RIGHT JOIN production.products AS p
    ON oi.product_id = p.product_id
WHERE oi.order_id IS NULL;

This returns the identical result as the LEFT JOIN above; RIGHT JOIN is the same operation with the tables written in reverse. Most style guides, including this book's, stick to LEFT JOIN exclusively and just reorder the FROM/JOIN tables instead. One join direction to remember is one less thing to get wrong at 2am.

A second example shows the same equivalence with a different pair of tables: every staff member alongside their store, written as a RIGHT JOIN:

SELECT
    st.store_name,
    s.first_name,
    s.last_name
FROM sales.staffs AS s
RIGHT JOIN sales.stores AS st
    ON s.store_id = st.store_id;

store_name        | first_name | last_name
-------------------+------------+-----------
Santa Cruz Bikes    | Fabiola    | Jackson
Santa Cruz Bikes    | Mireya     | Copeland
Baldwin Bikes        | Genna      | Serrano
Rowlett Bikes        | NULL       | NULL

Written as a LEFT JOIN instead (sales.stores AS st LEFT JOIN sales.staffs AS s), this produces the exact same four rows, including Rowlett Bikes with no staff assigned yet. The two queries are identical in meaning; only the table order and keyword differ.

---

5.5 FULL OUTER JOIN

FULL OUTER JOIN keeps unmatched rows from both sides at once.

SELECT
    p.product_name,
    s.quantity
FROM production.products AS p
FULL OUTER JOIN production.stocks AS s
    ON p.product_id = s.product_id AND s.store_id = 1;

product_name                | quantity
------------------------------+----------
Trek 820 - 2016                | 12
Surly Wednesday Frameset       | 0
Strider Classic 12 Balance Bike| NULL

The third row is a product that store 1 has never stocked at all — no row exists for it in stocks. In a schema this clean, with foreign keys enforced everywhere, FULL OUTER JOIN often behaves identically to a LEFT JOIN. Its real value shows up reconciling two tables where neither side is guaranteed complete: comparing this month's inventory count against last month's, or matching transactions between two independently maintained systems where mismatches in either direction are exactly what you're hunting for.

A second example shows the other direction of mismatch: a brand carried in the catalog with zero products currently listed under it:

SELECT
    b.brand_name,
    p.product_name
FROM production.brands AS b
FULL OUTER JOIN production.products AS p
    ON b.brand_id = p.brand_id
WHERE p.product_id IS NULL OR b.brand_id IS NULL;

brand_name  | product_name
-------------+---------------
Pure Cycles   | NULL

Pure Cycles exists as a registered brand but has no product rows pointing to it yet. With products.brand_id enforced by a foreign key, the reverse case (a product pointing to a brand that doesn't exist) can never happen, which is exactly why this query never returns a row with brand_name IS NULL. FULL OUTER JOIN checks both directions regardless; here, only one direction has anything to find.

---

5.6 CROSS JOIN

CROSS JOIN has no ON clause; it pairs every row on the left with every row on the right, producing all possible combinations.

SELECT
    s.store_name,
    c.category_name
FROM sales.stores AS s
CROSS JOIN production.categories AS c;

store_name        | category_name
-------------------+-------------------
Santa Cruz Bikes    | Children Bicycles
Santa Cruz Bikes    | Comfort Bicycles
Santa Cruz Bikes    | Mountain Bikes
Baldwin Bikes       | Children Bicycles
Baldwin Bikes       | Comfort Bicycles
...

3 stores × 7 categories returns 21 rows; every combination exists whether or not that store has ever sold a bike from that category. This is deliberate: a planning team building a per-store, per-category inventory target template needs a row for every combination upfront, including the ones that are currently empty, before filling in real numbers.

A second example crosses a different pair of small tables: every brand against every store, to scaffold a "which brands should this store carry" decision sheet:

SELECT
    st.store_name,
    b.brand_name
FROM sales.stores AS st
CROSS JOIN production.brands AS b
WHERE st.store_id = 1;

store_name        | brand_name
-------------------+------------
Santa Cruz Bikes    | Electra
Santa Cruz Bikes    | Haro
Santa Cruz Bikes    | Heller
Santa Cruz Bikes    | Pure Cycles
Santa Cruz Bikes    | Trek

Filtering to one store keeps the output readable while still demonstrating the same all-combinations behavior: every brand paired with that one store, regardless of what the store currently stocks.

Interview Angle The risk worth naming isn't the deliberate CROSS JOIN shown here; it's the accidental one: an old-style comma join (FROM a, b) where someone forgot the WHERE clause meant to relate the two tables. It compiles and runs without complaint; it just silently returns every combination instead of the matched rows anyone intended.

---

5.7 Self Join

A self join joins a table to itself, useful when a table contains a reference to another row in the same table. sales.staffs has exactly this: manager_id points to another staff_id in the same table.

SELECT
    e.first_name + ' ' + e.last_name AS employee,
    m.first_name + ' ' + m.last_name AS manager
FROM sales.staffs AS e
LEFT JOIN sales.staffs AS m
    ON e.manager_id = m.staff_id;

employee          | manager
-------------------+------------------
Fabiola Jackson     | NULL
Mireya Copeland     | Fabiola Jackson
Genna Serrano       | Mireya Copeland
Virgie Wiggins      | Mireya Copeland

The same table is given two different aliases (e for the employee's row, m for the manager's row), so the join can treat them as two separate tables even though both come from staffs. Fabiola Jackson has no manager, which means e.manager_id is NULL; a LEFT JOIN is essential here, since an INNER JOIN would silently drop the top of the org chart entirely.

Not every self join follows a hierarchy. A second example finds pairs of customers who live in the same city, useful for a regional marketing campaign that wants to group local customers together:

SELECT
    c1.first_name + ' ' + c1.last_name AS customer_a,
    c2.first_name + ' ' + c2.last_name AS customer_b,
    c1.city
FROM sales.customers AS c1
INNER JOIN sales.customers AS c2
    ON c1.city = c2.city AND c1.customer_id < c2.customer_id;

customer_a       | customer_b      | city
------------------+-----------------+-----------
Maria Lopez        | Daniel Cho       | Sacramento
Priya Nair          | James Tran       | San Diego

c1.customer_id < c2.customer_id is doing important work here; without it, every pair would appear twice (once each direction) and every customer would also match themselves. This is a comparison self join: instead of following a hierarchy like the staff example, it's comparing rows in the same table against each other on a shared attribute.

Interview Angle "What is a self join and when would you use one?" almost always expects both versions in the answer: the hierarchy case and the row-comparison case. Naming only one suggests the concept was memorized from a single example rather than actually understood.

---

5.8 The Fan-Out Problem

This is the join behavior most likely to produce a wrong number that looks completely correct.

SELECT
    o.order_id,
    o.order_date,
    oi.product_id,
    oi.quantity
FROM sales.orders AS o
INNER JOIN sales.order_items AS oi
    ON o.order_id = oi.order_id
WHERE o.order_id = 1;

order_id | order_date | product_id | quantity
---------+------------+------------+----------
1        | 2018-01-02 | 6          | 1
1        | 2018-01-02 | 14         | 2
1        | 2018-01-02 | 21         | 1

Order 1 had three line items, so joining to order_items returned three rows for one order; the order row was duplicated once per matching item. This is correct and expected. It becomes a bug only when you forget it happened:

-- Wrong: counts line items, not orders
SELECT COUNT(*) AS total_orders
FROM sales.orders AS o
INNER JOIN sales.order_items AS oi ON o.order_id = oi.order_id;

total_orders
------------
4658

There are nowhere near 4,658 orders in this database; that number counts every line item across every order, because the join multiplied each order row by however many items it had. The fix, once COUNT(DISTINCT ...) is available, is COUNT(DISTINCT o.order_id) (counting unique order IDs rather than rows). We use this properly once aggregation is introduced in Chapter 6; the lesson to take now is purely about the join itself: any one-to-many join multiplies rows on the "one" side, and any count, sum, or average taken straight off that result without accounting for the multiplication will be wrong, often by a lot, with no error to warn you.

Interview Angle A joined COUNT(*) that looks reasonable but is quietly wrong is a favorite SQL interview scenario, separating people who can write a join from people who understand what a join actually does to row counts.

---

5.9 Joining More Than Two Tables

Real reports usually chain several joins together. Each JOIN adds one more table to the same result:

SELECT
    c.first_name,
    c.last_name,
    p.product_name,
    oi.quantity,
    o.order_date
FROM sales.customers AS c
INNER JOIN sales.orders AS o
    ON c.customer_id = o.customer_id
INNER JOIN sales.order_items AS oi
    ON o.order_id = oi.order_id
INNER JOIN production.products AS p
    ON oi.product_id = p.product_id
WHERE c.customer_id = 42;

first_name | last_name | product_name              | quantity | order_date
-----------+-----------+----------------------------+----------+------------
Maria      | Lopez     | Trek 820 - 2016              | 1        | 2018-03-14
Maria      | Lopez     | Surly Wednesday Frameset     | 1        | 2018-03-14

Each join narrows down to the table that has the next piece of information; customers doesn't know about products, but orders connects to both customers and order_items, and order_items connects to products. Following the foreign keys one hop at a time, in either direction, is how every multi-table query in this book gets built.

---

5.10 Putting It Together

Customer service needs a worklist: every product on every order that's still pending, with the customer's name attached, most recent first.

SELECT
    c.first_name,
    c.last_name,
    o.order_id,
    o.order_date,
    p.product_name,
    oi.quantity
FROM sales.orders AS o
INNER JOIN sales.customers AS c
    ON o.customer_id = c.customer_id
INNER JOIN sales.order_items AS oi
    ON o.order_id = oi.order_id
INNER JOIN production.products AS p
    ON oi.product_id = p.product_id
WHERE o.order_status = 1
ORDER BY o.order_date DESC;

first_name | last_name | order_id | order_date | product_name           | quantity
-----------+-----------+----------+------------+-------------------------+----------
Daniel     | Cho       | 1542     | 2018-12-19 | Trek Fuel EX 8 29        | 1
Priya      | Nair      | 1538     | 2018-12-14 | Surly Wednesday Frameset| 2

Three joins, a WHERE filter from Chapter 4, and an ORDER BY, the two chapters working together to answer one real question.

---

Summary

A JOIN combines rows from two tables based on a matching condition, usually a foreign key relationship. INNER JOIN returns only matched rows; LEFT JOIN keeps every row from the left table and fills unmatched right-side columns with NULL, the standard way to find rows with no related record. RIGHT JOIN is the mirror of LEFT JOIN; most style guides avoid it for consistency. FULL OUTER JOIN keeps unmatched rows from both sides, most useful when reconciling two independently maintained tables. CROSS JOIN produces every combination of rows from two tables, with no ON clause. A self join joins a table to itself using two aliases, useful for hierarchical data like an employee-manager relationship. Joining tables on a one-to-many relationship multiplies rows on the "one" side, a fact that must be accounted for before counting or summing anything from a joined result. Multiple joins chain together by following foreign keys one relationship at a time.

---

Exercises

5.1. Write a query that lists every product with its brand name, using production.products and production.brands.

5.2. Using a LEFT JOIN, write a query that finds every store with zero staff currently assigned to it.

5.3. Explain, in your own words, why the following query returns every product regardless of stock level, and rewrite it so that it only returns products with fewer than 5 units in stock at store 1:

SELECT p.product_name, s.quantity
FROM production.products AS p
LEFT JOIN production.stocks AS s
    ON p.product_id = s.product_id AND s.quantity < 5 AND s.store_id = 1;

5.4. Write a self join on sales.staffs that lists every manager along with a count placeholder column showing 1 for each employee they manage (you will replace this with a real COUNT in Chapter 6); for now, just produce one row per employee-manager pair.

5.5. Write a three-table join across sales.orders, sales.order_items, and production.products that lists every item in order #1, including product name and quantity.

5.6. A colleague joins orders to order_items and reports "we received 4,658 orders last year." Explain what actually went wrong with that number and how you would find the true order count instead.

5.7. Think About It: CROSS JOIN is often considered dangerous because it can accidentally produce millions of rows from two innocent-looking tables. Under what real circumstances would you deliberately want a CROSS JOIN, and how would you guard against using one by accident, for example, by forgetting an ON clause that was meant to be there?