🔀 Lesson 13: Joins

Why JOINs?

Your data lives in separate tables — customers in one, orders in another, books in a third. But real questions span tables:

• "Which customers ordered Dune?"
• "What's the total revenue per author?"
• "Show me all orders with customer names and book titles."

A JOIN combines rows from two or more tables based on a related column (usually a foreign key matching a primary key). The result is a new, wider table that includes columns from both sources.

Our Sample Data

We'll use a simplified version of our bookstore schema for the examples. Here's what's in each table:

authors

books

customers

orders

order_items

Notice: Isaac Asimov (author 4) has no books in our table. Carol (customer 3) has no orders. These "unmatched" rows will become important when we compare INNER JOIN vs LEFT JOIN.

INNER JOIN

An INNER JOIN returns only rows where there's a match in both tables. If a row in one table has no matching row in the other, it's excluded from the results.

Basic Syntax

SELECT columns
FROM table_a
INNER JOIN table_b ON table_a.column = table_b.column;
                

Example: Books with Author Names

-- Show each book with its author's name
SELECT
    books.title,
    authors.name AS author
FROM books
INNER JOIN authors ON books.author_id = authors.id;
                

+---------------------+----------------+
| title               | author         |
+---------------------+----------------+
| Dune                | Frank Herbert  |
| 1984                | George Orwell  |
| Pride and Prejudice | Jane Austen    |
| Animal Farm         | George Orwell  |
+---------------------+----------------+
                    

Notice: Isaac Asimov doesn't appear — he has no books in our table. INNER JOIN only shows rows with matches on both sides.

Table Aliases

Typing full table names gets tedious. Use aliases to shorten them:

-- Same query with aliases
SELECT b.title, a.name AS author
FROM books b
INNER JOIN authors a ON b.author_id = a.id;
                

Orders with Customer Names

SELECT
    o.id AS order_id,
    c.first_name,
    c.last_name,
    o.order_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;
                

+----------+------------+-----------+------------+
| order_id | first_name | last_name | order_date |
+----------+------------+-----------+------------+
|        1 | Alice      | Johnson   | 2026-01-15 |
|        2 | Alice      | Johnson   | 2026-02-20 |
|        3 | Bob        | Smith     | 2026-03-10 |
+----------+------------+-----------+------------+
                    

Carol doesn't appear — she has no orders. INNER JOIN excludes her.

Adding WHERE to a JOIN

-- George Orwell's books only
SELECT b.title, b.price, a.name AS author
FROM books b
INNER JOIN authors a ON b.author_id = a.id
WHERE a.name = 'George Orwell';
                

+-------------+-------+---------------+
| title       | price | author        |
+-------------+-------+---------------+
| 1984        |  9.99 | George Orwell |
| Animal Farm |  8.49 | George Orwell |
+-------------+-------+---------------+
                    

The Shorthand: Just "JOIN"

JOIN without the INNER keyword is identical to INNER JOIN:

-- These are exactly the same:
SELECT b.title, a.name FROM books b INNER JOIN authors a ON b.author_id = a.id;
SELECT b.title, a.name FROM books b JOIN authors a ON b.author_id = a.id;
                

Most developers write just JOIN for brevity. Both are correct.

LEFT JOIN

A LEFT JOIN (also called LEFT OUTER JOIN) returns all rows from the left table, plus matching rows from the right table. If there's no match, the right side's columns are filled with NULL.

Example: All Authors, Even Without Books

-- All authors, including those with no books
SELECT
    a.name AS author,
    b.title
FROM authors a
LEFT JOIN books b ON a.id = b.author_id;
                

+----------------+---------------------+
| author         | title               |
+----------------+---------------------+
| Frank Herbert  | Dune                |
| George Orwell  | 1984                |
| George Orwell  | Animal Farm         |
| Jane Austen    | Pride and Prejudice |
| Isaac Asimov   | NULL                |  ← no books, but still appears
+----------------+---------------------+
                    

Isaac Asimov now appears with NULL for the title — LEFT JOIN keeps all rows from the left table (authors).

All Customers, Even Without Orders

SELECT
    c.first_name,
    c.last_name,
    o.id AS order_id,
    o.order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;
                

+------------+-----------+----------+------------+
| first_name | last_name | order_id | order_date |
+------------+-----------+----------+------------+
| Alice      | Johnson   |        1 | 2026-01-15 |
| Alice      | Johnson   |        2 | 2026-02-20 |
| Bob        | Smith     |        3 | 2026-03-10 |
| Carol      | Williams  |     NULL | NULL       |  ← no orders
+------------+-----------+----------+------------+
                    

Finding Rows Without Matches

One of the most common uses of LEFT JOIN is finding rows that don't have a match — orphans, inactive records, or gaps in your data:

-- Authors who have NO books
SELECT a.name
FROM authors a
LEFT JOIN books b ON a.id = b.author_id
WHERE b.id IS NULL;
                

+--------------+
| name         |
+--------------+
| Isaac Asimov |
+--------------+
                    

