beginnerintermediateadvanced

PostgreSQL · Joins

Joins

The four join types, CROSS/self joins, what makes a predicate correct and fast, and how to catch a row-count explosion before it reaches an aggregate

LEFT keeps every left rowunmatched right columns come back NULL
CROSS JOINevery combination, no ON clause, on purpose
Join on a unique columnusually a foreign key — predictable cardinality
Two one-to-many joins multiply3 items × 2 shipments = 6, not 5

1.INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN

INNER JOIN keeps only rows that match on both sides. LEFT JOIN keeps every row from the left table, filling in NULLs when there is no match on the right. RIGHT JOIN is the mirror image. FULL OUTER JOIN keeps every row from both sides, matched or not.

Key features

INNER JOIN returns only rows with a match on both sides
LEFT JOIN keeps every left-table row; unmatched right columns come back NULL
RIGHT JOIN keeps every right-table row; unmatched left columns come back NULL
FULL OUTER JOIN keeps every row from both tables, matched or not
RIGHT JOIN is rarely needed in practice — swapping table order and using LEFT JOIN reads the same way to most authors

What happens to an unmatched row, by join type

What happens to an unmatched row, by join type
Join type	Unmatched left row	Unmatched right row
INNER JOIN	dropped	dropped
LEFT JOIN	kept, right columns NULL	dropped
RIGHT JOIN	dropped	kept, left columns NULL
FULL OUTER JOIN	kept, right columns NULL	kept, left columns NULL

INNER vs LEFT — same tables, different treatment of a no-match

INNER JOIN

customers × orders, matched only
a customer with no orders vanishes from the result
result size can only shrink or stay equal

LEFT JOIN

every customer row survives
a customer with no orders gets one row, order columns NULL
result size is at least the left table's row count

Command

sql

SELECT ... FROM a INNER JOIN b ON a.key = b.key;
SELECT ... FROM a LEFT JOIN b ON a.key = b.key;
SELECT ... FROM a RIGHT JOIN b ON a.key = b.key;
SELECT ... FROM a FULL OUTER JOIN b ON a.key = b.key;

INNER drops unmatched rows on both sides; LEFT keeps every left row; RIGHT keeps every right row; FULL OUTER keeps both — the join type is really just "what happens to a row with no match."

2.CROSS JOIN and Self Joins

CROSS JOIN pairs every row of one table with every row of another, with no matching condition — a deliberate Cartesian product. A self join joins a table to itself, using two aliases, typically to compare rows within the same table.

Key features

CROSS JOIN produces rows_a × rows_b rows — no ON clause filters it
A comma-separated FROM list with no WHERE join predicate is an implicit CROSS JOIN
A self join requires two aliases for the same table to distinguish the two roles a row can play
Self joins commonly model hierarchies, like an employee row referencing a manager row in the same table
CROSS JOIN is intentional and rare; an accidental one is almost always a missing ON clause

CROSS JOIN vs self join — what each actually answers

CROSS JOIN vs self join — what each actually answers
Shape	Question it answers	Typical use
CROSS JOIN	"every combination of A and B"	generating a size × color product matrix
Self join	"how does this row relate to another row in the same table?"	employee → manager, both in employees

Command

sql

SELECT ... FROM a CROSS JOIN b;

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

CROSS JOIN pairs every row with every row on purpose (no ON clause); a self join is an ordinary join where two aliases point at the same table to compare rows within it.

3.How Join Predicates Affect Correctness and Performance

The ON clause decides which rows count as a match — get it wrong and the join is silently incorrect, not just slow. A predicate on an indexed, correctly-typed column also lets the planner use an index; a predicate wrapped in a function or comparing mismatched types often cannot.

Key features

The ON clause defines correctness: which rows are considered related, not merely how they are displayed together
A predicate comparing mismatched types (text to integer) can silently prevent index use via an implicit cast
ON col1 = col2 OR col1 IS NULL is a different, usually unintended, correctness statement from ON col1 = col2
A predicate wrapped in a function, like ON lower(a.email) = b.email, cannot use a plain index on a.email
A missing or too-loose predicate multiplies matched rows rather than raising an error

Predicate shape vs what the planner can do with it

Predicate shape vs what the planner can do with it
Predicate shape	Can use a plain B-tree index?
a.customer_id = b.id (same type)	yes
a.customer_id = b.id (int vs text, implicit cast)	often no
lower(a.email) = b.email	no — needs an expression index on lower(a.email)
a.customer_id = b.id AND a.active	yes, on the equality part

Command

sql

-- correct and index-friendly
SELECT ... FROM a JOIN b ON a.b_id = b.id;

-- correctness bug: matches more than intended
SELECT ... FROM a JOIN b ON a.status = b.status;

