Joins are the operation that makes relational databases relational. Without them, normalized data is just isolated tables. A solid understanding of join semantics — what rows survive, what becomes NULL, how the query planner executes them — is the difference between writing queries that get the right answer and queries that silently return incorrect results.
What changed in 2026
- Postgres 17 introduced lateral join improvements and better parallel query planning for large join trees.
- Query planners in 2026 are excellent — they will switch between nested-loop, hash, and merge joins based on statistics. Your job is to ensure the statistics are up to date (
ANALYZE) and indexes exist.
- ORMs like Drizzle and Prisma generate joins from relation definitions, but knowing the SQL is essential when generated queries are slow or incorrect.
- DuckDB handles analytical join-heavy queries in-process and is widely used for ad-hoc analysis against large datasets in 2026.
The core join types
INNER JOIN — only matching rows
SELECT users.name, orders.total_cents
FROM users
INNER JOIN orders ON orders.user_id = users.id;
-- Returns only users who have at least one order
LEFT JOIN — all left rows, NULLs for no match
SELECT users.name, orders.total_cents
FROM users
LEFT JOIN orders ON orders.user_id = users.id;
-- Returns ALL users; orders columns are NULL for users with no orders
RIGHT JOIN — all right rows (rarely used)
SELECT users.name, orders.total_cents
FROM users
RIGHT JOIN orders ON orders.user_id = users.id;
-- Returns ALL orders; user columns are NULL for orphaned orders
-- Usually rewritten as a LEFT JOIN with tables swapped
FULL OUTER JOIN — all rows from both tables
SELECT users.name, orders.total_cents
FROM users
FULL OUTER JOIN orders ON orders.user_id = users.id;
-- Returns all users and all orders; NULLs where no match exists on either side
CROSS JOIN — every combination
SELECT colors.name, sizes.label
FROM colors
CROSS JOIN sizes;
-- Returns M × N rows — every color paired with every size
-- Use deliberately; accidental cross joins on large tables are catastrophic
Join types at a glance
| Join type |
Rows returned |
| INNER JOIN |
Only rows with a match in both tables |
| LEFT JOIN |
All left rows + matched right rows (NULLs where no match) |
| RIGHT JOIN |
All right rows + matched left rows (NULLs where no match) |
| FULL OUTER JOIN |
All rows from both tables (NULLs where no match on either side) |
| CROSS JOIN |
Cartesian product — every row from left × every row from right |
| SELF JOIN |
A table joined to itself (using aliases) |
LEFT JOIN for "rows with no related record"
-- Find users who have never placed an order
SELECT users.id, users.email
FROM users
LEFT JOIN orders ON orders.user_id = users.id
WHERE orders.id IS NULL;
The WHERE … IS NULL filter on the right table's column is the idiomatic way to find unmatched rows.
Self-join — comparing rows within the same table
-- Find employees and their managers (both in the employees table)
SELECT
emp.name AS employee,
mgr.name AS manager
FROM employees emp
LEFT JOIN employees mgr ON mgr.id = emp.manager_id;
Self-joins require aliases to distinguish the two "copies" of the table.
CTEs for readable multi-join queries
WITH active_users AS (
SELECT id, email
FROM users
WHERE is_active = true
),
recent_orders AS (
SELECT user_id, COUNT(*) AS order_count
FROM orders
WHERE placed_at > now() - INTERVAL '30 days'
GROUP BY user_id
)
SELECT
au.email,
COALESCE(ro.order_count, 0) AS orders_last_30_days
FROM active_users au
LEFT JOIN recent_orders ro ON ro.user_id = au.id
ORDER BY orders_last_30_days DESC;
CTEs name intermediate results, making complex join trees readable.
Performance — the indexing rules
-- Always index the FK column (right-side join column)
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- Composite index if you filter after the join
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Check the query plan
EXPLAIN (ANALYZE, BUFFERS)
SELECT users.name, orders.total_cents
FROM users
INNER JOIN orders ON orders.user_id = users.id
WHERE orders.status = 'paid';
Look for Seq Scan on large tables in the EXPLAIN output — that usually means a missing index.
How to pick the right join
| Question |
Join to use |
| I only want rows that exist in both tables |
INNER JOIN |
| I want all rows from the main table, optional related data |
LEFT JOIN |
| I want to find records with no related records |
LEFT JOIN + WHERE right.id IS NULL |
| I want to find differences between two sets |
FULL OUTER JOIN |
| I need to pair every row with every other row |
CROSS JOIN |
| I need to compare rows within one table |
SELF JOIN (aliased) |
Common mistakes
Joining without an index on the FK column. Postgres will use a hash join or nested loop scan on every query. Add an index on the join column for any table with more than a few thousand rows.
Forgetting the join condition. A FROM a, b (implicit join) with no WHERE condition is a CROSS JOIN. Always use explicit JOIN … ON syntax.
Using WHERE instead of ON for LEFT JOIN filters. Filtering a LEFT JOIN's right table in WHERE converts it into an INNER JOIN:
-- This behaves like INNER JOIN (wrong if you want all users)
SELECT * FROM users
LEFT JOIN orders ON orders.user_id = users.id
WHERE orders.status = 'paid'; -- eliminates NULL rows
-- Correct: filter in the ON clause
SELECT * FROM users
LEFT JOIN orders ON orders.user_id = users.id AND orders.status = 'paid';
Over-joining. Joining 10 tables in one query becomes unmaintainable. Break into CTEs or subqueries; the planner often handles them equivalently.
What to skip
- Implicit comma-separated FROM (
FROM a, b WHERE a.id = b.a_id) — works but is ambiguous and not readable for new team members.
- FULL OUTER JOIN unless your schema genuinely has two independent sets you need to reconcile — for most use cases a LEFT JOIN covers the need.
- **SELECT *** in joins — column names collide across tables. Always name columns explicitly in join queries.
FAQ
What is the difference between ON and USING in a join?
USING (column_name) is shorthand when both tables have identically named join columns. ON a.id = b.a_id is explicit and works for any column names. Prefer ON for clarity.
Can I join on multiple conditions?
Yes: ON a.id = b.a_id AND a.tenant_id = b.tenant_id. Composite join conditions are common in multi-tenant schemas.
When does a LEFT JOIN return multiple rows per left-side row?
When there are multiple matching rows in the right table. If you expect one-to-one and get a fan-out, add a DISTINCT or aggregate, or investigate whether your data has duplicates.
Is there a performance difference between INNER JOIN and LEFT JOIN?
Marginally. INNER JOIN allows the planner to filter rows earlier. But for indexed joins the difference is small; write the semantically correct join type and optimize from EXPLAIN output.
Where to go next