SQL joins cheat sheet with examples

Joins combine rows from two tables, and they are the part of SQL that people find hardest to picture. This cheat sheet uses one tiny dataset so that you can follow every result by eye. All outputs below were produced by running the queries on MySQL 8.4, so they are exactly what you will see.

The sample data

Two tables: customers, and orders that point to customers with customer_id.

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(20), city VARCHAR(20));
CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT NULL, total DECIMAL(8,2), status VARCHAR(10));

INSERT INTO customers VALUES (1,'Asha','Pune'),(2,'Ben','Delhi'),(3,'Chen','Pune'),(4,'Dev','Goa');
INSERT INTO orders VALUES (101,1,250.00,'paid'),(102,1,120.50,'paid'),(103,2,80.00,'pending'),
                          (104,NULL,60.00,'paid'),(105,3,500.00,'cancelled');

customers.id	name	city
1	Asha	Pune
2	Ben	Delhi
3	Chen	Pune
4	Dev	Goa

orders.id	customer_id	total	status
101	1	250.00	paid
102	1	120.50	paid
103	2	80.00	pending
104	NULL	60.00	paid
105	3	500.00	cancelled

Two facts to hold on to: Dev has no orders, and order 104 has no customer (its customer_id is NULL).

The one-line summary

Join	Keeps
INNER JOIN	only rows that match in both tables
LEFT JOIN	all rows of the left table, plus matches (or NULLs) from the right
RIGHT JOIN	all rows of the right table, plus matches (or NULLs) from the left
FULL OUTER JOIN	all rows of both (MySQL has no keyword; use UNION)
CROSS JOIN	every left row paired with every right row
Self join	a table joined to itself, using two aliases

INNER JOIN: matches only

SELECT c.name, o.id AS order_id, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
ORDER BY o.id;

name	order_id	total
Asha	101	250.00
Asha	102	120.50
Ben	103	80.00
Chen	105	500.00

Dev (no orders) and order 104 (no customer) are both missing. Asha appears twice because she has two orders: a join returns one row per matching pair, not one per customer.

LEFT JOIN: everything from the left

SELECT c.name, o.id AS order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.id;

name	order_id	total
Asha	101	250.00
Asha	102	120.50
Ben	103	80.00
Chen	105	500.00
Dev	NULL	NULL

Dev is now present, with NULL in the columns that come from orders. Use LEFT JOIN when the left table is the one you are reporting on ("all customers, with their orders if any").

RIGHT JOIN: everything from the right

SELECT c.name, o.id AS order_id, o.total
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id
ORDER BY o.id;

name	order_id	total
Asha	101	250.00
Asha	102	120.50
Ben	103	80.00
NULL	104	60.00
Chen	105	500.00

Now the orphan order 104 appears, with a NULL name. A right join is a left join with the tables swapped; most people always write LEFT JOIN and reorder the tables, so the query reads in one direction.

FULL OUTER JOIN: both sides

MySQL does not have FULL OUTER JOIN. Combine a left and a right join with UNION, which also removes the duplicates of the matching rows:

SELECT c.name, o.id AS order_id
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
UNION
SELECT c.name, o.id
FROM customers c RIGHT JOIN orders o ON o.customer_id = c.id
ORDER BY order_id, name;

name	order_id
Dev	NULL
Asha	101
Asha	102
Ben	103
NULL	104
Chen	105

Both Dev (no orders) and order 104 (no customer) are there. Use UNION ALL only when you are sure the two halves cannot overlap, since it keeps duplicates.

Finding rows with no match (anti-join)

"Which customers have never ordered?" Left join, then keep rows where the right side is NULL:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

name
Dev

Test IS NULL on a column that can never be NULL in a real match, such as the right table's primary key. NOT EXISTS gives the same answer and is often the clearest way to write it:

SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

name
Dev

The NOT IN trap