The ON clause is both a correctness statement (which rows count as related) and a performance lever (what the planner can index) — join on a column the schema guarantees is unique on at least one side, usually a foreign key.

4.One-to-One, One-to-Many and Many-to-Many Joins

One-to-one means each row on one side matches at most one row on the other. One-to-many means one row on one side can match several rows on the other. Many-to-many needs a junction table in between, because neither side alone can hold the relationship.

Key features

One-to-one: a foreign key that also carries a UNIQUE constraint
One-to-many: a plain foreign key — many rows on the "many" side can share one value on the "one" side
Many-to-many: neither table can hold the relationship alone — it needs a junction table with two foreign keys
Joining the "many" side inflates row count on the "one" side unless explicitly aggregated back down
A many-to-many join always passes through the junction table — never table A joined directly to table B

Relationship shape → schema signal → join row-count effect

Relationship shape → schema signal → join row-count effect
Shape	Schema signal	Effect of joining
One-to-one	FK column also UNIQUE	row count unchanged
One-to-many	plain FK, no uniqueness	"one" side rows repeat per match
Many-to-many	junction table with two FKs	both sides repeat, routed through the junction

Command

sql

-- one-to-many
SELECT p.name, o.id FROM products p JOIN order_items o ON o.product_id = p.id;

-- many-to-many via a junction table
SELECT s.name, c.title
FROM students s
JOIN enrollments e ON e.student_id = s.id
JOIN courses c ON c.id = e.course_id;

The relationship shape lives in the schema, not the query: a UNIQUE foreign key is one-to-one, a plain foreign key is one-to-many, and two foreign keys on a junction table is many-to-many.

5.Recognizing Accidental Cartesian Products and Duplicate-Row Explosions

A row-count explosion happens when a query joins through a one-to-many relationship without accounting for it — a single "one" row appears once per match on the "many" side, inflating any SUM or COUNT computed alongside it.

Key features

Joining through two independent one-to-many relationships multiplies, not adds, row counts
A SUM() or COUNT() computed after such a join is inflated unless the multiplication is accounted for
DISTINCT can mask the symptom without fixing the cause, and can hide genuinely distinct rows too
Aggregate first (in a subquery or CTE), then join the pre-aggregated result, to avoid the multiplication entirely
A row count that jumps far beyond what one side of the join has is the tell-tale symptom

How a row count grows through chained one-to-many joins

How a row count grows through chained one-to-many joins
Joined tables	Rows per order	Why
orders alone	1	baseline
orders + order_items (3 items)	3	one row per item
orders + order_items (3) + shipments (2)	6	every item × every shipment event

One order, two one-to-many joins, multiplied rows

JOINorder_itemsJOINshipments

1 order

orders.id = 1

× 3 order_items

one row per item

× 2 shipment events

independent one-to-many

6 result rows

3 × 2, not 3 + 2

Command

sql

-- wrong: SUM inflated by the shipments join
SELECT o.id, SUM(i.price)
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN shipments s ON s.order_id = o.id
GROUP BY o.id;

-- right: aggregate order_items first, then join once
SELECT o.id, totals.item_total
FROM orders o
JOIN (SELECT order_id, SUM(price) AS item_total FROM order_items GROUP BY order_id) totals
    ON totals.order_id = o.id;

Two independent one-to-many joins in the same query multiply row counts, not add them — aggregate each child down to one row per parent first, then join the pre-aggregated result.

6.Determining Why a JOIN Changes Row Cardinality

Before trusting a join's row count, ask three questions in order: is the join predicate correct, is the relationship one-to-one/one-to-many/many-to-many, and does the join type (INNER vs LEFT) add or drop unmatched rows. Each answer independently changes the row count.

Key features

Compare COUNT(*) before and after each join, one join at a time, to isolate which one caused a jump
A row count that jumps beyond either input table size points at a bad predicate or a many-side multiplication
A row count smaller than the left table with INNER JOIN is expected whenever some left rows have no match
EXPLAIN ANALYZE's actual row counts per join node show exactly where a multiplication happens
Building the query incrementally, one join at a time, is more reliable than staring at the final result

Three independent questions to ask about a surprising row count

Three independent questions to ask about a surprising row count
Question	What to check
Is the predicate right?	is it comparing the actual key columns, correctly typed?
What is the relationship shape?	one-to-one, one-to-many, or many-to-many — via schema constraints
What join type is used?	INNER drops unmatched rows, LEFT/FULL keep and pad them

Command

sql

EXPLAIN ANALYZE
SELECT o.id, i.sku
FROM orders o
JOIN order_items i ON i.order_id = o.id;
-- "actual rows" at each join node shows exactly how many rows it produced

A join's row count changes for exactly one of three reasons: the predicate, the relationship shape, or the join type — isolate which one by checking COUNT(*) after each join is added, one at a time.

Spotted a mistake on this page? Tell us.

Read Sheet