Chapter 7: JOINs — Combining Tables

JOINs are the most powerful feature of relational databases. They let you combine data from multiple tables in a single query. This is what makes the relational model so flexible — you store data normalized (no duplication) and combine it on the fly.

Sample Data

-- employees
┌────┬─────────┬─────────┐
│ id │  name   │ dept_id │
├────┼─────────┼─────────┤
│  1 │ Alice   │    1    │
│  2 │ Bob     │    2    │
│  3 │ Charlie │    1    │
│  4 │ Dave    │   NULL  │  ← no department assigned
└────┴─────────┴─────────┘

-- departments
┌────┬─────────────┐
│ id │    name     │
├────┼─────────────┤
│  1 │ Engineering │
│  2 │ Marketing   │
│  3 │ Sales       │  ← no employees in this dept
└────┴─────────────┘

INNER JOIN

Returns only rows that have a match in both tables.

SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- Result: (only matched rows)
-- Alice   | Engineering
-- Bob     | Marketing
-- Charlie | Engineering
-- Dave is excluded (dept_id is NULL, no match)
-- Sales is excluded (no employee references it)

LEFT JOIN (LEFT OUTER JOIN)

Returns ALL rows from the left table, plus matching rows from the right. If no match, right side is NULL.

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- Result: (all employees, even unmatched)
-- Alice   | Engineering
-- Bob     | Marketing
-- Charlie | Engineering
-- Dave    | NULL          ← included! department is NULL

RIGHT JOIN (RIGHT OUTER JOIN)

Returns ALL rows from the right table, plus matching rows from the left.

SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

-- Result: (all departments, even empty ones)
-- Alice   | Engineering
-- Bob     | Marketing
-- Charlie | Engineering
-- NULL    | Sales         ← included! no employees

In practice, RIGHT JOIN is rarely used — just swap the table order and use LEFT JOIN instead.

FULL OUTER JOIN

Returns ALL rows from both tables. NULLs where there's no match on either side.

SELECT e.name, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

-- Result:
-- Alice   | Engineering
-- Bob     | Marketing
-- Charlie | Engineering
-- Dave    | NULL          ← unmatched employee
-- NULL    | Sales         ← unmatched department

CROSS JOIN (Cartesian Product)

Every row from table A combined with every row from table B. Rarely useful, often accidental.

SELECT e.name, d.name
FROM employees e
CROSS JOIN departments d;

-- 4 employees × 3 departments = 12 rows
-- Alice | Engineering
-- Alice | Marketing
-- Alice | Sales
-- Bob   | Engineering
-- ... (12 total)

Self JOIN

A table joined to itself. Useful for hierarchical data:

-- employees table with manager_id (self-referencing)
-- id | name    | manager_id
--  1 | Alice   | NULL        (CEO, no manager)
--  2 | Bob     | 1           (reports to Alice)
--  3 | Charlie | 1           (reports to Alice)
--  4 | Dave    | 2           (reports to Bob)

SELECT
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Result:
-- Alice   | NULL    (no manager)
-- Bob     | Alice
-- Charlie | Alice
-- Dave    | Bob

Multi-Table JOINs

-- Get order details with customer and product names
SELECT
    o.id AS order_id,
    c.name AS customer,
    p.name AS product,
    oi.quantity,
    oi.quantity * p.price AS line_total
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
WHERE o.created_at > '2024-01-01'
ORDER BY o.id;

JOIN Visual Summary

Performance Tip