Chapter 11: Joins
Up until now, we’ve been working with one table at a time. But in real life, data is almost always spread across multiple tables (customers in one table, orders in another, products in a third…). Joins are how we combine these tables to answer meaningful questions like:
- “Which customers ordered which books?”
- “Show all authors and their books — even if some authors haven’t written any books yet.”
- “Compare each book with the average price of books by the same author.”
Today we’re going to master all the major types of joins:
- INNER JOIN (the most common)
- LEFT / RIGHT / FULL OUTER JOIN (for including unmatched rows)
- CROSS JOIN (Cartesian product — every combination)
- SELF JOIN (joining a table to itself)
We’ll use our bookshop database and add a few more tables for realistic examples.
Step 1: Setup – Our Tables for This Chapter
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
USE bookshop; -- Authors (already have) -- author_id | name | country -- 1 | Ruskin Bond | India -- 2 | Jhumpa Lahiri | USA -- 3 | Chetan Bhagat | India -- 4 | Amish Tripathi | India -- 5 | Sudha Murty | India -- 6 | R.K. Narayan | India -- 7 | Khushwant Singh | India -- 8 | Chitra Banerjee | USA ← New author with NO books yet -- 9 | Vikram Seth | India ← Another with NO books -- Books (slightly updated) -- book_id | title | author_id | price | stock -- 1 | The Room on the Roof | 1 | 250.00 | 45 -- 2 | 2 States | 3 | 299.00 | 120 -- 3 | The White Tiger | 2 | 399.00 | 35 -- 4 | Wise and Otherwise | 5 | 199.00 | 80 -- 5 | The Immortals of Meluha | 4 | 349.00 | 65 -- 6 | The Blue Umbrella | 1 | 180.00 | 50 -- 7 | Malgudi Days | 6 | 220.00 | 90 -- 8 | Train to Pakistan | 7 | 280.00 | 60 |
1. INNER JOIN – Only Matching Rows from Both Tables
INNER JOIN returns only the rows where there is a match in both tables. If an author has no books → they won’t appear. If a book has no author → it won’t appear (but we have foreign key so this can’t happen).
Syntax:
|
0 1 2 3 4 5 6 7 8 |
SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column; |
Example – All books with their author names:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT b.title AS book_title, a.name AS author_name, b.price, a.country FROM books b INNER JOIN authors a ON b.author_id = a.author_id; |
Result (only authors who have books):
| book_title | author_name | price | country |
|---|---|---|---|
| The Room on the Roof | Ruskin Bond | 250.00 | India |
| 2 States | Chetan Bhagat | 299.00 | India |
| The White Tiger | Jhumpa Lahiri | 399.00 | USA |
| … | … | … | … |
Chitra Banerjee (author_id 8) and Vikram Seth (9) do NOT appear because they have no books.
2. LEFT JOIN (LEFT OUTER JOIN) – All from Left Table + Matching from Right
LEFT JOIN keeps all rows from the left table, and adds matching rows from the right table. If no match → right table columns get NULL.
Syntax:
|
0 1 2 3 4 5 6 7 |
FROM table1 LEFT JOIN table2 ON table1.column = table2.column; |
Example – All authors, and their books (if any):
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT a.name AS author_name, a.country, b.title AS book_title, b.price FROM authors a LEFT JOIN books b ON a.author_id = b.author_id ORDER BY a.name; |
Result:
| author_name | country | book_title | price |
|---|---|---|---|
| Amish Tripathi | India | The Immortals of Meluha | 349.00 |
| Chetan Bhagat | India | 2 States | 299.00 |
| Chitra Banerjee | USA | NULL | NULL |
| Jhumpa Lahiri | USA | The White Tiger | 399.00 |
| Khushwant Singh | India | Train to Pakistan | 280.00 |
| Ruskin Bond | India | The Room on the Roof | 250.00 |
| … | … | … | … |
| Vikram Seth | India | NULL | NULL |
Perfect for: “Show all authors, even those without books yet.”
RIGHT JOIN is the opposite: all from right table + matching from left. (Rarely used — most people just swap tables and use LEFT JOIN instead.)
3. FULL OUTER JOIN – All Rows from Both Tables
FULL OUTER JOIN = LEFT JOIN + RIGHT JOIN combined. You get all rows from both tables, with NULLs where no match.
Syntax:
|
0 1 2 3 4 5 6 7 |
FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column; |
Note: MySQL/MariaDB do not support FULL OUTER JOIN natively. You can simulate it with UNION of LEFT + RIGHT JOIN (excluding duplicates).
Example (simulated in MySQL):
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- All authors and all books, even if unmatched SELECT a.name AS author_name, b.title AS book_title FROM authors a LEFT JOIN books b ON a.author_id = b.author_id UNION SELECT a.name, b.title FROM authors a RIGHT JOIN books b ON a.author_id = b.author_id; |
Result: Every author appears (even without books) and every book appears (even if author missing — though not possible here).
4. CROSS JOIN – Every Possible Combination
CROSS JOIN combines every row from left with every row from right — no ON condition needed. Result: number of rows in table1 × number of rows in table2
Syntax:
|
0 1 2 3 4 5 6 7 8 9 |
FROM table1 CROSS JOIN table2; -- or just: FROM table1, table2; -- old style |
Example – Suppose we have 9 authors and 10 books:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT a.name, b.title FROM authors a CROSS JOIN books b LIMIT 5; -- just to see first few |
Result: 90 rows! Every author paired with every book. Use case: Generating all possible combinations (e.g., every customer × every product for a report).
Rarely used in production — but good to know.
5. SELF JOIN – Joining a Table to Itself
When a table has a relationship with itself (e.g., employees and their managers, books and sequels, categories and parent categories).
Example – Find authors who have written more than one book (using SELF JOIN):
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT a1.name AS author, a1.country, COUNT(DISTINCT b.book_id) AS books_written, GROUP_CONCAT(b.title SEPARATOR ', ') AS book_titles FROM authors a1 INNER JOIN books b ON a1.author_id = b.author_id GROUP BY a1.author_id, a1.name, a1.country HAVING COUNT(DISTINCT b.book_id) > 1; |
Another classic self-join example (if we had a ‘sequel_to’ column in books):
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
-- Imagine books had a column: sequel_to_book_id SELECT b1.title AS original, b2.title AS sequel FROM books b1 INNER JOIN books b2 ON b1.book_id = b2.sequel_to_book_id; |
Quick Comparison Table (2026 Style)
| Join Type | What it returns | When to use | Matching Rows? |
|---|---|---|---|
| INNER JOIN | Only matching rows from both | Common case: “books and their authors” | Yes |
| LEFT JOIN | All from left + matching from right (NULLs) | “All customers, even those with no orders” | Optional |
| RIGHT JOIN | All from right + matching from left | Rarely used (swap tables and use LEFT) | Optional |
| FULL OUTER | All from both (NULLs where no match) | “All authors and all books, even unmatched” | Optional |
| CROSS JOIN | Every combination | All possible pairs (rare) | No condition |
| SELF JOIN | Table joined to itself | Hierarchical data, comparisons within table | Depends |
Full Realistic Report Example
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- Business report: Authors and their books (including authors with zero books) SELECT a.name AS author_name, a.country, COUNT(b.book_id) AS number_of_books, COALESCE(SUM(b.stock), 0) AS total_stock, ROUND(COALESCE(AVG(b.price), 0), 2) AS average_price FROM authors a LEFT JOIN books b ON a.author_id = b.author_id GROUP BY a.author_id, a.name, a.country ORDER BY number_of_books DESC, author_name; |
