Combining rows from several tables: inner and outer joins, the FULL OUTER JOIN MySQL doesn't have, cross and self joins, long join chains, anti- and semi-joins, where conditions belong, and how MySQL actually runs a join.
10 topics
Parts marked Advanced are extra depth. Skip them on a first read or a quick revision.
A well-designed relational schema stores each fact once. An order doesn't contain its customer's name; it contains customer_id = 1, and the name lives in one row of customers. That's what keeps data consistent (rename a customer in one place and every order shows the new name), but it means the answer to almost any real question is spread over several tables. A join puts it back together at query time by matching rows on related values.
The mental model is a lookup by key: for each order, look up the customer whose id equals the order's customer_id, and glue the two rows side by side into one wider row. Formally, a join starts from every combination of rows from the two tables (the Cartesian product) and keeps the combinations where the join condition is true. MySQL never actually builds all combinations when it can avoid it, but that definition is what decides the result.
Here are the first four orders on their own. They tell you who ordered only as a number:
SQL
SELECT id, customer_id,statusFROM orders WHERE id <=4;
orders stores only customer_id, a foreign key pointing at customers.id. It doesn't know names or cities.
Orders 1 and 2 both say 1: one customer, two orders. If the name were stored in each order, renaming customer 1 would mean updating every one of their orders, and missing one would leave the data contradicting itself.
To show "order 1 was placed by Rohit in Toronto", we need a row from orders and the matching row from customers side by side. That's exactly what the next topic's INNER JOIN does.
Listing two tables with a comma and no condition pairs every customer with every order: 6 × 8 = 48 combinations. That's the Cartesian product.
Only 8 of those 48 pairs are meaningful, the ones where orders.customer_id = customers.id. A join condition is the filter that picks them out.
This is why a missing or wrong join condition is so dangerous: on real tables, 1 million customers × 10 million orders is 10 trillion rows. CROSS JOIN is the deliberate version.
INNER JOIN (or just JOIN) returns a row for every pair of rows, one from each table, for which the ON condition is true. Rows with no match on the other side disappear from the result, and a row with several matches appears once per match.
Think of a dance where everyone needs a partner: each order looks for its customer; pairs that find each other go onto the floor, and anyone left without a partner sits out. A customer with two orders dances twice, once with each.
SQL
SELECT o.id AS order_id, o.status, c.name AS customer, c.city
FROM orders o
INNERJOIN customers c ON c.id = o.customer_id
ORDERBY o.id;
Result
+----------+-----------+----------+---------+
| order_id | status | customer | city |
+----------+-----------+----------+---------+
| 1 | shipped | Rohit | Toronto |
| 2 | paid | Rohit | Toronto |
| 3 | shipped | Maya | Mumbai |
| 4 | cancelled | Liam | London |
| 5 | shipped | Liam | London |
| 6 | pending | Sofia | NULL |
| 7 | paid | Noah | Toronto |
| 8 | shipped | Maya | Mumbai |
+----------+-----------+----------+---------+
8 rows in set (0.000 sec)
What’s happening
orders o and customers c give each table a short alias; o.customer_id and c.id say exactly which table each column comes from.
For order 1, customer_id = 1, and the condition c.id = o.customer_id is true only for Rohit's row, so the result row is order 1 glued to Rohit's name and city.
Orders 1 and 2 both match Rohit, so his name appears twice, once per order. A join multiplies rows on the "one" side by the number of matches on the "many" side.
Every order has a valid customer (the foreign key guarantees it), so all 8 orders appear. Zara is missing: she has no orders, so there's no pair containing her, and an inner join drops unmatched rows.
Sofia's city is NULL. That's fine: the join condition is on id, which matched. NULLs only stop a match when they're in the join columns themselves.
AdvancedCounting how a join multiplies rows
SQL
SELECT c.name,COUNT(*)AS rows_after_join
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUPBY c.id, c.name
ORDERBY c.id;
Grouping the joined rows by customer shows how many rows each customer turned into: one per order.
Five customers, 2 + 2 + 2 + 1 + 1 = 8 rows, the same 8 as above. Zara contributes nothing and doesn't appear at all.
This multiplication is the source of most wrong totals: join customers to orders to order items and then SUM a customer-level column, and it's counted once per order line. See Joining more than two tables.
AdvancedOld-style comma joins
Before SQL-92, joins were written with commas in FROM and the condition in WHERE. MySQL still accepts it, and you'll see it in old code:
SQL
SELECT c.name, o.id
FROM customers c, orders o
WHERE o.customer_id = c.id AND c.city ='Toronto';
FROM customers c, orders o is a cross join (48 combinations); the WHERE then keeps the matching pairs in Toronto.
The result and the plan are the same as JOIN … ON o.customer_id = c.id WHERE c.city = 'Toronto'.
The explicit JOIN … ON form is preferred because the join condition sits next to the table it joins, so it's hard to forget, and because outer joins can't be written with commas at all. Mixing the two styles also changes precedence: the comma binds more loosely than JOIN, which can produce Unknown column errors in ON clauses.
AdvancedPitfall: MySQL lets you forget ON
AdvancedError 1052: ambiguous column names
As soon as two joined tables share a column name, unqualified names stop working:
SQL
SELECT id FROM customers c JOIN orders o ON o.customer_id = c.id WHERE o.id =1;
Result
ERROR 1052 (23000): Column 'id' in field list is ambiguous
What’s happening
Both tables have an id column, so a bare id could mean c.id (the customer) or o.id (the order). MySQL refuses to guess.
A column that exists in only one of the tables (like name here) can be used unqualified, but that breaks the day someone adds a name column to the other table.
Qualify every column in a join (c.name, o.id). It costs a few characters and makes the query readable on its own.
A LEFT JOIN (LEFT OUTER JOIN; OUTER is optional) keeps every row from the left table, whether or not it has a match. Where there is a match it behaves like an inner join; where there isn't, the left row appears once with NULL in every column from the right table. RIGHT JOIN is the mirror image, keeping every row of the right table.
The mental model is a guest list: the left table is the list, and everyone on it gets a line in the report. Next to each name you write what you found in the right table, or leave the space blank (NULL) if you found nothing. "Left" simply means the table written before the JOIN keyword.
SQL
SELECT c.id, c.name, o.id AS order_id, o.statusFROM customers c
LEFTJOIN orders o ON o.customer_id = c.id
ORDERBY c.id, o.id;
customers is the left table, so all six customers must appear.
For Rohit to Noah the join finds matching orders, and each customer appears once per order, exactly as with an inner join: 8 rows.
For Zara the ON condition matches no order. Instead of dropping her, the left join keeps her row once and fills the order columns, order_id and status, with NULL. That's the 9th row.
Those NULLs are produced by the join; they aren't stored anywhere. That's how you later tell "no matching row" apart: an order's id is never NULL in the table, so o.id IS NULL can only mean "no order".
The result has 9 rows: 8 matched plus 1 unmatched left row. In general a left join returns at least as many rows as the left table has.
?Orders per customer, including zero
SQL
SELECT c.name,COUNT(*)AS count_star,COUNT(o.id)AS count_orders
FROM customers c LEFTJOIN orders o ON o.customer_id = c.id
GROUPBY c.id, c.name ORDERBY c.id;
The left join gives Zara one row, with o.id set to NULL.
COUNT(*) counts rows, and she has one, so it says she has 1 order. That's wrong.
COUNT(o.id) counts non-NULL values of o.id. Her only value is the join-produced NULL, so it's 0. That's right.
Rule: after a left join, count a column from the right table, ideally its primary key. This is one of the most common reporting bugs.
AdvancedRIGHT JOIN is LEFT JOIN with tables swapped
SQL
SELECT c.name, o.id AS order_id
FROM orders o
RIGHTJOIN customers c ON c.id = o.customer_id
WHERE c.id >=5ORDERBY c.id;
Result
+------+----------+
| name | order_id |
+------+----------+
| Noah | 7 |
| Zara | NULL |
+------+----------+
What’s happening
Here customers is written on the right, and RIGHT JOIN keeps every row of the right table, so Zara still appears with a NULL order.
orders o RIGHT JOIN customers c is exactly customers c LEFT JOIN orders o. MySQL even rewrites right joins into left joins internally.
Most teams write only LEFT JOINs, putting the "keep everything" table first, because a chain of joins that reads left to right is easier to follow. Use RIGHT JOIN when it genuinely reads better, which is rare.
AdvancedDepartments with no staff
SQL
SELECT d.name AS department, e.name AS employee
FROM departments d
LEFTJOIN employees e ON e.department_id = d.id
ORDERBY d.id, e.id;
Result
+-------------+----------+
| department | employee |
+-------------+----------+
| Engineering | Asha |
| Engineering | Ben |
| Engineering | Chloe |
| Engineering | Dev |
| Sales | Elena |
| Sales | Farid |
| Sales | Grace |
| Support | Hiro |
| Support | Isla |
| Research | NULL |
+-------------+----------+
What’s happening
With departments on the left, every department appears. Research has no employees and gets one row with NULL for the employee.
Jonas is missing: his department_id is NULL, which matches no department, and employees is the right-hand table, so unmatched employees are dropped.
Keeping both Research and Jonas needs a full outer join, which is the next topic.
AdvancedPitfall: WHERE on the right table undoes it
A FULL OUTER JOIN keeps every row from both tables: matched rows are paired as usual, unmatched rows from either side appear with NULLs for the other side. It answers questions like "reconcile these two lists": departments with no staff and staff with no department, in one result. It's in the SQL standard and in PostgreSQL, SQL Server and Oracle, but MySQL 9.7 still doesn't implement it.
The mental model is two overlapping circles (the Venn picture people draw for joins): an inner join is the overlap, a left join is the left circle, and a full join is both circles. Since MySQL can do each circle, the standard trick is to compute the left circle and the right circle and glue them together with UNION.
SQL
SELECT d.name AS department, e.name AS employee
FROM departments d
LEFTJOIN employees e ON e.department_id = d.id
UNIONSELECT d.name, e.name
FROM departments d
RIGHTJOIN employees e ON e.department_id = d.id
ORDERBY department ISNULL, department, employee ISNULL, employee;
Result
+-------------+----------+
| department | employee |
+-------------+----------+
| Engineering | Asha |
| Engineering | Ben |
| Engineering | Chloe |
| Engineering | Dev |
| Research | NULL |
| Sales | Elena |
| Sales | Farid |
| Sales | Grace |
| Support | Hiro |
| Support | Isla |
| NULL | Jonas |
+-------------+----------+
11 rows in set (0.000 sec)
What’s happening
The first SELECT is the left join from the previous topic: 9 matched rows plus Research with a NULL employee (10 rows).
The second SELECT is a right join: every employee, so the same 9 matched rows plus Jonas with a NULL department (10 rows).
UNION stacks the two results and removes duplicate rows. The 9 matched rows appeared in both halves and are kept once, leaving 9 + Research + Jonas = 11 rows.
Column names come from the first SELECT, which is why ORDER BY can use department and employee. An ORDER BY after the last SELECT sorts the whole union.
The sort uses the NULLs-last trick (department IS NULL first) so Jonas's row ends up at the bottom.
AdvancedWhat PostgreSQL 18 does with a real FULL JOIN
The same two tables loaded into PostgreSQL 18 (schema ch01), with a genuine full join:
SQL
SELECT d.name AS department, e.name AS employee
FROM departments d
FULLOUTERJOIN employees e ON e.department_id = d.id
ORDERBY d.name NULLS LAST, e.name NULLS LAST;
Result
department | employee
-------------+----------
Engineering | Asha
Engineering | Ben
Engineering | Chloe
Engineering | Dev
Research |
Sales | Elena
Sales | Farid
Sales | Grace
Support | Hiro
Support | Isla
| Jonas
(11 rows)
What’s happening
Same 11 rows. psql prints NULL as an empty cell rather than the word NULL.
PostgreSQL also has NULLS LAST, which MySQL lacks.
In MySQL 9.7 the same FULL OUTER JOIN query is just a syntax error: ERROR 1064 (42000): … near 'FULL OUTER JOIN employees e ON e.department_id = d.id'.
?Why the UNION version can lose rows
Now show each department's salaries, with the same emulation. How many rows should there be, and how many does UNION return?
SQL
SELECT d.name AS department, e.salary
FROM departments d LEFTJOIN employees e ON e.department_id = d.id
UNIONSELECT d.name, e.salary
FROM departments d RIGHTJOIN employees e ON e.department_id = d.id
ORDERBY department ISNULL, department, salary DESC;
Show answer
A real full join has 11 rows (10 employees plus Research). The UNION version returns 10:
Ben and Chloe are both in Engineering on 142000.00, so they produce two identical rows (Engineering, 142000.00). Without names, nothing tells them apart.
UNION removes duplicates across the whole result, not just the overlap between the halves, so one of them disappears.
The safe emulation keeps the left join whole and adds only the unmatched right rows, with UNION ALL (no de-duplication):
… LEFT JOIN … UNION ALL SELECT d.name, e.salary FROM departments d RIGHT JOIN employees e ON e.department_id = d.id WHERE d.id IS NULL. The WHERE d.id IS NULL keeps only employees with no department (Jonas). I ran it: 11 rows, both 142000.00 rows present.
UNION ALL is also faster, because it skips the de-duplication step.
A CROSS JOIN returns every combination of rows from the two tables, with no join condition: 4 departments × 3 months = 12 rows. It's the Cartesian product that every other join is defined as a filter of. By accident it's a disaster; on purpose it's the tool for generating grids: every product in every size, every store for every day, every customer for every month.
The mental model is a multiplication table: rows of one table down the side, rows of the other across the top, and one cell for each pair. Its most useful job is building the full grid first, so that a LEFT JOIN to the real data can show the empty cells as zeros instead of leaving them out.
SQL
SELECT d.name AS department, m.monthFROM departments d
CROSSJOIN(SELECT'2025-02'ASmonthUNIONALLSELECT'2025-03'UNIONALLSELECT'2025-04')AS m
ORDERBY d.id, m.month;
Result
+-------------+---------+
| department | month |
+-------------+---------+
| Engineering | 2025-02 |
| Engineering | 2025-03 |
| Engineering | 2025-04 |
| Sales | 2025-02 |
| Sales | 2025-03 |
| Sales | 2025-04 |
| Support | 2025-02 |
| Support | 2025-03 |
| Support | 2025-04 |
| Research | 2025-02 |
| Research | 2025-03 |
| Research | 2025-04 |
+-------------+---------+
12 rows in set (0.000 sec)
What’s happening
The derived table m is three one-column rows built with UNION ALL: a tiny inline table of months.
CROSS JOIN pairs each of the 4 departments with each of the 3 months: 4 × 3 = 12 rows. There's no ON, because every pair is wanted.
Even Research, which has no staff, gets three rows. A cross join doesn't care about matches; it only multiplies.
ORDER BY d.id, m.month lays the grid out department by department.
AdvancedA zero-filled report: CROSS JOIN, then LEFT JOIN
"Orders per customer per month" with a plain GROUP BY would leave out months in which a customer ordered nothing. Build the grid first:
SQL
SELECT c.name, m.month,COUNT(o.id)AS orders
FROM customers c
CROSSJOIN(SELECT'2025-02'ASmonthUNIONALLSELECT'2025-03'UNIONALLSELECT'2025-04')AS m
LEFTJOIN orders o
ON o.customer_id = c.id
AND o.ordered_at >= CONCAT(m.month,'-01')AND o.ordered_at < CONCAT(m.month,'-01')+INTERVAL1MONTHWHERE c.id <=3GROUPBY c.id, c.name, m.monthORDERBY c.id, m.month;
The cross join makes every (customer, month) cell: 3 customers × 3 months = 9 rows.
The left join attaches each customer's orders that fall in that cell's month. The month test is a half-open range built from the month string: '2025-02-01' up to but not including '2025-02-01' + INTERVAL 1 MONTH.
Cells with no orders keep one row with NULL order columns, and COUNT(o.id) turns them into 0: Rohit in April, Maya in March, Liam in April.
Liam's February order is the cancelled one (order 4). Add AND o.status <> 'cancelled' to the ON clause to exclude it while still keeping the cell.
On a real system the month list would come from a calendar table or a recursive CTE (see Recursive CTEs).
AdvancedInline rows with VALUES ROW()
Since MySQL 8.0.19 a VALUES statement can build the small side of the grid:
SQL
SELECT c.name AS category, s.size
FROM categories c
CROSSJOIN(VALUESROW('S'),ROW('M'),ROW('L'))AS s (size)WHERE c.parent_id ISNULLORDERBY c.id, s.size DESC;
Result
+-------------+------+
| category | size |
+-------------+------+
| Electronics | S |
| Electronics | M |
| Electronics | L |
| Books | S |
| Books | M |
| Books | L |
+-------------+------+
What’s happening
VALUES ROW('S'), ROW('M'), ROW('L') is a three-row table; AS s (size) names it and its column. MySQL requires the ROW keyword; PostgreSQL writes VALUES ('S'), ('M'), ('L').
The two top-level categories (Electronics and Books) times three sizes gives 6 rows.
ORDER BY s.size DESC happens to give S, M, L because that's reverse alphabetical order. For a real size order, sort by a number stored next to each size.
AdvancedPitfall: accidental cross joins multiply fast
A self join joins a table to itself. There's no special syntax: you list the same table twice under two different aliases, and SQL treats them as two separate copies. You need it whenever a table refers to its own rows: an employee's manager_id is another employee's id, and a category's parent_id is another category's id.
The mental model is photocopying the sheet. Put the original on the left labelled e ("the employee") and the copy on the right labelled m ("the manager"), then match each row on the left with the row on the right whose id equals its manager_id. The aliases are what tell the two roles apart.
SQL
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFTJOIN employees m ON m.id = e.manager_id
ORDERBY e.id;
Result
+----------+---------+
| employee | manager |
+----------+---------+
| Asha | NULL |
| Ben | Asha |
| Chloe | Asha |
| Dev | Ben |
| Elena | Asha |
| Farid | Elena |
| Grace | Elena |
| Hiro | Asha |
| Isla | Hiro |
| Jonas | Asha |
+----------+---------+
10 rows in set (0.000 sec)
What’s happening
employees e is the employee's role and employees m the manager's. Same table, two names.
For Dev, e.manager_id = 2, so the condition m.id = e.manager_id matches Ben's row in the m copy, and Dev's row gets manager = Ben.
Asha's manager_id is NULL, which matches nobody. Because it's a LEFT join she stays, with manager = NULL. An inner join would drop the head of the company.
Without aliases MySQL can't tell the copies apart: FROM employees JOIN employees fails with ERROR 1066 (42000): Not unique table/alias: 'employees'.
AdvancedComparing a row with its manager's row
The classic interview question: "find employees who earn more than their manager".
SQL
SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary;SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary *0.75ORDERBY e.id;
After the self join, each row holds an employee and their manager side by side, so comparing them is an ordinary WHERE on two columns of the same row.
In the sample company nobody out-earns their manager, so the first query correctly returns an empty set. An inner join is right here: Asha has no manager to compare with.
Loosening it to "more than 75% of the manager's salary" shows the comparison working: Ben's 142000 is 77% of Asha's 185000; Farid (95000 vs Elena's 130000, 73%) just misses.
The same shape answers "hired before their manager" (empty here too) or "in a different department from their manager".
AdvancedPairs within one table without mirror pairs
"Which employees in the same department earn exactly the same?"
SQL
SELECT a.name ASfirst, b.name ASsecond, a.salary
FROM employees a
JOIN employees b
ON a.department_id = b.department_id AND a.salary = b.salary AND a.id <> b.id;SELECT a.name ASfirst, b.name ASsecond, a.salary
FROM employees a
JOIN employees b
ON a.department_id = b.department_id AND a.salary = b.salary AND a.id < b.id;
Result
+-------+--------+-----------+
| first | second | salary |
+-------+--------+-----------+
| Ben | Chloe | 142000.00 |
| Chloe | Ben | 142000.00 |
+-------+--------+-----------+
+-------+--------+-----------+
| first | second | salary |
+-------+--------+-----------+
| Ben | Chloe | 142000.00 |
+-------+--------+-----------+
What’s happening
Without any id condition every employee would pair with themselves (same department, same salary). a.id <> b.id removes those.
It still returns the pair twice, (Ben, Chloe) and (Chloe, Ben), because each is a valid combination of the two copies.
a.id < b.id keeps only the version where the first id is smaller, so each unordered pair appears once. Use it for every "find duplicates / pairs" self join.
AdvancedWalking up more than one level
Each extra level is one more copy of the table:
SQL
SELECT e.name AS employee, m.name AS manager, gm.name AS managers_manager
FROM employees e
JOIN employees m ON m.id = e.manager_id
JOIN employees gm ON gm.id = m.manager_id
ORDERBY e.id;SELECT child.name AS category, parent.name AS parent
FROM categories child
LEFTJOIN categories parent ON parent.id = child.parent_id
ORDERBY child.id;
Three aliases, e, m and gm, are three roles for the same table. Only employees whose manager also has a manager survive the inner joins: the four people two levels below Asha.
The category query is the same idea one level deep, with a left join so the top-level categories (no parent) stay.
A fixed number of self joins only reaches a fixed depth. For "everyone under Asha, however deep", use a recursive CTE (Recursive CTEs).
AdvancedCounting direct reports
SQL
SELECT m.name AS manager,COUNT(e.id)AS direct_reports
FROM employees m
LEFTJOIN employees e ON e.manager_id = m.id
GROUPBY m.id, m.name
HAVING direct_reports >0ORDERBY direct_reports DESC, m.name;
Real questions often cross four or five tables: "for each shipped order, the customer, every product and its category". You just keep adding JOIN … ON … clauses. Logically, each join takes the result so far and joins one more table to it, left to right, so every new table needs a condition linking it to a table that's already in the query.
The mental model is following foreign keys like a path on the ER diagram: start at orders, step to customers (via customer_id), to order_items (via order_id), to products (via product_id), to categories (via category_id). Each step is one join. Two things go wrong on long paths: rows multiply at every one-to-many step, and one inner join after a left join can silently undo it.
SQL
SELECT o.id AS order_id, c.name AS customer, p.name AS product,
cat.name AS category, oi.quantity,
oi.quantity * oi.unit_price AS line_total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status='shipped'ORDERBY o.id, p.name;
orders o JOIN customers c attaches one customer per order (many-to-one, no multiplication).
JOIN order_items oi ON oi.order_id = o.id is one-to-many: order 1 becomes two rows, one per line. From here on the result has one row per order line.
products and categories are many-to-one again, adding the product name and its category to each line without adding rows.
WHERE o.status = 'shipped' keeps orders 1, 3, 5 and 8 and their 7 lines. The optimizer is free to apply it first and join fewer rows; the result is the same.
The aliases (c, oi, p, cat) keep a five-table query readable. cat instead of c avoids a clash with the customers alias.
AdvancedPitfall: an inner join after a left join
AdvancedCustomer lifetime value without double counting
Here is "every customer's lifetime value" written so nobody is dropped and nothing is counted twice:
SQL
SELECT c.name,COUNT(DISTINCT o.id)AS orders,COALESCE(SUM(oi.quantity * oi.unit_price),0)AS lifetime_value
FROM customers c
LEFTJOIN orders o ON o.customer_id = c.id AND o.status<>'cancelled'LEFTJOIN order_items oi ON oi.order_id = o.id
GROUPBY c.id, c.name
ORDERBY lifetime_value DESC;
Two questions come up constantly and aren't really "joins" in the sense of combining columns. A semi-join asks "which rows of A have at least one match in B?" (customers who have ordered), returning each A row once. An anti-join asks "which rows of A have no match in B?" (customers who never ordered). SQL has no SEMI JOIN keyword; you write them with EXISTS / IN and NOT EXISTS / NOT IN / LEFT JOIN … IS NULL, and MySQL's optimizer recognises the pattern.
The mental model is a yes/no question per row: for each customer, peek into orders and ask "is there any order for you?". You never bring the orders back; you only need the answer, and you can stop looking at the first match. That's the difference from an inner join, which brings back every match and therefore duplicates.
SQL
SELECT c.id, c.name
FROM customers c
WHERENOTEXISTS(SELECT1FROM orders o WHERE o.customer_id = c.id
);
Result
+----+------+
| id | name |
+----+------+
| 6 | Zara |
+----+------+
1 row in set (0.000 sec)
What’s happening
For each customer, the subquery looks for orders with customer_id = c.id. It's correlated: it refers to c from the outer query.
SELECT 1 is a convention: EXISTS only cares whether at least one row exists, never about the columns, so what you select doesn't matter.
For Rohit the subquery finds orders, EXISTS is true and NOT EXISTS false, so he's filtered out. The same for Maya, Liam, Sofia and Noah.
For Zara it finds nothing, NOT EXISTS is true, and she's returned. Each customer appears at most once, however many orders they have.
NOT EXISTS is the safest way to write an anti-join: it is never confused by NULLs (see the gotcha below), and MySQL 8.0.17+ turns it into a real antijoin plan.
AdvancedThree ways to write the same anti-join
SQL
SELECT c.id, c.name
FROM customers c
LEFTJOIN orders o ON o.customer_id = c.id
WHERE o.id ISNULL;SELECT id, name FROM customers
WHERE id NOTIN(SELECT customer_id FROM orders);SELECT p.id, p.name FROM products p
WHERENOTEXISTS(SELECT1FROM order_items oi WHERE oi.product_id = p.id);
Result
+----+------+
| id | name |
+----+------+
| 6 | Zara |
+----+------+
+----+------+
| id | name |
+----+------+
| 6 | Zara |
+----+------+
+----+-----------+
| id | name |
+----+-----------+
| 4 | iPhone 17 |
+----+-----------+
What’s happening
LEFT JOIN … IS NULL: join everything, then keep the rows where the right side came back as NULL. Test a column that can't be NULL in the table itself (the primary key o.id), so NULL can only mean "no match".
NOT IN works here because orders.customer_id is NOT NULL. If the subquery can return a NULL, it silently returns nothing (next box).
The same pattern finds the product nobody has ordered: the iPhone 17.
All three give the same answer, and on MySQL 8.0.17+ the first and the NOT EXISTS version get the same antijoin plan (see the EXPLAIN box below).
AdvancedPitfall: NOT IN with a nullable column
AdvancedSemi-joins: EXISTS vs IN vs JOIN
"Which customers have a shipped order?"
SQL
SELECT c.name FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status='shipped'ORDERBY c.id;SELECT c.name FROM customers c
WHEREEXISTS(SELECT1FROM orders o
WHERE o.customer_id = c.id AND o.status='shipped')ORDERBY c.id;SELECT c.name FROM customers c
WHERE c.id IN(SELECT customer_id FROM orders WHEREstatus='shipped')ORDERBY c.id;
The join returns Maya twice, because she has two shipped orders (3 and 8). A join answers "give me the pairs", not "yes or no".
Adding DISTINCT would hide that, at the cost of de-duplicating the whole result. EXISTS and IN express the actual question and return each customer once.
EXISTS with a correlated subquery and IN with an uncorrelated one are equivalent here, and MySQL 8.0.16+ plans both as a semi-join (which one is "faster" is mostly a 5.x-era concern; see IN vs EXISTS).
Use a join when you need columns from the other table; use EXISTS when you only need to know a match exists.
AdvancedAntijoin and semijoin plans in EXPLAIN
SQL
EXPLAIN FORMAT=TREE
SELECT c.id, c.name FROM customers c
WHERENOTEXISTS(SELECT1FROM orders o WHERE o.customer_id = c.id);EXPLAIN FORMAT=TREE
SELECT c.id, c.name FROM customers c
LEFTJOIN orders o ON o.customer_id = c.id WHERE o.id ISNULL;EXPLAIN FORMAT=TREE
SELECT c.name FROM customers c
WHERE c.id IN(SELECT customer_id FROM orders WHEREstatus='shipped');
Result
EXPLAIN: -> Nested loop antijoin (cost=3.31 rows=9.6)
-> Table scan on c (cost=0.85 rows=6)
-> Covering index lookup on o using customer_id (customer_id = c.id) (cost=0.443 rows=1.6)
EXPLAIN: -> Filter: (o.id is null) (cost=10 rows=9.6)
-> Nested loop antijoin (cost=10 rows=9.6)
-> Table scan on c (cost=0.85 rows=6)
-> Covering index lookup on o using customer_id (customer_id = c.id) (cost=0.253 rows=1.6)
EXPLAIN: -> Hash semijoin (FirstMatch) (orders.customer_id = c.id) (cost=1.61 rows=0.75)
-> Table scan on c (cost=0.85 rows=6)
-> Hash
-> Filter: (orders.`status` = 'shipped') (cost=0.159 rows=1)
-> Table scan on orders (cost=0.159 rows=8)
What’s happening
Read the tree from the innermost, most-indented lines outwards. For NOT EXISTS, MySQL scans customers and, for each one, looks up orders through the customer_id index (the one InnoDB created for the foreign key).
Nested loop antijoin means: emit the customer only if the lookup finds nothing, and stop at the first match otherwise. The subquery has been turned into a join; nothing is re-run per row the slow way.
The LEFT JOIN … IS NULL version is recognised as the same antijoin, with a leftover Filter: (o.id is null) on top.
The IN version became a Hash semijoin (FirstMatch): build a hash table of the customer ids that have shipped orders, then keep each customer whose id is in it, once.
In MySQL 5.7, NOT EXISTS was always executed as a dependent subquery, once per outer row. The antijoin transformation arrived in 8.0.17.
There are three places a condition can go, and for inner joins they're mostly interchangeable. For outer joins they mean different things. ON decides which rows match while the join is being built; unmatched left rows still survive, padded with NULLs. WHERE filters the finished result, after the NULL padding. USING (col) is shorthand for ON a.col = b.col when both tables name the column the same, and it merges the two columns into one.
The mental model for a left join: ON is the matchmaker's rules ("only introduce customers to their shipped orders"); everyone on the guest list still gets a seat, matched or not. WHERE is the bouncer at the end ("only people holding a shipped order may stay"), and people without a match are holding nothing, so they're thrown out.
SQL
SELECT d.name AS department, e.name AS employee, e.salary
FROM departments d
LEFTJOIN employees e ON e.department_id = d.id AND e.salary >120000ORDERBY d.id, e.id;
Result
+-------------+----------+-----------+
| department | employee | salary |
+-------------+----------+-----------+
| Engineering | Asha | 185000.00 |
| Engineering | Ben | 142000.00 |
| Engineering | Chloe | 142000.00 |
| Sales | Elena | 130000.00 |
| Support | NULL | NULL |
| Research | NULL | NULL |
+-------------+----------+-----------+
6 rows in set (0.000 sec)
What’s happening
The question is "every department, with its employees earning over 120000". departments is on the left, so every department must appear.
The ON clause has two parts: the employee must be in the department and earn more than 120000. Both are matching rules.
Engineering matches Asha, Ben and Chloe (Dev's 118000 fails the salary rule, so he simply isn't matched). Sales matches only Elena.
Support has employees, but none who pass the salary rule, so it has no match and is kept with NULLs, exactly like Research, which has no employees at all.
Six rows: every department, each with the employees that satisfied the ON conditions, or a NULL placeholder.
AdvancedThe same condition moved to WHERE
SQL
SELECT d.name AS department, e.name AS employee, e.salary
FROM departments d
LEFTJOIN employees e ON e.department_id = d.id
WHERE e.salary >120000ORDERBY d.id, e.id;
Now the join only matches on department: 9 employee rows plus Research with NULLs, 10 rows in all.
WHERE e.salary > 120000 then runs on those 10 rows. Dev, Farid, Grace, Hiro and Isla fail it.
Research's padded row has e.salary = NULL, and NULL > 120000 is unknown, so it's removed too. Support disappears because all its rows were removed.
Four rows, and the "every department" promise of the left join is gone. MySQL's optimizer notices that the WHERE rejects NULL-padded rows and converts the join to an inner join.
Rule of thumb: conditions on the optional (right) table of a left join go in ON; conditions on the preserved (left) table go in WHERE. A condition like WHERE d.name <> 'Research' is fine in WHERE.
AdvancedInner joins: ON and WHERE are equivalent
SQL
SELECT o.id, o.status, c.name FROM orders o
JOIN customers c ON c.id = o.customer_id AND o.status='paid';SELECT o.id, o.status, c.name FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status='paid';
Result
+----+--------+-------+
| id | status | name |
+----+--------+-------+
| 2 | paid | Rohit |
| 7 | paid | Noah |
+----+--------+-------+
+----+--------+-------+
| id | status | name |
+----+--------+-------+
| 2 | paid | Rohit |
| 7 | paid | Noah |
+----+--------+-------+
What’s happening
An inner join keeps only matched rows, so "doesn't match" and "filtered out afterwards" both mean "not in the result". The two queries are equivalent and MySQL produces the same plan.
Style still matters: put the conditions that relate the tables in ON and the conditions that filter the result in WHERE. Then turning the join into a left join later doesn't silently change the meaning.
AdvancedUSING: shorthand for same-named columns
USING only works when both sides have a column with the same name. In the sample schema the foreign keys are order_id but the primary keys are id, so here the orders side is renamed in a derived table:
SQL
SELECT oi.order_id, oi.product_id, o.statusFROM order_items oi
JOIN(SELECT id AS order_id,statusFROM orders)AS o USING(order_id)WHERE oi.product_id =7;SELECT*FROM order_items oi
JOIN(SELECT id AS order_id,statusFROM orders)AS o USING(order_id)WHERE oi.product_id =7;SELECT*FROM order_items oi
JOIN(SELECT id AS order_id,statusFROM orders)AS o ON o.order_id = oi.order_id
WHERE oi.product_id =7;
USING (order_id) means ON oi.order_id = o.order_id. The USB-C charger (product 7) appears in orders 1, 5 and 8.
With SELECT *, USINGmerges the two order_id columns into one, listed first. With ON, you get both columns, order_id twice.
For outer joins the merged column is COALESCE(left.col, right.col), so it's never NULL just because one side was missing.
USING reads nicely in schemas that name keys consistently (customer_id in both tables). Many teams prefer ON anyway because it always works and states the condition explicitly.
AdvancedPitfall: NATURAL JOIN guesses the columns
?ON or WHERE?
You want every customer and, if they have one, their paid order. Which query is right, and what does the wrong one return?
SQL
-- ASELECT c.name, o.id FROM customers c
LEFTJOIN orders o ON o.customer_id = c.id AND o.status='paid';-- BSELECT c.name, o.id FROM customers c
LEFTJOIN orders o ON o.customer_id = c.id
WHERE o.status='paid';
Show answer
A is right: all six customers, with order ids only for Rohit (2) and Noah (7). B returns only Rohit and Noah.
Result
+-------+------+
| name | id |
+-------+------+
| Rohit | 2 |
| Maya | NULL |
| Liam | NULL |
| Sofia | NULL |
| Noah | 7 |
| Zara | NULL |
+-------+------+
6 rows in set (0.001 sec)
+-------+------+
| name | id |
+-------+------+
| Rohit | 2 |
| Noah | 7 |
+-------+------+
2 rows in set (0.000 sec)
What’s happening
In A, status = 'paid' is a matching rule. Each customer is matched only with paid orders; everyone else keeps one row with o.id = NULL.
In B the join matches all orders first, then WHERE keeps only rows whose status is 'paid'. Customers whose orders are all unpaid lose every row, and Zara's NULL-padded row fails NULL = 'paid'.
The first result above is A, the second B. It's the same pattern as the shipped-orders example in LEFT and RIGHT OUTER JOIN. The general rule: filters on the right-hand table of a LEFT JOIN go in ON.
SQL says what a join returns; the optimizer decides how. MySQL joins tables one at a time in a chosen order, and for each step picks an access method. The classic method is the nested-loop join: for each row of the outer (driving) table, look up the matching rows in the inner table, ideally through an index, so each lookup is a quick B+tree search. When there's no usable index for an equality join, MySQL 8.0.18+ builds a hash join instead: load the smaller input into an in-memory hash table keyed on the join column, then stream the other input past it.
The mental model is two ways to match guests to their tables at a wedding. Nested loop with an index: take each guest in turn and look their name up in the alphabetical seating chart (fast because the chart is sorted). Hash join: with no sorted chart, first write every table's guest names into a quick-lookup notebook (build), then check each arriving guest against it (probe). Without either, you'd compare every guest with every seat, which is what nested loops without an index do.
SQL
EXPLAINANALYZESELECT c.name, o.id, o.statusFROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.city ='Toronto';
Result
EXPLAIN: -> Nested loop inner join (cost=1.41 rows=1.6) (actual time=0.0394..0.0434 rows=3 loops=1)
-> Filter: (c.city = 'Toronto') (cost=0.85 rows=1) (actual time=0.0172..0.0192 rows=2 loops=1)
-> Table scan on c (cost=0.85 rows=6) (actual time=0.0143..0.0153 rows=6 loops=1)
-> Index lookup on o using customer_id (customer_id = c.id) (cost=0.56 rows=1.6) (actual time=0.00675..0.00729 rows=1.5 loops=2)
What’s happening
The optimizer chose customers as the outer table: it reads all 6 rows (Table scan on c, rows=6) and keeps the Toronto ones (Filter, actual rows=2: Rohit and Noah).
For each of those 2 customers it does an Index lookup on o using customer_id. loops=2 confirms the inner side ran twice, finding rows=1.5 on average (Rohit's 2 orders and Noah's 1).
Nested loop inner join glues each customer to each order found: 3 rows in total, in about 0.04 ms.
cost and rows before the brackets are the optimizer's estimates; actual time (first row..last row, in milliseconds), rows and loops inside the brackets are measurements, because EXPLAIN ANALYZE really runs the query. Big gaps between estimated and actual rows are where bad plans come from.
The customer_id index exists because InnoDB creates one for every foreign key. Without it, each of those loops would have to scan orders, and MySQL would switch to a hash join instead.
AdvancedHash joins when there's no index
"Which order lines were sold at the product's current list price?" joins on price columns, which have no index:
SQL
EXPLAIN FORMAT=TREE
SELECT p.name, oi.order_id
FROM products p
JOIN order_items oi ON oi.unit_price = p.price;EXPLAIN FORMAT=TRADITIONAL
SELECT p.name, oi.order_id
FROM products p
JOIN order_items oi ON oi.unit_price = p.price;
Result
EXPLAIN: -> Inner hash join (oi.unit_price = p.price) (cost=11.7 rows=10.4)
-> Table scan on oi (cost=0.0478 rows=13)
-> Hash
-> Table scan on p (cost=1.05 rows=8)
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | p | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | NULL |
| 1 | SIMPLE | oi | NULL | ALL | NULL | NULL | NULL | NULL | 13 | 10.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
What’s happening
In the tree, the child under Hash is the build input: MySQL scans the 8 products and puts them into an in-memory hash table keyed on price.
The other child is the probe input: it scans the 13 order lines and, for each, looks up its unit_price in the hash table. Each table is read once, instead of 8 × 13 comparisons.
The traditional format shows the same plan as a table: type = ALL (full scan) for both tables, key = NULL (no index), and Using join buffer (hash join) in Extra. filtered = 10.00 is the optimizer's guess that 10% of combinations match.
Run for real, it returns all 13 lines: every order in the sample data was charged the current list price.
Hash joins arrived in MySQL 8.0.18 and replaced the older Block Nested-Loop (BNL) join entirely in 8.0.20. On 5.7 the same plan would say Using join buffer (Block Nested Loop).
AdvancedIndex lookups: ref vs eq_ref
SQL
EXPLAIN FORMAT=TREE
SELECT o.id, p.name
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.id =3;EXPLAIN FORMAT=TRADITIONAL
SELECT c.name, o.id, o.statusFROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.city ='Toronto';
Result
EXPLAIN: -> Nested loop inner join (cost=1.15 rows=2)
-> Covering index lookup on oi using PRIMARY (order_id = 3) (cost=0.45 rows=2)
-> Single-row index lookup on p using PRIMARY (id = oi.product_id) (cost=0.3 rows=1)
+----+-------------+-------+------------+------+---------------+-------------+---------+-----------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-----------+------+----------+-------------+
| 1 | SIMPLE | c | NULL | ALL | PRIMARY | NULL | NULL | NULL | 6 | 16.67 | Using where |
| 1 | SIMPLE | o | NULL | ref | customer_id | customer_id | 4 | ch01.c.id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------------+---------+-----------+------+----------+-------------+
What’s happening
In the first plan orders has disappeared. Because o.id = 3 and oi.order_id = o.id, the optimizer knows oi.order_id = 3 and needs nothing else from orders (only o.id is selected, and it's known), so it removed the table from the plan.
Covering index lookup on oi using PRIMARY (order_id = 3) reads order 3's lines straight from the primary key (order_id, product_id); "covering" means the index alone holds every column needed.
Single-row index lookup on p using PRIMARY is a primary-key lookup that returns at most one row. In the traditional format that access type is called eq_ref, the best possible join access.
In the second plan the inner table o uses ref: a non-unique index lookup that can return several rows per key (a customer can have many orders). ref = ch01.c.id shows the value it looks up comes from the outer table.
The outer table c is type = ALL, a full table scan, which is fine for 6 rows. On a big table you'd want an index on city so the driving table is read by range instead.
AdvancedJoin order, hints and the hypergraph optimizer
MySQL picks the join order by estimated cost, not the order you wrote. You can override it, and 9.7 ships an alternative optimizer:
SQL
EXPLAIN FORMAT=TREE
SELECT STRAIGHT_JOIN c.name, o.id
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE c.city ='Toronto';EXPLAIN FORMAT=TREE
SELECT/*+ NO_BNL(oi) */ p.name, oi.order_id
FROM products p JOIN order_items oi ON oi.unit_price = p.price;SET optimizer_switch ='hypergraph_optimizer=on';EXPLAIN FORMAT=TREE
SELECT c.name, o.id, o.statusFROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.city ='Toronto';
Result
EXPLAIN: -> Inner hash join (c.id = o.customer_id) (cost=2.6 rows=1.33)
-> Filter: (c.city = 'Toronto') (cost=0.106 rows=1)
-> Table scan on c (cost=0.106 rows=6)
-> Hash
-> Covering index scan on o using customer_id (cost=1.05 rows=8)
EXPLAIN: -> Nested loop inner join (cost=13.4 rows=10.4)
-> Table scan on p (cost=1.05 rows=8)
-> Filter: (oi.unit_price = p.price) (cost=0.266 rows=1.3)
-> Table scan on oi (cost=0.266 rows=13)
EXPLAIN: -> Inner hash join (o.customer_id = c.id) (cost=18.5..18.5 rows=0.8)
-> Index scan on o using PRIMARY (cost=0.406..3.24 rows=8)
-> Hash
-> Filter: (c.city = 'Toronto') (cost=13.8..13.8 rows=0.6)
-> Index scan on c using PRIMARY (cost=2.25..13.5 rows=6)
What’s happening
STRAIGHT_JOIN forces the tables to be joined in the order written: orders first. The Toronto filter on customers can no longer shrink the driving table, so every order takes part: MySQL hashes all 8 orders (the Hash child is the first table, the build input) and probes with the Toronto customers. The result is the same; the plan does more work than the optimizer's own choice (cost 2.6 against 1.41).
The NO_BNL hint switches hash joins off for that table. (The NO_HASH_JOIN hint from 8.0.18 is still accepted but has no effect in current versions; I tried it on 9.7 and the plan stayed a hash join.) The result is a nested loop that scans all of order_items once per product: 8 × 13 row reads. That's the plan hash joins exist to avoid.
The hypergraph optimizer is a newer optimizer that considers more join shapes (it can, for instance, put the filtered customers on the build side). It's switched on per session with optimizer_switch, and on this 9.7.2 Community server it was available. Its costs (18.5..18.5) are on a different scale, so don't compare its numbers with the default optimizer's.
Hints and STRAIGHT_JOIN are last resorts: they freeze a plan that may be wrong once the data changes. Fix statistics (ANALYZE TABLE) and indexes first.