Joins

Overview

Join: Combine rows from two tables on a condition — the core of relational querying
Types: INNER, LEFT/RIGHT/FULL OUTER, CROSS, self join, plus the patterns semi-join (EXISTS) and anti-join (NOT EXISTS)
Postgres Extras: LATERAL joins, USING, NATURAL (avoid), FULL JOIN support (MySQL lacks it)
Physical Algorithms: Nested Loop, Hash Join, Merge Join — chosen by the planner, see Indexes and Query Performance and Query Planner and Optimiser
Sample Data: E-commerce Practice Queries

Join Types

Visual Summary

A = users, B = orders (joined on users.id = orders.user_id)
 
INNER JOIN        → only matching pairs                    (users with orders)
LEFT JOIN         → all of A + matches, NULLs if none     (every user, orders or not)
RIGHT JOIN        → all of B + matches                     (rarely used; flip to LEFT)
FULL OUTER JOIN   → all of A and all of B                  (reconciliation)
CROSS JOIN        → every A × every B                      (combinations, calendars)
SEMI JOIN         → A rows that HAVE a match, no dup rows  (WHERE EXISTS)
ANTI JOIN         → A rows that have NO match              (WHERE NOT EXISTS)

Join	Unmatched Left Rows	Unmatched Right Rows	Can Duplicate Left Rows
INNER	Dropped	Dropped	Yes (one per match)
LEFT	Kept (NULLs on right)	Dropped	Yes
RIGHT	Dropped	Kept	Yes
FULL	Kept	Kept	Yes
CROSS	n/a	n/a	Yes (m × n rows)
Semi (EXISTS)	Dropped	n/a	No
Anti (NOT EXISTS)	Only these kept	n/a	No

INNER JOIN

SELECT o.id AS order_id, u.full_name, o.status
FROM orders o
JOIN users u ON u.id = o.user_id          -- JOIN = INNER JOIN
WHERE o.status = 'shipped'
ORDER BY o.id;

LEFT JOIN

-- Every user with their order count (0 for none)
SELECT u.full_name, count(o.id) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.full_name
ORDER BY orders DESC, u.full_name;

ON vs WHERE in an Outer Join

Condition in ON: Filters the right table before matching — left rows are still kept
Condition in WHERE: Filters the result — a WHERE right.col = … turns the LEFT JOIN into an INNER JOIN
Rule of Thumb: Conditions on the optional (right) table go in ON

-- Users with their DELIVERED order count — keeps users with zero
SELECT u.full_name, count(o.id) AS delivered_orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'delivered'
GROUP BY u.id, u.full_name
ORDER BY u.id;                                   -- 8 rows
 
-- Same filter in WHERE silently drops Gina and Hiro (and anyone without a delivered order)
SELECT u.full_name, count(o.id) AS delivered_orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'delivered'
GROUP BY u.id, u.full_name
ORDER BY u.id;                                   -- 6 rows

FULL OUTER JOIN

-- Reconcile orders vs successful payments: show mismatches from both sides
SELECT COALESCE(o.id, p.order_id) AS order_id, o.status, p.amount
FROM (SELECT * FROM orders WHERE status IN ('paid','shipped','delivered')) o
FULL JOIN (SELECT order_id, sum(amount) AS amount FROM payments
           WHERE status = 'succeeded' GROUP BY order_id) p
       ON p.order_id = o.id
WHERE o.id IS NULL OR p.order_id IS NULL;        -- empty = books are balanced

CROSS JOIN

-- Every user × every month → a grid to LEFT JOIN facts onto (zero-filled report)
SELECT u.full_name, m.month::date, count(o.id) AS orders
FROM users u
CROSS JOIN generate_series(date '2025-01-01', date '2025-03-01', interval '1 month') AS m(month)
LEFT JOIN orders o ON o.user_id = u.id
                  AND o.created_at >= m.month AND o.created_at < m.month + interval '1 month'
WHERE u.id <= 2
GROUP BY u.full_name, m.month
ORDER BY u.full_name, m.month;

USING and NATURAL

USING (col): Shorthand when both sides share a column name; outputs the column once
NATURAL JOIN: Joins on all same-named columns — fragile (adding created_at to both tables silently changes the join). Avoid

SELECT order_id, product_id, quantity, payments.amount
FROM order_items
JOIN payments USING (order_id)
WHERE order_id = 1;          -- note the payment amount repeats on every item row (fan-out)

Self Joins

What: Join a table to itself with two aliases
Uses: Hierarchies (employee → manager), referrals, comparing rows within a table (pairs, duplicates)

-- Employee with their manager (LEFT so the CEO appears)
SELECT e.name AS employee, m.name AS manager, e.department
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.id;
 
-- Employees who earn more than their manager
SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary;          -- none here; try UPDATE employees SET salary = 100000 WHERE name = 'Leo'
 
-- Pairs of products in the same category (a.id < b.id avoids mirror duplicates and self-pairs)
SELECT a.name, b.name, a.category_id
FROM products a
JOIN products b ON a.category_id = b.category_id AND a.id < b.id
ORDER BY a.category_id, a.name;

Deep Hierarchies: A self join goes one level per join — use a recursive CTE for arbitrary depth (Subqueries and CTEs)

Semi Joins and Anti Joins

Semi Join: EXISTS / IN

-- Users who have at least one delivered order (one row per user, no DISTINCT needed)
SELECT u.full_name
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'delivered')
ORDER BY u.full_name;
 
-- Same with IN
SELECT full_name FROM users
WHERE id IN (SELECT user_id FROM orders WHERE status = 'delivered')
ORDER BY full_name;

