Joins are the most fundamental and most misused feature in relational SQL. Get them wrong and you either lose rows silently or produce a Cartesian explosion that brings your database to its knees. This is the 2026 guide that covers every join type with real examples and production performance notes.
What changed in 2026
- LATERAL joins are now standard knowledge — they unlock row-by-row subqueries that used to require ugly correlated subqueries or application-side loops.
- pgvector + joins are a common pattern: join a vector similarity search result against a relational table in a single query.
- Query planners improved — Postgres 17 and MySQL 9 both ship better hash-join and merge-join selection, but the fundamentals of what indexes you need have not changed.
- Analytical SQL on OLTP grew: DuckDB embedding and Postgres column-store extensions let you run heavier joins on the same cluster.
The join types at a glance
| Join type |
Rows returned |
Use when |
| INNER JOIN |
Only matching rows in both tables |
You need related data from both sides |
| LEFT JOIN |
All left rows, nulls for non-matches |
Optional relationship (user → subscription) |
| RIGHT JOIN |
All right rows, nulls for non-matches |
Rarely; flip table order and use LEFT instead |
| FULL OUTER JOIN |
All rows from both sides |
Reconciliation, diff queries |
| CROSS JOIN |
Every combination (Cartesian) |
Enumeration, matrix generation |
| LATERAL JOIN |
Correlated subquery per row |
Top-N per group, row-by-row computation |
INNER JOIN
SELECT o.id, o.total, u.email
FROM orders o
INNER JOIN users u ON u.id = o.user_id
WHERE o.created_at > now() - interval '30 days';
Returns only orders that have a matching user. If a user was deleted and user_id still exists as a dangling FK, that order disappears from results — which is often the bug.
LEFT JOIN
SELECT u.id, u.email, s.plan
FROM users u
LEFT JOIN subscriptions s ON s.user_id = u.id;
Returns every user. If they have no subscription, s.plan is NULL. Filter WHERE s.plan IS NULL to find free users. This is the "optional relationship" pattern.
FULL OUTER JOIN
SELECT a.id AS a_id, b.id AS b_id
FROM table_a a
FULL OUTER JOIN table_b b ON a.key = b.key
WHERE a.id IS NULL OR b.id IS NULL;
Useful for reconciliation: find rows in A with no match in B and vice versa. Not common in CRUD apps.
LATERAL JOIN
-- Top 3 orders per user
SELECT u.id, u.email, o.id AS order_id, o.total
FROM users u
JOIN LATERAL (
SELECT id, total
FROM orders
WHERE user_id = u.id
ORDER BY created_at DESC
LIMIT 3
) o ON true;
LATERAL is the 2026 replacement for the "top-N per group" correlated subquery. Cleaner and often faster because the inner query is evaluated once per outer row with an index seek.
How to pick the right join
- Need rows from both tables? → INNER JOIN.
- Need all rows from one table plus optional data from another? → LEFT JOIN.
- Need to diff two tables? → FULL OUTER JOIN.
- Need top-N, nearest-N, or a correlated subquery? → LATERAL JOIN.
- Building a matrix or enumeration (small tables)? → CROSS JOIN with an explicit size limit.
Indexing rules
-- Always index the join column on the many side
CREATE INDEX idx_orders_user_id ON orders (user_id);
-- Composite index if you also filter by another column
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);
A missing index on the join column forces a sequential scan. On a 10M-row table that is the difference between 5 ms and 30 s. Run EXPLAIN (ANALYZE, BUFFERS) and look for Seq Scan on large tables.
Common mistakes
Implicit CROSS JOIN. Forgetting an ON clause in older comma-syntax: SELECT * FROM a, b WHERE ... — if the WHERE is wrong you silently get a Cartesian product.
LEFT JOIN with WHERE that kills nulls. Adding WHERE b.column = 'x' after a LEFT JOIN turns it into an INNER JOIN because NULLs fail equality checks. Move that filter into the ON clause or use WHERE b.column = 'x' OR b.column IS NULL.
Joining on unindexed columns. The planner often cannot use a hash join efficiently across very different cardinalities. Index both sides when in doubt.
Selecting SELECT * across joined tables. You fetch duplicate columns and waste bandwidth. Always list the columns you need.
Misunderstanding NULL in FULL OUTER. NULL = NULL is FALSE in SQL. Use IS NOT DISTINCT FROM or COALESCE for nullable join keys.
What to skip
- RIGHT JOIN — just swap the table order and use LEFT. Right joins confuse readers for no benefit.
- Multi-level nested LATERAL in a single OLTP query — break it into a CTE or application-side call if it goes beyond two levels.
- Joining in application code across two separate DB calls when a single SQL join is available. Network round-trips are expensive; let the database do the work.
FAQ
What is the difference between ON and WHERE in a join?
In INNER JOINs they are equivalent. In LEFT/RIGHT JOINs, filtering in WHERE happens after the join (eliminating NULLed rows); filtering in ON happens during the join (keeping them). This distinction is a common source of subtle bugs.
Does join order matter for performance?
Modern planners reorder joins automatically up to ~8 tables. Beyond that, you may need to guide the planner with explicit CTEs or SET join_collapse_limit.
When should I use a subquery instead of a join?
When the subquery is used for filtering only (WHERE id IN (SELECT ...)) and the optimizer rewrites it as a semi-join. For data retrieval, an explicit JOIN with LATERAL is almost always clearer.
Are there join types specific to Postgres?
LATERAL is standard SQL but Postgres implements it well. Postgres also supports JOIN ... USING (column) as shorthand for ON a.column = b.column.
Where to go next