Chapter 3

Chapter 3 — RIGHT JOIN, CROSS JOIN, Self-Joins, and Multi-Table Joins

The previous chapter covered the two joins you'll write 90% of the time — INNER JOIN and LEFT JOIN. This chapter handles the rest of the toolkit: RIGHT JOIN (rarely needed, but good to recognise), CROSS JOIN (combinatorial queries and test data), self-joins (comparing rows in the same table), and the practical mechanics of chaining three or more tables together without losing your mind.

RIGHT JOIN

All rows from the right table; matching rows from the left. NULLs where the left has no match. Usually rewritable as a LEFT JOIN by swapping table order.

CROSS JOIN

Every row in table A paired with every row in table B — no join condition. Produces m × n rows. Use deliberately: test data grids, pricing matrices, calendar generation.

Self-Join

A table joined to itself using two aliases. Classic uses: employees and their managers, comparing pairs of rows, finding records that relate to each other.

3+ Table Joins

Chain joins one at a time. Give every table a short alias. Think of each JOIN as "add another column source." MySQL plans the order for you.

Schema reminder — all examples use the extended bookshop schema from Chapter 1: books, authors, book_authors, publishers, customers, orders, order_items, reviews. Setup scripts are in Chapter 1 if you need to recreate the tables.

1. RIGHT JOIN

A RIGHT JOIN is the mirror image of a LEFT JOIN. It keeps all rows from the right table and fills in NULLs for columns from the left table wherever there is no match.

RIGHT JOIN — right side highlighted

Syntax

SELECT columns FROM left_table RIGHT JOIN right_table ON left_table.id = right_table.left_id;

Example — find publishers with no books yet

We want every publisher in the publishers table, even those that haven't had any books added to the books table yet.

-- Publishers that have no books in the catalogue SELECT p.publisher_id, p.name AS publisher, b.title AS book_title FROM books b RIGHT JOIN publishers p ON b.publisher_id = p.publisher_id WHERE b.book_id IS NULL ORDER BY p.name;

publisher_id	publisher	book_title
7	Horizon Press	NULL
11	Velvet Pages	NULL

The rewrite rule — prefer LEFT JOIN

Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the tables. Most developers use LEFT JOIN exclusively because it reads left-to-right the way you think about it: "start with this table, optionally attach that table."

-- Identical result — LEFT JOIN version (preferred style) SELECT p.publisher_id, p.name AS publisher, b.title AS book_title FROM publishers p LEFT JOIN books b ON b.publisher_id = p.publisher_id WHERE b.book_id IS NULL ORDER BY p.name;

Style rule: Always rewrite RIGHT JOINs as LEFT JOINs by swapping the table order. Mixing LEFT and RIGHT JOIN in the same query is legal but extremely hard to read. Pick one direction and stick to it — LEFT JOIN convention is almost universal.

When RIGHT JOIN is genuinely useful

You might see RIGHT JOIN in legacy code or when someone finds it easier to express a query that way. Accept it, understand it, but in new code always convert to LEFT JOIN.

2. CROSS JOIN — Cartesian Products

A CROSS JOIN produces every combination of rows from two tables. If table A has 5 rows and table B has 4 rows, the result has 5 × 4 = 20 rows. There is no ON condition — every row is paired with every other row.

Size warning: On large tables CROSS JOIN is catastrophic. 1,000 rows × 1,000 rows = 1,000,000 rows. Always know the cardinality of both sides before writing one, and add a WHERE clause if you only want a subset of combinations.

Syntax

SELECT a.col, b.col FROM table_a a CROSS JOIN table_b b;

Use case 1 — pricing matrix

Generate every combination of book format (hardcover, paperback, ebook) and discount tier (0%, 10%, 20%) to build a pricing grid:

-- Small helper tables — create temporarily for this example CREATE TEMPORARY TABLE formats (format VARCHAR(20)); INSERT INTO formats VALUES ('Hardcover'), ('Paperback'), ('Ebook'); CREATE TEMPORARY TABLE discounts (pct DECIMAL(4,2)); INSERT INTO discounts VALUES (0.00), (0.10), (0.20); -- All 9 combinations SELECT f.format, d.pct AS discount, ROUND(14.99 * (1 - d.pct), 2) AS sale_price FROM formats f CROSS JOIN discounts d ORDER BY f.format, d.pct;

format	discount	sale_price
Ebook	0.00	14.99
Ebook	0.10	13.49
Ebook	0.20	11.99
Hardcover	0.00	14.99
Hardcover	0.10	13.49
Hardcover	0.20	11.99
Paperback	0.00	14.99
Paperback	0.10	13.49
Paperback	0.20	11.99

Use case 2 — generate test data

Cross joining a list of first names with last names quickly builds a large customer test set:

