SQL Joins Explained With Visual Examples
Inner, left, right, full and cross joins each return a different set of rows. Learn the difference and the mistakes that cause duplicates.
By Velloreth
The tables
Two tables for the examples.
users
id | name
1 | Alice
2 | Bob
3 | Carol
orders
id | user_id | total
10 | 1 | 50
11 | 1 | 30
12 | 2 | 20
Note that Carol has no orders, and every order has a user.
Inner join
Returns only the rows that match in both tables.
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;
| name | total |
|---|---|
| Alice | 50 |
| Alice | 30 |
| Bob | 20 |
Carol is absent, because she has no orders. Alice appears twice, because she has two orders.
JOIN is shorthand for INNER JOIN.
Left join
Returns every row from the left table, with nulls where the right table has no match.
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;
| name | total |
|---|---|
| Alice | 50 |
| Alice | 30 |
| Bob | 20 |
| Carol | NULL |
Carol appears with a null, because she has no orders.
This is the join to use when you want every row from one table, with the related data where it exists.
Right join
Returns every row from the right table.
SELECT u.name, o.total
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;
This is the mirror of a left join. In practice, most people write a left join and swap the table order, because it is easier to read.
Full outer join
Returns every row from both tables, with nulls where there is no match.
SELECT u.name, o.total
FROM users u
FULL OUTER JOIN orders o ON o.user_id = u.id;
| name | total |
|---|---|
| Alice | 50 |
| Alice | 30 |
| Bob | 20 |
| Carol | NULL |
If there were an order with a user_id that does not exist in users, it would appear with a null name.
MySQL does not support FULL OUTER JOIN directly; it is emulated with a union of a left and a right join.
Cross join
Returns every combination of rows.
SELECT u.name, o.total
FROM users u
CROSS JOIN orders o;
Three users and three orders is nine rows.
It is useful for generating a grid, such as every date and every product. It is dangerous by accident, because a missing join condition produces a cross join and a huge result.
The duplicate trap
A join on a non-unique column multiplies the rows.
-- If a user has 3 orders and 2 addresses, this returns 6 rows
SELECT u.name, o.total, a.city
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN addresses a ON a.user_id = u.id;
Each order is paired with each address, so the row count is the product.
The fix is to aggregate before the join, or to use a subquery.
SELECT u.name, o.total, a.city
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN (
SELECT user_id, MIN(city) AS city
FROM addresses
GROUP BY user_id
) a ON a.user_id = u.id;
Always check the cardinality of a join key. A join on a unique column does not multiply; a join on a non-unique column does.
ON versus WHERE in a left join
This is a subtle and common mistake.
-- Keeps every user, with nulls for the non-matching orders
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.total > 25;
-- Removes the users with no matching order: this is an inner join
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.total > 25;
A condition in ON filters the right table before the join, so the left rows are kept with nulls.
A condition in WHERE filters after the join, so a null fails the condition and the left row is removed.
The second query returns only Alice, because Bob’s order is 20 and Carol has no order. The first returns every user, with Bob and Carol as null.
Finding rows with no match
A left join with a null check:
SELECT u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;
NOT EXISTS, which is usually clearer and often faster:
SELECT u.name
FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
NOT IN is the one to avoid: if the subquery returns a null, the whole condition is null and the query returns nothing. This is a classic bug.
Self join
A join of a table to itself, with two aliases.
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
This is how a hierarchy is queried. The left join keeps an employee with no manager, such as the CEO.
Join performance
A join is where a query most often becomes slow, and the cause is usually a missing index on the join column. Without one, the database reads the whole table for each row of the other side, which is a nested loop over a large set. Adding an index on the foreign key turns that into a lookup, and the difference is often orders of magnitude. The second common cause is a type mismatch: joining a string column to an integer forces a cast, which prevents the index from being used, and the fix is to make the types match. The third is a filter applied after the join rather than before, which means the database joins far more rows than it needs to. Reading the plan with EXPLAIN ANALYZE shows which of these is happening, and the estimated row count versus the actual row count is the fastest signal that the planner is working from a wrong assumption.
Index the join column. A join without an index on the foreign key is a nested loop that scans the table for each row.
Join on the same type. A type mismatch forces a cast, which prevents the index from being used.
Filter early. A WHERE clause that reduces the rows before the join is faster than one that filters after.
Prefer EXISTS to IN for a large subquery, because the database can stop at the first match.
Check the plan. EXPLAIN ANALYZE shows whether the join uses a nested loop, a hash join, or a merge join, and whether the row estimates are accurate.
Practical guidance
- Use an inner join for the rows that match.
- Use a left join for every row from one table.
- Check the cardinality of the join key.
- Put a filter in
ONto keep the left rows. - Use
NOT EXISTSrather thanNOT IN. - Index the join column.
- Avoid a cross join by accident.
- Read the plan for a slow join.
For the related guides, see database-indexing-explained and database-normalization-explained. For the broader context, sql-vs-nosql-databases and database-migrations-guide.
Quotable summary
An inner join returns only the rows that match in both tables, while a left join returns every row from the left table with nulls where the right table has no match. A join on a non-unique column multiplies the rows, which is the most common cause of an unexpected row count, so always check the cardinality of the join key. A condition in the WHERE clause on a left-joined table turns the join into an inner join, because a null fails the condition and the left row is removed, whereas the same condition in ON filters the right table before the join. Use NOT EXISTS rather than NOT IN to find rows with no match, because a null in the subquery makes NOT IN return nothing.