SQLinner joinleft joinouter joincross joinself join

Joins: INNER, LEFT, RIGHT, FULL, CROSS, SELF

A join pairs rows from two tables on a condition; the join type decides what happens to rows with no partner, and where you put a condition — ON or WHERE — decides whether an outer join stays outer.

▶ Interactive Try queries Interview question See how this works internally →

Progress

What each join keeps

Picture two tables and a condition. INNER JOIN keeps only the pairs where the condition holds. LEFT JOIN keeps every row of the left table, pairing it with matches or with NULLs. RIGHT JOIN is the mirror. FULL OUTER JOIN keeps every row of both. CROSS JOIN pairs everything with everything — n × m rows — and is what you get when you forget the condition. A self join is any of these with the same table on both sides under two aliases.

INNER JOIN silently deletes rows without a partner. That is correct when the row is meaningless without its partner (an order line without a product) and a data-loss bug when it is not (a customer without orders, in a report that should list all customers). Ask, for every join, "which side may legitimately have no match?" — that side wants an outer join.

Which rows survive

UserLLMAgentToolDataDecisionHumanGuardrail

ON versus WHERE

For an inner join it does not matter where the condition goes. For an outer join it is the whole game. A condition in ON decides *which right rows match*; unmatched left rows survive with NULLs. The same condition in WHERE runs *after* the join and throws away the rows whose right side is NULL — turning your LEFT JOIN back into an INNER JOIN.

Rule: conditions about the preserved (left) side go in WHERE; conditions about the nullable (right) side go in ON. The one deliberate exception is the anti-join: LEFT JOIN … WHERE right.id IS NULL, which keeps exactly the rows that had no match.

Same condition, two meanings

1-- All users; their paid orders if any.  (users with no paid orders still appear)
2SELECT u.name, o.id
3FROM users u
4LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid';
5 
6-- Only users who HAVE a paid order.  (the LEFT is now meaningless)
7SELECT u.name, o.id
8FROM users u
9LEFT JOIN orders o ON o.user_id = u.id
10WHERE o.status = 'paid';                 -- NULL = 'paid' is not true → row dropped

How joins execute

Three physical algorithms. Nested loop: for each outer row, scan the inner input — O(n × m) without an index on the inner side, excellent with one. Hash join: build a hash table from the smaller input, probe it with the larger — O(n + m), needs an equality condition and memory for the build side. Merge join: both inputs sorted on the key, walked together — O(n + m) after sorting, ideal when indexes already provide the order.

You do not choose the algorithm; the planner does, from row estimates. You influence it by giving it an equality condition (enables hash and merge), an index on the inner join column (makes nested loop cheap), and accurate statistics. A Nested Loop with a large loops count and a sequential scan underneath is the canonical slow join, and it is usually a missing index on a foreign key. See How a Query Executes: Planner and Executor.

Self joins and many-to-many

A self join compares rows of a table with other rows of the same table: an employee with their manager, a category with its parent, orders by the same customer in the same week. It needs two aliases and almost always an inequality (b.id <> a.id, or b.id > a.id to avoid seeing each pair twice).

A many-to-many relationship is always two one-to-many joins through a junction table: orders → order_items → products. There is no shortcut around the junction, and its own columns (quantity, price paid) are usually the point. See Relationships, Keys and Constraints.

Key points

INNER keeps matches; LEFT/RIGHT/FULL preserve one or both sides with NULLs; CROSS is the product.
ON filters what matches; WHERE filters the result. On an outer join, a WHERE on the nullable side makes it inner.
LEFT JOIN … WHERE right IS NULL is the anti-join idiom; NOT EXISTS is the NULL-safe equivalent.
Nested loop, hash join, merge join — chosen by the planner; enabled by equality conditions, indexes and statistics.
Many-to-many is two joins through a junction table.

Every join, visualised

Every join, on four customers and five orders

The data is small and dirty on purpose: Carla and Dmitri never ordered, order 103 has a NULL customer, order 104 points at a customer id that does not exist.

customers

id	name	country
1	Alice	DE
2	Bob	NL
3	Carla	DE
4	Dmitri	FR

orders

id	customer_id	amount
100	1	120
101	1	40.5
102	2	310
103	NULL	99
104	9	15

customers.id
=> orders.customer_id

SELECT c.id AS cust, c.name, o.id AS ord, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.id

Rows out

Keeps

Only pairs that match on both sides.

#	cust	name	ord	amount
1	1	Alice	100	120
2	1	Alice	101	40.5
3	2	Bob	102	310

Use it when: The default when both sides are required for the row to mean anything: an order line and its product.

The trap: Silently deletes rows. Carla and Dmitri vanish because they have no orders, and orders 103 and 104 vanish because they have no customer. If you are counting customers, you just undercounted.

Try it in the playground

SELECT u.id, u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL
ORDER BY u.id LIMIT 20;

Anti-join: users who never ordered. →

SELECT c.name AS child, p.name AS parent
FROM categories c
LEFT JOIN categories p ON p.id = c.parent_id
ORDER BY 2 NULLS FIRST, 1;

Self-join on a tree. →

When to use — and when not

Use it when

Combining facts stored in different tables — the entire point of a normalised schema.

Avoid it when

When a join produces more rows than the table you are aggregating; aggregate first.
Six-hop self joins over a graph — that is where a graph database earns its keep, see SQL vs NoSQL: Choosing a Data Model.

Failure modes

Inner join silently dropping rows with no partner.
Outer join condition in WHERE.
Forgotten ON clause producing a cross product.
Join key without an index on the many side.

See how this works internally →

Descend one layer: the same topic explained from the machinery up.

How is a join actually executed — nested loop, hash or merge?

↓ Database Internals → Join Algorithms: Nested Loop, Hash, Merge

← Aggregation: COUNT, SUM, AVG, GROUP BY, HAVING Subqueries, CTEs, EXISTS, UNION, CASE →