-- 4 first names × 5 last names = 20 synthetic customers SELECT CONCAT(fn.first_name, ' ', ln.last_name) AS full_name, CONCAT( LOWER(fn.first_name), '.', LOWER(ln.last_name), '@example.com' ) AS email FROM ( SELECT 'Alice' AS first_name UNION ALL SELECT 'Bob' UNION ALL SELECT 'Clara' UNION ALL SELECT 'Diego' ) fn CROSS JOIN ( SELECT 'Smith' AS last_name UNION ALL SELECT 'Jones' UNION ALL SELECT 'Brown' UNION ALL SELECT 'Davis' UNION ALL SELECT 'Wilson' ) ln;

Implicit CROSS JOIN — a common accident

If you list two tables in FROM without a join condition, MySQL performs a CROSS JOIN silently. This is how beginners accidentally create million-row result sets:

-- ACCIDENTAL cross join — missing ON clause SELECT * FROM books, authors; -- ← every book × every author -- What was intended SELECT * FROM books b JOIN book_authors ba ON ba.book_id = b.book_id JOIN authors a ON a.author_id = ba.author_id;

Always write explicit JOIN syntax. The comma-separated FROM style (old SQL-89 syntax) hides cross joins and makes it easy to forget a condition. Modern SQL uses explicit JOIN … ON everywhere.

3. Self-Join — a Table Joined to Itself

A self-join is not a special JOIN keyword — it's just joining a table to itself using two different aliases. It's the standard solution whenever rows in the same table relate to each other.

Classic example — employee/manager hierarchy

Add a staff table to the bookshop schema to demonstrate:

CREATE TABLE IF NOT EXISTS staff ( staff_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, role VARCHAR(60), manager_id INT DEFAULT NULL, -- FK → staff.staff_id FOREIGN KEY (manager_id) REFERENCES staff(staff_id) ); INSERT INTO staff (name, role, manager_id) VALUES ('Sandra Holt', 'General Manager', NULL), ('Tom Briggs', 'Buying Manager', 1), ('Rita Cho', 'Events Manager', 1), ('Kenji Mori', 'Buyer', 2), ('Amara Osei', 'Buyer', 2), ('Lena Fischer', 'Events Coordinator',3);

Self-join — show each staff member with their manager

SELECT emp.name AS employee, emp.role AS employee_role, mgr.name AS manager, mgr.role AS manager_role FROM staff emp LEFT JOIN staff mgr ON emp.manager_id = mgr.staff_id ORDER BY mgr.name NULLS FIRST, emp.name;

employee	employee_role	manager	manager_role
Sandra Holt	General Manager	NULL	NULL
Kenji Mori	Buyer	Tom Briggs	Buying Manager
Amara Osei	Buyer	Tom Briggs	Buying Manager
Lena Fischer	Events Coordinator	Rita Cho	Events Manager
Tom Briggs	Buying Manager	Sandra Holt	General Manager
Rita Cho	Events Manager	Sandra Holt	General Manager

Why LEFT JOIN? Sandra Holt has no manager (manager_id IS NULL). An INNER JOIN would drop her from the results — we'd silently lose the top of the hierarchy. Always use LEFT JOIN in self-joins unless you are certain every row has a parent.

Self-join — find pairs of books by the same publisher

Self-joins also work for comparing sibling rows — finding books that share something:

-- Pairs of books published by the same publisher (no duplicates, no self-pairs) SELECT a.title AS book_a, b.title AS book_b, p.name AS publisher FROM books a JOIN books b ON a.publisher_id = b.publisher_id AND a.book_id < b.book_id -- prevents (A,B) and (B,A) both appearing JOIN publishers p ON a.publisher_id = p.publisher_id ORDER BY p.name, a.title;

The < trick: When pairing rows in a self-join, a.id < b.id ensures each pair appears exactly once and a row is never paired with itself. Use <= only if you want self-pairs (a row matching itself).

4. Joining Three or More Tables

Multi-table joins are nothing more than chaining — each JOIN adds another "column source" to the growing result set. MySQL evaluates them left to right unless you explicitly hint otherwise. The key to readable multi-table queries is consistent, short table aliases.

Three-table join — books with their authors and publisher

-- books → book_authors (bridge) → authors → publishers SELECT b.title, CONCAT(a.first_name, ' ', a.last_name) AS author, p.name AS publisher FROM books b JOIN book_authors ba ON ba.book_id = b.book_id JOIN authors a ON a.author_id = ba.author_id JOIN publishers p ON p.publisher_id = b.publisher_id ORDER BY b.title;

Five-table join — full order line detail

A realistic reporting query: every order line with customer name, book title, author, and price paid.

