September 12, 2026 · Yunus Emre Vurgun

SQL Join Types: INNER, LEFT, RIGHT, FULL

sql · databases · reference · cheat-sheet

SQL has six join types and most queries use two of them. The confusion is always the same: which side's rows survive when there is no match. This guide answers that question once, with the exact syntax for each type.

All six joins

Type	Which rows survive
INNER JOIN	Only rows with a match in both tables.
LEFT JOIN	All left rows, plus matched right rows.
RIGHT JOIN	All right rows, plus matched left rows.
FULL OUTER JOIN	All rows from both tables, NULLs for non-matches.
CROSS JOIN	Every combination (Cartesian product).
SELF JOIN	A table joined with itself, for hierarchical data.

The syntax

SELECT * FROM a INNER JOIN b ON a.id = b.a_id
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id
SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id
SELECT * FROM a CROSS JOIN b
SELECT * FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id

Two rules that prevent most join bugs

Filter placement changes LEFT JOIN results. A condition on the right table in the WHERE clause discards the NULL-extended rows and silently converts your LEFT JOIN into an INNER JOIN. Put right-table filters in the ON clause when you want unmatched left rows to survive.

CROSS JOIN is almost never what you want. Two tables of 10,000 rows produce 100,000,000 output rows. If a query returns absurdly many rows, a missing or wrong ON condition is the first suspect.

Fetch it as data

This reference is the SQL Join Types dataset — every type with its description and syntax, one request away:

curl "https://yjtoon.com/api/dataset/sql-join-types?format=toon"

For query shapes beyond joins, see SQL Query Types, and for when not to use SQL at all, NoSQL Databases Overview.