Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

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.

~5 min readSQL
#SQL#Relational Database#Queries
3D illustration of intersecting relational data tables with glowing overlapping intersection zones showing join match conditions

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

sql
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.

sql
-- 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

JoinRows kept from leftRows kept from rightUnmatched sideDuplicates rows when…
INNER JOINMatched onlyMatched onlyDroppedAny key matches more than once
LEFT JOINAllMatched onlyRight padded with NULLRight key matches more than once
RIGHT JOINMatched onlyAllLeft padded with NULLLeft key matches more than once
FULL OUTER JOINAllAllEither side paddedEither key matches more than once
CROSS JOINAllAlln/a — every pairAlways: rows = left × right
LEFT JOIN ... WHERE right.pk IS NULLUnmatched only—Anti-joinNever

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.

sql
-- 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:

sql
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

sql
-- 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:

sql
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:

sql
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

sql
SELECT * FROM a JOIN b ON a.key = b.key; -- NULL keys match nothing, ever

NULL = 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:

sql
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 symptomCauseFix
Every aggregate is inflated by a constant factorOne-to-many join multiplied rows before aggregationAggregate at the target grain first, then join
LEFT JOIN returns only matched rowsRight-side predicate in WHEREMove it into ON
column reference "id" is ambiguous (PostgreSQL 42702)Same column name on both sides without a qualifierQualify 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 JOINFix the FROM order; do not mix , and JOIN
operator does not exist: integer = text (PostgreSQL 42883)Type mismatch between join keysCast explicitly, then fix the schema
Result blows up to millions of rowsMissing 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 MySQLNot supportedLEFT ... UNION ... RIGHT
Rows disappear when a key is NULLNULL = NULL is UNKNOWNBackfill keys, or use IS NOT DISTINCT FROM deliberately
Duplicate column name 'id' in a CREATE TABLE AS SELECTSELECT * across joined tablesList columns explicitly
Slow nested-loop join on a large tableNo index on the inner side of the predicateIndex (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 tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.