SELECT o.order_id, o.order_date, CONCAT(c.first_name, ' ', c.last_name) AS customer, b.title AS book, CONCAT(a.first_name, ' ', a.last_name) AS author, oi.quantity, oi.unit_price, (oi.quantity * oi.unit_price) AS line_total FROM orders o JOIN customers c ON c.customer_id = o.customer_id JOIN order_items oi ON oi.order_id = o.order_id JOIN books b ON b.book_id = oi.book_id JOIN book_authors ba ON ba.book_id = b.book_id JOIN authors a ON a.author_id = ba.author_id ORDER BY o.order_date DESC, o.order_id, b.title;

Beware fan-out with many-to-many: If a book has two authors, each order line for that book will appear twice in this result — once per author. That's correct for listing authors, but it will double-count line_total in a SUM. To aggregate safely, either pick one author per book (MAX/MIN on author name), or aggregate before joining authors.

Mixing INNER and LEFT JOIN in the same query

You can — and often should — mix join types. Inner joins for required relationships, left joins for optional ones:

-- All books: required author + publisher, optional review score SELECT b.title, CONCAT(a.first_name, ' ', a.last_name) AS author, p.name AS publisher, ROUND(AVG(r.rating), 1) AS avg_rating, COUNT(r.review_id) AS review_count FROM books b JOIN book_authors ba ON ba.book_id = b.book_id -- required JOIN authors a ON a.author_id = ba.author_id -- required JOIN publishers p ON p.publisher_id = b.publisher_id -- required LEFT JOIN reviews r ON r.book_id = b.book_id -- optional GROUP BY b.book_id, b.title, a.first_name, a.last_name, p.name ORDER BY avg_rating DESC NULLS LAST;

Alias discipline — a worked example

With five tables in a query, ambiguous column names become a real problem. Always qualify every column with its alias. A consistent naming convention helps:

-- Convention: 1-2 letter alias = table initial(s) -- o = orders c = customers -- oi = order_items b = books -- p = publishers a = authors -- r = reviews ba = book_authors -- s = staff -- Never write: SELECT title, name, first_name ... -- Always write: SELECT b.title, p.name, a.first_name ...

Join order doesn't change correctness — MySQL's query planner reorders joins for performance. But write them in a logical sequence (start from the "central" table, radiate outward) so the query is easy to read and debug.

Putting It Together — A Worked Exercise

Build a query that answers: "Which customers have never left a review for any book they purchased?"

This requires four tables: customers, orders, order_items, and reviews.

-- Step 1: identify all books each customer has purchased -- Step 2: left-join reviews so un-reviewed purchases show NULL -- Step 3: filter to customers where ALL their purchases have NULL reviews SELECT DISTINCT c.customer_id, CONCAT(c.first_name, ' ', c.last_name) AS customer, c.email FROM customers c JOIN orders o ON o.customer_id = c.customer_id JOIN order_items oi ON oi.order_id = o.order_id LEFT JOIN reviews r ON r.book_id = oi.book_id AND r.customer_id = c.customer_id WHERE r.review_id IS NULL ORDER BY customer;

Why put the customer condition inside ON, not WHERE? If we move r.customer_id = c.customer_id to the WHERE clause, it effectively turns the LEFT JOIN into an INNER JOIN — any row with a non-matching review would be filtered out. Keeping it in ON means "match only reviews that belong to this customer; if none exist, keep the row anyway." Chapter 2 covered this pattern in detail.

Quick Reference

Join type	What it returns	Typical use	Watch out for
RIGHT JOIN	All rows from right table + matching rows from left (NULLs where no match)	"Find right-table rows with no left-table partner" — though usually rewritten as LEFT JOIN	Mixing LEFT and RIGHT in one query; just rewrite as LEFT JOIN
CROSS JOIN	Every row in A × every row in B — no condition	Pricing matrices, test data generation, calendar grids	Accidental cross join via comma-FROM; exponential row count on large tables
Self-join	Rows from the same table related to each other via an FK pointing back to the same table	Manager/employee hierarchies, comparing sibling rows, finding related records in one table	Forgetting LEFT JOIN at the top of a hierarchy; duplicate pairs without `a.id < b.id`
3+ table join	Progressively wider result set — each JOIN adds column sources	Any real reporting query that spans bridge/junction tables	Fan-out from many-to-many: rows multiply per join leg; inflates SUM/COUNT aggregates
`a.id < b.id` in self-join	Ensures each pair appears once; no self-pairs	Finding books by the same publisher, duplicate-detection	Use `<=` only if you explicitly want self-pairs
Condition in ON vs WHERE	ON condition applied during join (keeps LEFT JOIN soft); WHERE applied after (can harden to INNER)	Filtering on the optional table's columns — must go in ON	Moving optional-table filter to WHERE silently converts LEFT to INNER JOIN

Next: Chapter 4 — Subqueries: scalar, row, and table subqueries; correlated vs non-correlated; IN, EXISTS, ANY, ALL; when a subquery beats a join and when it doesn't.