NOT IN looks like the simplest anti-join, and it silently breaks when the list contains a NULL. Our orders table has one (order 104):

SELECT name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);

This returns no rows at all, although Dev has no orders. The reason: id NOT IN (1, 1, 2, NULL, 3) means "not equal to every value", and comparing anything with NULL gives unknown, so no row can be proved to qualify. Two fixes:

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);

name
Dev

or use NOT EXISTS, as above. Prefer NOT EXISTS or LEFT JOIN … IS NULL whenever the subquery column can be NULL.

Filter in ON or in WHERE?

With inner joins it makes no difference. With a left join it changes the meaning completely.

Condition in ON: restricts which orders match, but keeps every customer:

SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
ORDER BY c.id, o.id;

name	order_id
Asha	101
Asha	102
Ben	NULL
Chen	NULL
Dev	NULL

Condition in WHERE: filters the final rows, so customers without a matching paid order disappear:

SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
ORDER BY c.id, o.id;

name	order_id
Asha	101
Asha	102

The WHERE version turned the left join into an inner join, because rows with NULL status fail the test. Rule: put conditions about the right table in ON if you want to keep the left rows.

CROSS JOIN: every combination

SELECT c.name, o.id
FROM customers c
CROSS JOIN (SELECT 1 AS id UNION SELECT 2) o
ORDER BY c.id, o.id;

This returns 4 customers × 2 rows = 8 rows (Asha 1, Asha 2, Ben 1, Ben 2, and so on). It is useful for generating combinations such as every product with every size, and dangerous when you forget a join condition by accident: two tables of 10,000 rows give 100 million rows.

Self join: a table against itself

Find pairs of customers who live in the same city. The condition a.id < b.id stops each pair from appearing twice and a customer from pairing with themselves:

SELECT a.name AS one, b.name AS other, a.city
FROM customers a
JOIN customers b ON a.city = b.city AND a.id < b.id;

one	other	city
Asha	Chen	Pune

Self joins are also how you read a hierarchy stored in one table, such as employees and their managers.

Joins and aggregates

To count orders per customer including customers with none, count a column of the right table:

SELECT c.name, COUNT(o.id) AS orders, COALESCE(SUM(o.total), 0) AS spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

name	orders	spent
Asha	2	370.50
Ben	1	80.00
Chen	1	500.00
Dev	0	0.00

Using COUNT(*) instead would count Dev's single null-extended row and report 1, which is wrong. COUNT(o.id) skips the NULL and correctly reports 0. COALESCE turns the NULL sum into 0.

Checklist for correct joins

1. Name the join condition in ON using the keys (usually primary key = foreign key).
2. Decide which table you must keep all rows of, and make it the left one.
3. Expect one row per matching pair. If you join to a table with many matches per row, totals can multiply. Aggregate before joining when needed.
4. Prefix columns with aliases so that id or name is never ambiguous.
5. Watch NULL keys. They never match anything, not even each other.
6. Index the foreign key columns used in joins; check with EXPLAIN.
7. Check the row count against what you expect before trusting a result.

Learn more

Each join type has a lesson with exercises in the free MySQL course: INNER JOIN, LEFT and RIGHT JOIN, self and cross joins, UNION and EXISTS, ANY and ALL. The wider MySQL cheat sheet covers everyday commands, and the MySQL interview questions include several join questions. A 60-day schedule is in the MySQL and SQL study plan.

Quick answers

What is the difference between INNER and LEFT JOIN? Inner keeps only matching rows; left also keeps unmatched left rows, with NULLs on the right.

Why did my left join lose rows? A WHERE condition on the right table removed the NULL rows. Move it to ON.

Does MySQL support FULL OUTER JOIN? Not directly; combine a left and right join with UNION.

Why did my row count multiply? One-to-many joins return a row per match. Aggregate first or use DISTINCT carefully.

What is USING? A shorthand when both tables have a column of the same name: JOIN b USING (customer_id).