SQL Joins: INNER, LEFT, RIGHT, FULL OUTER, and CROSS JOIN with Visual Diagrams
Every join type as a match policy, why a WHERE clause on the right table turns LEFT into INNER, and how row multiplication inflates every aggregate.

Quick Summary
A join is a predicate plus a match policy. The predicate (ON ...) says which row pairs are compatible; the join type says what happens to rows that find no partner. INNER JOIN drops them, LEFT JOIN keeps the left side and pads the right with NULL, RIGHT JOIN is the mirror, FULL OUTER JOIN keeps both sides, and CROSS JOIN pairs everything with everything. USING/NATURAL JOIN are shorthand worth avoiding in production SQL because a schema change can silently alter the predicate.
Almost every join bug is one of three things: the predicate is wrong, so rows multiply; the join type says LEFT but a WHERE clause quietly makes it INNER; or a NULL key matches nothing and an entire join becomes a no-op. None of these raise errors. They return a number that looks reasonable.
Basic syntax and query signatures
SELECT o.id, o.total, c.name
FROM orders AS o
INNER JOIN customers AS c
ON c.id = o.customer_id
AND c.tenant_id = o.tenant_id
WHERE o.status = 'paid';Standard join types: INNER, LEFT [OUTER], RIGHT [OUTER], FULL [OUTER], CROSS, plus NATURAL and the USING (col, ...) shorthand. PostgreSQL adds LATERAL, SQL Server uses CROSS APPLY / OUTER APPLY for the same idea.
-- NULL-side filter belongs in ON, not WHERE
SELECT c.id, c.name, COUNT(o.id) AS paid_orders
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
AND o.status = 'paid'
GROUP BY c.id, c.name;
-- anti-join: customers with no orders at all
SELECT c.id, c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;Note COUNT(o.id) rather than COUNT(*): on an unmatched left row the padded right columns are NULL, so COUNT(*) returns 1 and COUNT(o.id) returns 0. That single character is the difference between a correct conversion report and one that claims every customer ordered.
USING (id) merges the join column into one output column and is equivalent to ON a.id = b.id for INNER/LEFT, but with FULL OUTER JOIN it behaves like COALESCE(a.id, b.id) — and SELECT a.id afterwards becomes ambiguous in some engines. Prefer explicit ON.
Key comparison table
| Join | Rows kept from left | Rows kept from right | Unmatched side | Duplicates rows when… |
|---|---|---|---|---|
INNER JOIN | Matched only | Matched only | Dropped | Any key matches more than once |
LEFT JOIN | All | Matched only | Right padded with NULL | Right key matches more than once |
RIGHT JOIN | Matched only | All | Left padded with NULL | Left key matches more than once |
FULL OUTER JOIN | All | All | Either side padded | Either key matches more than once |
CROSS JOIN | All | All | n/a — every pair | Always: rows = left × right |
LEFT JOIN ... WHERE right.pk IS NULL | Unmatched only | — | Anti-join | Never |
RIGHT JOIN is LEFT JOIN with the tables written the other way round; reviewers prefer LEFT because the driving table reads first. There is no FULL INNER — "outer" is the modifier, and FULL JOIN without the keyword means the same as FULL OUTER JOIN in PostgreSQL, SQL Server, and Oracle.
Practical edge cases and deep dives
1. Row multiplication and inflated totals
Joining a table with more than one matching row duplicates the left row, and any aggregate over the left side then counts it twice.
-- Wrong: 12 orders × 3 line items = 36 rows; SUM(o.total) is 3× too big
SELECT c.id, SUM(o.total) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items i ON i.order_id = o.id
GROUP BY c.id;Two fixes, and they mean different things. If line items are the grain you want, sum the item level (SUM(i.quantity * i.unit_price)). If order totals are the grain, aggregate first and join the aggregate:
WITH order_totals AS (
SELECT customer_id, SUM(total) AS revenue, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
)
SELECT c.id, COALESCE(t.revenue, 0) AS revenue, COALESCE(t.order_count, 0) AS order_count
FROM customers c
LEFT JOIN order_totals t ON t.customer_id = c.id;The COALESCE matters: a customer with no orders produces NULL, not 0, and NULL in a chart silently disappears from the bar line while 0 renders as a zero-height bar. Both are defensible, but the choice has to be made on purpose.
2. The LEFT JOIN that is really an INNER JOIN
-- Returns only customers who have a paid order: the WHERE undoes the LEFT
SELECT c.id, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';
-- Returns every customer, with NULL order columns when nothing matched
SELECT c.id, o.id
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
AND o.status = 'paid';WHERE o.status = 'paid' evaluates NULL = 'paid', which is UNKNOWN, which is not TRUE, so padded rows are filtered out. SQL Server's optimizer is even happy to convert the whole thing to an inner join, so the execution plan confirms what the result already shows. Any predicate on the right-hand table that does not tolerate NULL — =, <, IN, LIKE — belongs in ON. Predicates that explicitly accept NULL (IS NULL, IS DISTINCT FROM) can stay in WHERE.
3. FULL OUTER JOIN in MySQL
MySQL has no FULL OUTER JOIN. The standard replacement is a union of two anti-joins with the inner join, deduplicated by UNION:
SELECT c.id, c.name, o.id AS order_id
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
UNION
SELECT c.id, c.name, o.id
FROM customers c RIGHT JOIN orders o ON o.customer_id = c.id;UNION removes duplicates, which is what makes this correct — UNION ALL would double every matched pair. If you need to know which side produced the row, add a literal marker per branch ('left', 'right') and accept that matched rows are then intentionally listed once per branch; use UNION ALL with a WHERE NOT EXISTS guard instead when that duplication is unacceptable.
4. Correlated top-N with LATERAL / CROSS APPLY
A join can carry a subquery that sees the current left row, which is the clean way to get "latest three orders per customer" without a window function:
SELECT c.id, t.id AS order_id, t.placed_at
FROM customers c
CROSS JOIN LATERAL (
SELECT o.id, o.placed_at
FROM orders o
WHERE o.customer_id = c.id
ORDER BY o.placed_at DESC
LIMIT 3
) AS t;CROSS JOIN LATERAL drops customers with no orders; LEFT JOIN LATERAL (...) ON TRUE keeps them with NULL columns. The SQL Server equivalent is CROSS APPLY (drop) and OUTER APPLY (keep). Without an index on orders (customer_id, placed_at DESC) this runs the subquery once per customer, so the plan matters more than the syntax.
5. NULL keys never match — including to each other
SELECT * FROM a JOIN b ON a.key = b.key; -- NULL keys match nothing, everNULL = NULL is UNKNOWN, not TRUE, so a nullable foreign key or a nullable natural key silently drops rows. IS NOT DISTINCT FROM (PostgreSQL) and <=> (MySQL) compare with null-safe equality:
SELECT * FROM a JOIN b ON a.key IS NOT DISTINCT FROM b.key;That is convenient and usually the wrong fix: a nullable join key means the model allows "this row belongs to nobody", and pairing all of those rows with each other is rarely the intent. Fix the data (backfill, or make the column NOT NULL) and keep = so future nulls surface as missing rows rather than as accidental matches.
Common mistakes and runtime errors
| Error or symptom | Cause | Fix |
|---|---|---|
| Every aggregate is inflated by a constant factor | One-to-many join multiplied rows before aggregation | Aggregate at the target grain first, then join |
LEFT JOIN returns only matched rows | Right-side predicate in WHERE | Move it into ON |
column reference "id" is ambiguous (PostgreSQL 42702) | Same column name on both sides without a qualifier | Qualify with the table alias |
Unknown column 'o.customer_id' in 'on clause' (MySQL 1054) | Alias used before it is introduced, or a comma-join mixed with JOIN | Fix the FROM order; do not mix , and JOIN |
operator does not exist: integer = text (PostgreSQL 42883) | Type mismatch between join keys | Cast explicitly, then fix the schema |
| Result blows up to millions of rows | Missing or wrong join predicate (accidental cartesian) | Check row counts before/after; every JOIN needs a predicate unless CROSS is intended |
FULL OUTER JOIN syntax error on MySQL | Not supported | LEFT ... UNION ... RIGHT |
Rows disappear when a key is NULL | NULL = NULL is UNKNOWN | Backfill keys, or use IS NOT DISTINCT FROM deliberately |
Duplicate column name 'id' in a CREATE TABLE AS SELECT | SELECT * across joined tables | List columns explicitly |
| Slow nested-loop join on a large table | No index on the inner side of the predicate | Index (join_key) on the inner table; check EXPLAIN (ANALYZE) |
A final word on join order. FROM a, b WHERE a.id = b.a_id is an inner join written in 1989 style; it still works, and it is still the source of accidental cartesian products when a predicate is edited out during debugging. Write JOIN ... ON everywhere so a missing predicate is a syntax error rather than a slow query.
Try matching browser tools
All toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesSQL Cursors vs Set-Based Operations: Syntax, Memory, and Performance
Why a row-by-row cursor costs minutes where one UPDATE takes seconds, how server cursors spool to disk, and the set-based replacements for each loop.
SQL String Functions: CONCAT, SUBSTRING, REPLACE, and Pattern Matching
The concatenation and slice functions that differ between engines, why LIKE 'x%' uses an index and '%x%' cannot, plus escaping and NULL rules.
SQL Date and Time Functions: INTERVAL Arithmetic, Time Zones, and DST
timestamptz versus timestamp, half-open ranges that keep an index, and the daylight saving gaps that make local scheduling wrong once a year.
SQL Row Locking: SELECT FOR UPDATE, SKIP LOCKED, and NOWAIT
How FOR UPDATE locks rows until commit, why the plan decides the lock scope, and the SKIP LOCKED claim-queue pattern that scales to many workers.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.