Why not JOIN + DISTINCT?: A join multiplies rows (Asha has 2 delivered orders), forcing a DISTINCT sort/hash. Semi-joins stop at the first match

Anti Join: NOT EXISTS vs LEFT JOIN … IS NULL vs NOT IN

-- 1. NOT EXISTS (preferred)
SELECT u.full_name FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
 
-- 2. LEFT JOIN … IS NULL (equivalent, Postgres plans the same Hash Anti Join)
SELECT u.full_name FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

The NOT IN NULL Pitfall

Problem: x NOT IN (subquery) is x <> v1 AND x <> v2 AND …. If any v is NULL, every comparison with it is unknown → the whole predicate is never true → zero rows
Also Slower: Postgres can’t turn NOT IN into an anti-join (because of those NULL semantics); it uses a hashed SubPlan or, if the subquery is too big for work_mem, a per-row scan
Fix: Always use NOT EXISTS (or ensure the column is NOT NULL)

-- "Users who never referred anyone": referred_by contains NULLs
SELECT full_name FROM users
WHERE id NOT IN (SELECT referred_by FROM users);                 -- 0 rows (!!)
 
SELECT full_name FROM users u
WHERE NOT EXISTS (SELECT 1 FROM users r WHERE r.referred_by = u.id)
ORDER BY u.id;                                                   -- 5 rows: Chen, Eva, Farid, Gina, Hiro
 
-- NOT IN works only if you strip NULLs yourself
SELECT full_name FROM users
WHERE id NOT IN (SELECT referred_by FROM users WHERE referred_by IS NOT NULL)
ORDER BY id;

Pattern	Handles NULLs	Plan	Verdict
NOT EXISTS	Correct	Hash/Merge Anti Join	Use this
LEFT JOIN … IS NULL	Correct	Anti Join	Fine
NOT IN (subquery)	Breaks on any NULL	Hashed SubPlan / slow	Avoid
EXCEPT	Treats NULLs as equal	Set operation	Good for whole-row diffs

LATERAL Joins

Concept

What: A subquery in FROM that can reference columns of tables to its left — like a for each row loop
Use Cases: Top-N per group, calling set-returning functions per row, reusing computed expressions
LEFT JOIN LATERAL … ON true: Keep left rows even when the lateral subquery returns nothing

-- Latest 2 orders for every user (users with none still shown)
SELECT u.full_name, recent.id AS order_id, recent.created_at::date
FROM users u
LEFT JOIN LATERAL (
    SELECT o.id, o.created_at
    FROM orders o
    WHERE o.user_id = u.id
    ORDER BY o.created_at DESC
    LIMIT 2
) recent ON true
ORDER BY u.id, recent.created_at DESC;

-- Most expensive item in each order
SELECT o.id, top_item.name, top_item.unit_price
FROM orders o
CROSS JOIN LATERAL (
    SELECT p.name, oi.unit_price
    FROM order_items oi JOIN products p ON p.id = oi.product_id
    WHERE oi.order_id = o.id
    ORDER BY oi.unit_price DESC
    LIMIT 1
) top_item
ORDER BY o.id
LIMIT 5;
 
-- Expand an array column into rows per product (implicit LATERAL for functions)
SELECT p.sku, t.tag
FROM products p, unnest(p.tags) AS t(tag)
WHERE p.category_id = 4;

Top-N per Group Method	Strength	Weakness
LATERAL + LIMIT	Fast with index on (group, sort) — reads only N rows per group	Needs a driving table of groups
ROW_NUMBER() window	Portable, one pass	Sorts every row of every group
DISTINCT ON	Shortest for N = 1	N = 1 only, Postgres-only

Join Performance Notes

Index the FK Side: orders(user_id), order_items(product_id) — Postgres doesn’t do it for you
Join Algorithms: Nested Loop (small outer + indexed inner), Hash Join (large unsorted sets, equality only), Merge Join (both sides sorted)
Filter Early: Push filters into ON / WHERE of the smaller side; pre-aggregate before joining to avoid fan-out (Aggregation and GROUP BY)
Many Joins: Above join_collapse_limit (8) tables the planner stops exhaustively reordering joins

EXPLAIN
SELECT u.full_name FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Interview Questions

“LEFT JOIN but rows still disappear?”: A WHERE filter on the right table discards the NULL-extended rows — move it into ON
“NOT IN vs NOT EXISTS?”: NOT IN returns nothing if the subquery has a NULL; NOT EXISTS is NULL-safe and plans as an anti-join
“Find employees earning more than their manager”: Self join on manager_id
“Top 3 orders per customer”: LATERAL + LIMIT, or ROW_NUMBER() (Window Functions)
“Why does my count double after a join?”: One-to-many fan-out; aggregate before joining or count(DISTINCT …)

Summary

INNER / LEFT / FULL / CROSS: Choose by which unmatched rows you need to keep
ON vs WHERE: Right-table filters belong in ON for outer joins
Self Join: Same table twice with aliases — hierarchies and row comparisons
Semi / Anti Joins: EXISTS / NOT EXISTS; never NOT IN against a nullable column
LATERAL: Per-row subqueries — the Postgres tool for top-N per group and per-row function calls

Kaushik's Notes

Explorer

Joins

Overview

Join Types

Visual Summary

INNER JOIN

LEFT JOIN

ON vs WHERE in an Outer Join

FULL OUTER JOIN

CROSS JOIN

USING and NATURAL

Self Joins

Semi Joins and Anti Joins

Semi Join: EXISTS / IN

Anti Join: NOT EXISTS vs LEFT JOIN … IS NULL vs NOT IN

The NOT IN NULL Pitfall

LATERAL Joins

Concept

Join Performance Notes

Interview Questions

Summary

Graph View

Table of Contents

Backlinks