SQL JOIN Cheat Sheet

Every JOIN type with diagrams, syntax, and when to use each one.

Updated October 23, 2026

The JOIN types at a glance#

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

JOIN	Keeps	Unmatched rows
`INNER JOIN` (or `JOIN`)	only matching pairs	dropped from both sides
`LEFT JOIN`	every row of the left (first) table	right columns become `NULL`
`RIGHT JOIN`	every row of the right (second) table	left columns become `NULL`
`FULL OUTER JOIN`	every row of both tables	`NULL` on whichever side is missing
`CROSS JOIN`	every combination	(no matching at all)
self join	a table joined to itself with two aliases	depends on the join type used

Sample data used below#

customers: Asha (1), Ravi (2), Meera (3), John (4)

orders

order_id	customer_id	amount
101	1	250
102	1	120
103	2	560
104	5	90

Meera and John have no orders; order 104 points to a customer that does not exist.

Join	Rows returned
INNER	3
LEFT	5
RIGHT	4
FULL OUTER	6
CROSS	16 (4 × 4)

INNER JOIN: only matches#

SQLRun

SELECT c.name, o.order_id
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id;

Output

name | order_id
-----+---------
Asha | 101
Asha | 102
Ravi | 103

LEFT JOIN: all rows from the left table#

SQLRun

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;

Output

name  | order_id
------+---------
Asha  | 101
Asha  | 102
Ravi  | 103
Meera | NULL
John  | NULL

RIGHT JOIN: all rows from the right table#

SQLRun

SELECT c.name, o.order_id
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id;
-- same rows as: FROM orders o LEFT JOIN customers c ON ...

Output

name | order_id
-----+---------
Asha | 101
Asha | 102
Ravi | 103
NULL | 104

FULL OUTER JOIN: everything#

SQLRun

SELECT c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id;

Output

name  | order_id
------+---------
Asha  | 101
Asha  | 102
Ravi  | 103
Meera | NULL
John  | NULL
NULL  | 104

MySQL has no FULL OUTER JOIN. Emulate it:

SQLRun

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

Find rows with no match (anti-join)#

SQLRun

-- Customers who never ordered
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

-- Same result, often clearer
SELECT c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

Output

name
-----
Meera
John

Avoid NOT IN (SELECT customer_id FROM orders): one NULL in the subquery makes it return no rows.

Self join: a table joined to itself#

SQLRun

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Output

employee | manager
---------+--------
Asha     | Priya
Ravi     | Meera
Meera    | NULL
John     | Priya
Priya    | NULL

To pair rows that share a value without duplicates, add AND a.id < b.id to the join condition.

CROSS JOIN: every combination#

SQL

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;     -- rows = rows(sizes) × rows(colors)

Joining three or more tables#

SQL

SELECT o.order_id, c.name, p.name AS product, oi.quantity
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 products p     ON p.product_id  = oi.product_id;

Each new table needs its own ON condition linking it to a table already in the query.

ON vs WHERE in a LEFT JOIN#

SQLRun

-- Condition in ON: every customer kept
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.amount > 200;

Output

name  | order_id
------+---------
Asha  | 101
Ravi  | 103
Meera | NULL
John  | NULL

SQLRun

-- Condition in WHERE: unmatched customers disappear
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 200;

Output

name | order_id
-----+---------
Asha | 101
Ravi | 103

Rule: in a LEFT JOIN, filters on the right table go in ON; filters on the left table go in WHERE.

Traps to avoid#

Trap	What happens	Fix
Missing or wrong `ON`	accidental cross join, huge row counts	join foreign key to primary key
Ambiguous column (`name` in both tables)	error	prefix with the alias: `c.name`
`SUM` after joining a "many" table	amounts counted once per child row (fan-out)	aggregate in a subquery/CTE first
`COUNT(*)` after a LEFT JOIN	unmatched rows count as 1	`COUNT(right_table.id)`
`INNER JOIN` after a `LEFT JOIN` on the same branch	unmatched rows dropped again	keep the branch as `LEFT JOIN`

Full lessons: Introduction to JOINs, INNER, LEFT, RIGHT, FULL OUTER, SELF, CROSS, Multiple JOINs.

Want the full explanations? Go to the Learn SQL course →