There are six SQL join types you write with the JOIN keyword: INNER, LEFT, RIGHT, FULL, CROSS, and the self join, which is any of those with the same table on both sides. Two more, the semi join and the anti join, have no keyword in most databases and are written with EXISTS and NOT EXISTS. Each type answers one question: what happens to a row that has no match on the other side?
That question is easy. The surprises come from duplicate keys, NULL keys, and syntax that one database accepts and another silently reinterprets. Every result below comes from running the query on PostgreSQL 18 and SQLite, which returned identical rows for every query that both support. The MySQL and SQL Server notes were checked on MySQL 8.4 and SQL Server 2022.
SQL join types at a glance
| Join type | Keeps | Rows from the sample data |
|---|---|---|
INNER JOIN |
Only pairs that match | 3 |
LEFT JOIN |
Every left row, plus matches | 4 |
RIGHT JOIN |
Every right row, plus matches | 4 |
FULL JOIN |
Every row from both sides | 5 |
CROSS JOIN |
Every combination, no condition | 12 |
| Self join | Any type above, table joined to itself | 3 (inner) or 4 (left) |
Semi join (EXISTS) |
Left rows with at least one match, once each | 2 |
Anti join (NOT EXISTS) |
Left rows with no match | 1 |
OUTER is optional everywhere: LEFT JOIN and LEFT OUTER JOIN are the same thing. Plain JOIN means INNER JOIN.
The sample tables
Two small tables, with the gaps that real data always has. Legal has no employees. Ken has no department.
CREATE TABLE departments (dept_id INTEGER PRIMARY KEY, dept_name TEXT);
CREATE TABLE employees (
emp_id INTEGER PRIMARY KEY, name TEXT,
dept_id INTEGER, manager_id INTEGER, salary INTEGER
);
INSERT INTO departments VALUES (10, 'Engineering'), (20, 'Sales'), (30, 'Legal');
INSERT INTO employees VALUES
(1, 'Ada', 10, NULL, 120000),
(2, 'Linus', 10, 1, 95000),
(3, 'Grace', 20, 1, 50000),
(4, 'Ken', NULL, 1, 70000);
-- used in the non-equi join section
CREATE TABLE pay_bands (band TEXT, low INTEGER, high INTEGER);
INSERT INTO pay_bands VALUES ('B1', 0, 50000), ('B2', 50000, 100000), ('B3', 100000, 200000);
Engineering has two employees. That detail matters more than it looks.
Every SQL join type, one query each
Each query below joins employees e to departments d on d.dept_id = e.dept_id and changes only the join keyword.
INNER JOIN: only matches
An inner join returns one row for every pair of rows whose condition is true. Rows without a partner disappear from both sides.
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON d.dept_id = e.dept_id;
| name | dept_name |
|---|---|
| Ada | Engineering |
| Linus | Engineering |
| Grace | Sales |
Ken is gone because his dept_id is NULL. Legal is gone because nobody points at it.
LEFT JOIN: keep every left row
A left join returns the inner join result plus every left row that found no match, with NULL in the right-hand columns. Swapping the keyword above for LEFT JOIN adds one row: Ken | NULL.
The classic LEFT JOIN bug is a filter on the right table placed in WHERE instead of ON, which quietly turns it back into an inner join. The LEFT JOIN SQL guide covers that trap, anti-joins, and aggregate fan-out with tested output.
RIGHT JOIN: keep every right row
A right join is a left join with the tables swapped. employees RIGHT JOIN departments returns the three matches plus NULL | Legal. It produces exactly the same rows as departments LEFT JOIN employees.
The MySQL manual recommends LEFT JOIN over RIGHT JOIN "to keep code portable across databases." Reading a query top to bottom is easier when the table you are preserving comes first.
FULL OUTER JOIN: keep everything
A full join returns the matches, plus unmatched rows from the left, plus unmatched rows from the right.
| name | dept_name |
|---|---|
| Ada | Engineering |
| Linus | Engineering |
| Grace | Sales |
| Ken | NULL |
| NULL | Legal |
This is the join for reconciliation work: comparing two exports, finding records that exist in one system but not the other. MySQL does not have it. Its join grammar lists only LEFT and RIGHT outer joins, so you have to emulate it, and the obvious emulation is wrong (see below).
MySQL can also get it wrong without an error. FULL is not a reserved word there, so in employees FULL JOIN departments USING (dept_id) MySQL reads FULL as an alias for employees and runs an inner join. MySQL 8.4 returned 3 rows. The only hint was a deprecation warning about FULL as an unquoted identifier, which you see only if you run SHOW WARNINGS. When the table already has an alias (employees e FULL JOIN ...), the same mistake is a syntax error instead.
CROSS JOIN: every combination
A cross join pairs every left row with every right row and takes no condition. Four employees times three departments is 12 rows. Use it on purpose for things like generating a calendar grid (every store × every day). Use it by accident and a million-row table times another million-row table is a trillion rows.
Self join: a table joined to itself
A self join is not a separate keyword. It is any join where both sides are the same table, told apart by aliases. The classic case is a manager lookup:
SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id;
| name | manager |
|---|---|
| Ada | NULL |
| Linus | Ada |
| Grace | Ada |
| Ken | Ada |
With INNER JOIN instead, Ada drops out because she has no manager. For an org chart deeper than one level, a self join turns into a recursive CTE; the CTE guide walks through that.
Row counts: the arithmetic behind join types
A join's row count follows directly from the data, and checking it is the fastest way to catch a wrong join. The formulas:
- Inner: for each key, (left rows with that key) × (right rows with that key), summed. Here: 2 × 1 for Engineering + 1 × 1 for Sales = 3.
- Left: inner + left rows with no match. 3 + 1 (Ken) = 4.
- Right: inner + right rows with no match. 3 + 1 (Legal) = 4.
- Full: inner + unmatched left + unmatched right. 3 + 1 + 1 = 5.
- Cross: left rows × right rows. 4 × 3 = 12.
The first formula is the one people forget. Joins multiply. Turn the inner join around so that departments is on the left and select only d.dept_name, and Engineering comes back twice, once per employee. A Venn diagram of two overlapping circles cannot show this, because a join's output is not a subset of either table. It is a set of pairs.
When a join returns more rows than the table you care about, a key on the other side is not unique. If you only wanted to know which departments have staff, you wanted a semi join.
Semi joins and anti joins: the join types without a keyword
A semi join returns each left row that has at least one match, once, no matter how many matches it has. Standard SQL spells it EXISTS:
SELECT d.dept_name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);
-- Engineering, Sales (2 rows; the INNER JOIN version returns 3)
An anti join returns each left row with no match at all:
SELECT d.dept_name
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);
-- Legal
The databases treat these as real join types internally. PostgreSQL planned the NOT EXISTS query as a Hash Right Anti Join. The EXISTS query became a hash join over a de-duplicated employees, which is how the optimizer guarantees one row per department.
Do not write the anti join as NOT IN. WHERE d.dept_id NOT IN (SELECT dept_id FROM employees) returned zero rows on both engines, because Ken's NULL makes every NOT IN comparison unknown instead of true. The SQL cheat sheet lists that trap with its relatives.
Spark SQL is the exception that gives these a keyword. Its join reference defines LEFT SEMI JOIN and LEFT ANTI JOIN. PostgreSQL rejects both as syntax errors, and SQLite reports "unknown join type: LEFT SEMI", so keep them out of queries meant to run anywhere else.
NULL keys never match, in any join type
NULL = NULL is not true, so a NULL join key matches nothing, including another NULL. Joining a one-row table holding NULL to another one-row table holding NULL:
| Join | Rows |
|---|---|
JOIN ... ON a.x = b.y |
0 |
FULL JOIN ... ON a.x = b.y |
2 (each side, unmatched) |
JOIN ... ON a.x IS NOT DISTINCT FROM b.y |
1 |
The FULL JOIN row is the one that bites in reconciliation queries. Two records that both have a NULL region do not pair up. They show up as two "missing" rows, one from each system.
When NULL should match NULL, use the null-safe comparison for your database:
- PostgreSQL:
IS NOT DISTINCT FROM - SQL Server 2022+:
IS NOT DISTINCT FROM - SQLite:
ISorIS NOT DISTINCT FROM. The SQLite docs callISthe compact form, an SQLite extension. - MySQL: the
<=>operator, which the manual calls the "NULL-safe equal to operator"
PostgreSQL rejected the SQLite shorthand a.x IS b.y with a syntax error, so the long form is the portable one. Avoid the COALESCE(a.x, '') = COALESCE(b.y, '') shortcut: it also pairs NULL with real empty strings, and the SQL COALESCE guide shows that join returning 4 rows instead of 2.
Non-equi joins: when one row matches two
A join condition does not have to be =. Range joins attach a bucket to each row, and the bucket edges decide whether the result is correct. With pay bands of 0 to 50,000, 50,000 to 100,000, and 100,000 to 200,000:
SELECT e.name, e.salary, b.band
FROM employees e
JOIN pay_bands b ON e.salary BETWEEN b.low AND b.high;
That returned 5 rows for 4 employees. Grace earns exactly 50,000, and BETWEEN is inclusive at both ends, so she landed in two bands. Half-open ranges fix it:
JOIN pay_bands b ON e.salary >= b.low AND e.salary < b.high
-- 4 rows, Grace in B2 only
The same rule applies to date ranges, IP ranges, and price tiers. Shared boundaries need one inclusive edge and one exclusive edge.
NATURAL JOIN and USING
NATURAL JOIN joins on every column name the two tables share, without you listing them. On the sample tables, the only shared name is dept_id, so employees NATURAL JOIN departments returns the correct 3 rows.
Then someone renames departments.dept_name to name. Now both tables have name too, so the join silently requires e.name = d.name as well. Re-running the identical query returned 0 rows on both PostgreSQL and SQLite. No error. The PostgreSQL documentation states the risk directly: NATURAL "is considerably more risky since any schema changes to either relation that cause a new matching column name to be present will cause the join to combine that new column as well." It also notes that with no shared names, NATURAL JOIN behaves like CROSS JOIN.
USING (dept_id) names the column explicitly, survived the rename (still 3 rows), and is the safe shorthand. It has one behavior worth knowing in outer joins. The single output column is the coalesced value of both sides, which the MySQL manual defines as COALESCE(t1.a, t2.a). In the full join, SELECT dept_id ... USING (dept_id) returned 30 on the Legal row, while SELECT e.dept_id ... ON ... returned NULL there. Pick the column you mean.
SQL Server supports neither. Its FROM clause grammar allows only ON conditions and CROSS JOIN, plus CROSS APPLY and OUTER APPLY.
Emulating FULL OUTER JOIN in MySQL
The common emulation is LEFT JOIN ... UNION ... RIGHT JOIN. It works on the sample data when the select list includes a unique column. Drop that column and it breaks. Here is a per-department row list, the kind of thing you would feed into a headcount:
SELECT d.dept_name FROM employees e LEFT JOIN departments d ON d.dept_id = e.dept_id
UNION
SELECT d.dept_name FROM employees e RIGHT JOIN departments d ON d.dept_id = e.dept_id;
-- 4 rows: Engineering, Legal, Sales, NULL
The real FULL JOIN returns 5 rows, with Engineering twice. UNION removes duplicate rows, and Ada's and Linus's rows were duplicates once name left the select list. The correct emulation keeps everything from the left join and adds only the unmatched right rows:
SELECT d.dept_name FROM employees e LEFT JOIN departments d ON d.dept_id = e.dept_id
UNION ALL
SELECT d.dept_name FROM employees e RIGHT JOIN departments d ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;
-- 5 rows, identical to FULL JOIN
Test the right-hand anti filter on a column that cannot be NULL in a real match, such as the primary key. Both versions were checked against the native FULL JOIN on PostgreSQL and SQLite, and MySQL 8.4 returned the same 4 and 5 rows.
Join types vs join algorithms
A join type is what you ask for. A join algorithm is how the database computes it. The three common algorithms are nested loop, hash join, and merge join, and the planner picks one per join regardless of whether you wrote INNER or LEFT.
On the sample tables, PostgreSQL chose a hash join for the plain inner join. Adding WHERE e.emp_id = 2 switched it to a nested loop with two primary-key index lookups. The range join from the pay-bands example could only run as a nested loop, since a hash join needs an equality condition. These plans come from tiny tables with no collected statistics. On real data the choice depends on table sizes and statistics, so read EXPLAIN on your own database rather than trusting a rule of thumb.
SQL Server lets you force an algorithm with join hints such as INNER HASH JOIN. Its FROM clause documentation notes that once a hint is present, the INNER keyword is no longer optional.
SQL join types across databases
| Feature | PostgreSQL | MySQL | SQL Server | SQLite |
|---|---|---|---|---|
INNER, LEFT, CROSS |
Yes | Yes | Yes | Yes |
RIGHT JOIN |
Yes | Yes | Yes | Since 3.39.0 |
FULL JOIN |
Yes | No | Yes | Since 3.39.0 |
NATURAL JOIN |
Yes | Yes | No | Yes |
USING (col) |
Yes | Yes | No | Yes |
JOIN with no ON |
Syntax error | Cross join (12 rows) | Syntax error | Cross join (12 rows) |
CROSS JOIN ... ON |
Syntax error | Allowed; acts as inner join | Syntax error | Allowed; acts as inner join |
| Null-safe key match | IS NOT DISTINCT FROM |
<=> |
IS NOT DISTINCT FROM (2022+) |
IS |
| Lateral join | LATERAL |
LATERAL (8.0.14+) |
CROSS/OUTER APPLY |
No |
Two rows deserve a note. The MySQL manual says JOIN, CROSS JOIN, and INNER JOIN "are syntactic equivalents" in MySQL, and that without a join condition INNER JOIN and a comma both produce a Cartesian product. The SQLite SELECT docs say the same of JOIN, INNER JOIN, and , and add that CROSS JOIN is the one operator that stops the optimizer from reordering tables. So forgetting ON is a loud error on PostgreSQL and a quiet row explosion on MySQL and SQLite.
Checking a join before you run it
Formatting a query puts each join and its condition on its own line, which makes a missing ON, a stray NATURAL, or an INNER after a LEFT easy to spot. SelfDevKit's SQL tools format and validate SQL offline. A three-table query comes out like this:
SELECT
e.name,
d.dept_name,
m.name AS manager
FROM
employees e
LEFT JOIN departments d ON d.dept_id = e.dept_id
LEFT JOIN employees m ON m.emp_id = e.manager_id
WHERE
e.salary > 60000
The validator checks syntax with a generic SQL grammar, and its limits match the dialect table above. These results come from running the same parser version the app uses:
JOIN departments dwith noONis marked valid. That matches MySQL and SQLite, which run it as a cross join, but PostgreSQL rejects it. Look for theONyourself.LEFT SEMI JOINandLEFT ANTI JOINare marked valid. They are Spark syntax; PostgreSQL and SQLite reject them.CROSS JOIN ... ONis marked invalid, even though SQLite and MySQL accept it.- Legacy outer joins (
*=and Oracle's(+)) are rejected, a useful signal on an old query.
The formatter also has two layout quirks with join syntax. It splits employees NATURAL JOIN departments into employees NATURAL and JOIN departments on separate lines. It breaks IS NOT DISTINCT FROM so that FROM starts a new line, which reads like a new clause. Both queries are unchanged; only the layout is misleading.
Join queries are full of schema names, and the WHERE clause often holds a customer ID or email. A formatter that runs on your machine keeps all of that local. The online SQL formatter guide covers what web-based formatters do with pasted queries.
Picking the right join type
- Need only rows that exist on both sides?
INNER JOIN. - Need every row from one table, with optional details?
LEFT JOIN, with the preserved table first. - Comparing two datasets for gaps on either side?
FULL JOIN, orLEFT JOIN ... UNION ALLwith an anti filter in MySQL. - Asking "does a match exist?"
EXISTS, not a join plusDISTINCT. - Asking "is there no match?"
NOT EXISTS, neverNOT INover a nullable column. - Need every combination?
CROSS JOIN, written explicitly so the intent is visible. - Keys can be
NULLand should match? A null-safe comparison, spelled for your database.
Then check the row count against the formula. If it is higher than the table you care about, something on the other side is not unique. Once the join is right, window functions are usually the next tool for ranking or totaling within it.
Download SelfDevKit to format and validate SQL offline, alongside 50+ other developer tools. See pricing for license options.