-- Customers who have never ordered
SELECT c.first_name, c.last_name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;
                

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Carol      | Williams  |
+------------+-----------+
                    

RIGHT JOIN

A RIGHT JOIN is the mirror of LEFT JOIN — it returns all rows from the right table, plus matching rows from the left. Unmatched left rows are filled with NULL.

-- All books, including those by unknown authors (if any)
SELECT a.name AS author, b.title
FROM authors a
RIGHT JOIN books b ON a.id = b.author_id;
                

-- These produce the same result:
SELECT * FROM A RIGHT JOIN B ON A.id = B.a_id;
SELECT * FROM B LEFT JOIN A ON A.id = B.a_id;
                    

CROSS JOIN

A CROSS JOIN produces the Cartesian product — every row from the left table paired with every row from the right table. No ON clause needed.

-- Every author paired with every book (regardless of who wrote what)
SELECT a.name AS author, b.title
FROM authors a
CROSS JOIN books b;
                

+----------------+---------------------+
| author         | title               |
+----------------+---------------------+
| Frank Herbert  | Dune                |
| Frank Herbert  | 1984                |
| Frank Herbert  | Pride and Prejudice |
| Frank Herbert  | Animal Farm         |
| George Orwell  | Dune                |
| George Orwell  | 1984                |
| George Orwell  | Pride and Prejudice |
| George Orwell  | Animal Farm         |
| Jane Austen    | Dune                |
| Jane Austen    | 1984                |
| Jane Austen    | Pride and Prejudice |
| Jane Austen    | Animal Farm         |
| Isaac Asimov   | Dune                |
| Isaac Asimov   | 1984                |
| Isaac Asimov   | Pride and Prejudice |
| Isaac Asimov   | Animal Farm         |
+----------------+---------------------+
                    

When CROSS JOIN Is Useful

Despite the warning, CROSS JOIN has legitimate uses:

-- Generate a grid: every size × every color
SELECT s.size_name, c.color_name
FROM sizes s
CROSS JOIN colors c;
-- Small-Red, Small-Blue, Medium-Red, Medium-Blue, etc.

-- Pair every employee with every training module
SELECT e.name, t.module_name
FROM employees e
CROSS JOIN training_modules t;
                

Self-Joins

A self-join joins a table to itself. This is useful when rows in the same table have a parent-child or peer relationship.

Employee-Manager Example

Imagine an employees table where each employee has a manager_id that references another employee's id:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Alice', NULL),    -- CEO, no manager
    (2, 'Bob', 1),         -- reports to Alice
    (3, 'Carol', 1),       -- reports to Alice
    (4, 'Dave', 2),        -- reports to Bob
    (5, 'Eve', 2);         -- reports to Bob
                

-- Show each employee with their manager's name
SELECT
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
                

+----------+---------+
| employee | manager |
+----------+---------+
| Alice    | NULL    |  ← CEO has no manager
| Bob      | Alice   |
| Carol    | Alice   |
| Dave     | Bob     |
| Eve      | Bob     |
+----------+---------+
                    

Finding Peers

-- Find pairs of books by the same author
SELECT
    b1.title AS book_1,
    b2.title AS book_2,
    a.name AS author
FROM books b1
INNER JOIN books b2 ON b1.author_id = b2.author_id AND b1.id < b2.id
INNER JOIN authors a ON b1.author_id = a.id;
                

+--------+-------------+---------------+
| book_1 | book_2      | author        |
+--------+-------------+---------------+
| 1984   | Animal Farm | George Orwell |
+--------+-------------+---------------+
                    

The trick b1.id < b2.id prevents duplicate pairs (we don't want both "1984 + Animal Farm" and "Animal Farm + 1984") and prevents a book from pairing with itself.

Joining Three or More Tables

Real queries often span many tables. Just chain additional JOINs:

Orders → Customers + Order Items + Books

-- Full order details: who bought what
SELECT
    c.first_name,
    c.last_name,
    o.order_date,
    b.title,
    oi.quantity,
    b.price
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN books b ON oi.book_id = b.id
ORDER BY o.order_date, c.last_name;
                

+------------+-----------+------------+---------------------+----------+-------+
| first_name | last_name | order_date | title               | quantity | price |
+------------+-----------+------------+---------------------+----------+-------+
| Alice      | Johnson   | 2026-01-15 | Dune                |        1 | 14.99 |
| Alice      | Johnson   | 2026-01-15 | 1984                |        2 |  9.99 |
| Alice      | Johnson   | 2026-02-20 | Pride and Prejudice |        1 |  7.99 |
| Bob        | Smith     | 2026-03-10 | Dune                |        1 | 14.99 |
+------------+-----------+------------+---------------------+----------+-------+
                    

Adding Authors to the Chain

-- Who bought books by which author?
SELECT
    c.first_name AS customer,
    b.title,
    a.name AS author,
    o.order_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN books b ON oi.book_id = b.id
INNER JOIN authors a ON b.author_id = a.id
ORDER BY c.first_name;
                

JOINs with Aggregates

JOINs combined with GROUP BY and aggregate functions unlock powerful analytics:

How Many Books Per Author?

SELECT
    a.name AS author,
    COUNT(b.id) AS book_count
FROM authors a
LEFT JOIN books b ON a.id = b.author_id
GROUP BY a.id, a.name
ORDER BY book_count DESC;
                

+----------------+------------+
| author         | book_count |
+----------------+------------+
| George Orwell  |          2 |
| Frank Herbert  |          1 |
| Jane Austen    |          1 |
| Isaac Asimov   |          0 |  ← LEFT JOIN keeps this
+----------------+------------+
                    

Total Revenue Per Customer

SELECT
    c.first_name,
    c.last_name,
    COUNT(DISTINCT o.id) AS total_orders,
    SUM(oi.quantity) AS items_bought,
    ROUND(SUM(oi.quantity * b.price), 2) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN books b ON oi.book_id = b.id
GROUP BY c.id, c.first_name, c.last_name
ORDER BY total_spent DESC;
                

+------------+-----------+--------------+--------------+-------------+
| first_name | last_name | total_orders | items_bought | total_spent |
+------------+-----------+--------------+--------------+-------------+
| Alice      | Johnson   |            2 |            4 |       42.96 |
| Bob        | Smith     |            1 |            1 |       14.99 |
| Carol      | Williams  |            0 |         NULL |        NULL |
+------------+-----------+--------------+--------------+-------------+
                    

Revenue Per Author

SELECT
    a.name AS author,
    SUM(oi.quantity) AS copies_sold,
    ROUND(SUM(oi.quantity * b.price), 2) AS revenue
FROM authors a
INNER JOIN books b ON a.id = b.author_id
INNER JOIN order_items oi ON b.id = oi.book_id
GROUP BY a.id, a.name
ORDER BY revenue DESC;
                

+---------------+-------------+---------+
| author        | copies_sold | revenue |
+---------------+-------------+---------+
| Frank Herbert |           2 |   29.98 |
| George Orwell |           2 |   19.98 |
| Jane Austen   |           1 |    7.99 |
+---------------+-------------+---------+
                    

Filtering Groups with HAVING

-- Authors with total revenue over $15
SELECT
    a.name AS author,
    ROUND(SUM(oi.quantity * b.price), 2) AS revenue
FROM authors a
INNER JOIN books b ON a.id = b.author_id
INNER JOIN order_items oi ON b.id = oi.book_id
GROUP BY a.id, a.name
HAVING revenue > 15
ORDER BY revenue DESC;
                

Choosing the Right JOIN

*MySQL doesn't support FULL OUTER JOIN directly, but you can emulate it with a UNION of LEFT and RIGHT JOINs.

Exercises

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

-- 2
SELECT o.id AS order_id, o.order_date,
       CONCAT(c.first_name, ' ', c.last_name) AS customer
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

-- 3
SELECT oi.id, b.title, oi.quantity
FROM order_items oi
INNER JOIN books b ON oi.book_id = b.id;

-- 4
SELECT b.title, b.price
FROM books b
INNER JOIN authors a ON b.author_id = a.id
WHERE a.name = 'George Orwell';
                        

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

-- 2
SELECT a.name AS author
FROM authors a
LEFT JOIN books b ON a.id = b.author_id
WHERE b.id IS NULL;

-- 3
SELECT
    c.first_name,
    c.last_name,
    COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name;

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

-- 1
SELECT
    c.first_name,
    c.last_name,
    b.title,
    oi.quantity
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.id
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN books b ON oi.book_id = b.id;

-- 2
SELECT
    a.name AS author,
    ROUND(SUM(oi.quantity * b.price), 2) AS revenue
FROM authors a
INNER JOIN books b ON a.id = b.author_id
INNER JOIN order_items oi ON b.id = oi.book_id
GROUP BY a.id, a.name
ORDER BY revenue DESC;

-- 3
SELECT DISTINCT c.first_name, c.last_name
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN books b ON oi.book_id = b.id
INNER JOIN authors a ON b.author_id = a.id
WHERE a.name = 'George Orwell';
                        

SELECT
    CONCAT(c.first_name, ' ', c.last_name) AS customer,
    COUNT(DISTINCT o.id) AS total_orders,
    COALESCE(SUM(oi.quantity), 0) AS items_bought,
    ROUND(SUM(oi.quantity * b.price), 2) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN books b ON oi.book_id = b.id
GROUP BY c.id, c.first_name, c.last_name
ORDER BY total_spent IS NULL, total_spent DESC;
                        

Summary

Quick Reference

📚 Additional Resources

• MySQL Docs — JOIN Clause
• MySQL Docs — Nested Join Optimization
• MySQL Docs — Outer Join Simplification

🚀 What's Next?

That completes Module 4: Relationships & Joins! You've gone from single-table queries to multi-table analytics — a massive leap. In Module 5: Database Design, you'll learn to make everything faster and cleaner: Indexes for query optimization, Views for simplifying complex queries, and Normalization for designing efficient schemas from scratch.