JOINs — when two tables become one

Day 13 taught you normalization: split one lying table into many honest ones. But now your user wants to see “Alice’s orders with product names” — and that data lives in three tables. JOIN is how you reassemble it. Today you’ll learn the four JOIN types, the three algorithms PostgreSQL uses under the hood, and why a missing foreign key index silently kills performance.

The problem: data is scattered

After normalization (Day 13), your e-commerce schema looks like this:

customers (id, name, email, city)
products  (id, name, price)
orders    (id, customer_id, product_id, quantity, order_date)

A user asks: “Show me Alice’s recent orders with product names and prices.” That data is spread across three tables. Without JOIN, you’d need three separate queries and application-level stitching — slow, error-prone, and race-condition-prone.

JOIN combines rows from two (or more) tables based on a related column. The related column is usually a foreign key — orders.customer_id references customers.id.

The four JOIN types

There are four JOIN types. Each decides what happens to rows that don’t match.

• INNER JOIN = intersection. Matches only. If Alice has no orders, she disappears.
• LEFT JOIN = left table + intersection. All left rows kept; unmatched right columns are NULL.
• RIGHT JOIN = right table + intersection. Same as LEFT but reversed.
• FULL OUTER JOIN = union. Everything from both tables; unmatched gaps are NULL.

The word tells you which table’s rows are “kept” when there’s no match.

INNER JOIN — matches only

SELECT c.name, o.order_date, o.quantity
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Jakarta'
ORDER BY o.order_date DESC
LIMIT 1000;

Translation: “Find customers in Jakarta who have orders. Show their name, order date, and quantity.” Customers without orders are excluded.

LEFT JOIN — keep the left table

-- Find customers who have NEVER ordered
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

This is the classic “anti-join” pattern. The WHERE o.id IS NULL filters to only rows where no order matched — customers who exist but have zero orders.

RIGHT JOIN — keep the right table

Same as LEFT JOIN but reversed — all rows from the right table, NULLs for unmatched left rows. Rarely used in practice because you can rewrite it as a LEFT JOIN by swapping table order.

FULL OUTER JOIN — keep everything

Returns all rows from both tables. Matching rows are combined; unmatched rows get NULLs on the other side. Useful for finding mismatches between two tables.

The three JOIN algorithms

You write JOIN. PostgreSQL’s planner picks how to execute it. There are three algorithms. Understanding them is the difference between “JOINs are slow” and “this specific JOIN is slow because the planner chose a nested loop without an index.”

1. Nested Loop Join

For each row in the outer table, scan the inner table for matches. Brute force. O(N × M) — if you have 100K orders and 1K customers, that’s 100M comparisons.

When the planner chooses it: when one table is small (fits in memory) or when an index exists on the join column of the inner table. With an index, the inner scan becomes an index lookup — O(N × log M) instead of O(N × M).

EXPLAIN (costs off)
SELECT c.name, o.order_date
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 42;

-- PLAN:
--   Nested Loop
--     Index Scan on customers c (id = 42)
--     Bitmap Index Scan on idx_orders_customer (customer_id = 42)

Without the FK index, the inner scan is a Seq Scan — PostgreSQL reads every order row to find matches. With the index, it’s a Bitmap Index Scan — it jumps directly to the matching rows.

2. Hash Join

PostgreSQL reads the smaller table, builds a hash table in memory, then scans the larger table and probes the hash table for matches. O(N + M) — linear in total rows.

When the planner chooses it: when both tables are large, there’s no selective index, and the smaller table fits in memory. This is the default for analytical queries.

EXPLAIN (costs off)
SELECT c.name, o.order_date
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Jakarta';

-- PLAN:
--   Hash Join
--     Hash Cond: (o.customer_id = c.id)
--     Seq Scan on orders o
--     Hash
--       Seq Scan on customers c (city = 'Jakarta')

PostgreSQL hashes the filtered customers (Jakarta) into memory, then scans all 100K orders and probes the hash. No index needed — the hash table makes lookups O(1).

3. Merge Join

Both tables are sorted on the join column, then merged like two sorted lists. O(N + M) but requires pre-sorted input. If an index exists on the join column (B-tree indexes are sorted), PostgreSQL can scan the index in order — no explicit sort needed.

When the planner chooses it: when both inputs are already sorted (via index or prior sort). Common when joining on primary keys with indexed foreign keys. If no sorted input is available, PostgreSQL must sort both tables first — which adds overhead.

Here’s an illustrative plan (not from the benchmark — constructed to show the pattern):

The key insight: you don’t choose the algorithm — the planner does. But you control the indexes. An index on the foreign key column lets the planner use Nested Loop (index probe) or Merge Join (sorted scan). Without it, the planner falls back to Hash Join — fine for analytics, slow for point lookups.

The silent killer: missing foreign key indexes

Here’s the scenario that catches every developer: you add a foreign key constraint, but PostgreSQL does not automatically create an index on the FK column. The constraint enforces referential integrity (you can’t insert an order with customer_id that doesn’t exist). But it does nothing for query performance.

-- This enforces integrity, NOT performance:
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers(id);

-- PostgreSQL does NOT auto-create an index on customer_id.
-- You must do it yourself:
CREATE INDEX idx_orders_customer ON orders(customer_id);

Without that index, every “show me Alice’s orders” query scans the entire orders table. On 100K rows it’s annoying. On 10M rows it’s production-down.

Real benchmark: the FK index difference

I ran a benchmark on PostgreSQL 16 (Alpine, in a Podman container) with 100,000 orders, 1,000 customers, and 1,000 products. 30 iterations per query, reporting the median.

Setup: orders: 100,000 rows (random customer_id 1-1000, product_id 1-1000). customers: 1,000 rows (10% in Jakarta). products: 1,000 rows (price $10-$1000). All queries run 30×, median reported.

Query 1: Single customer’s orders (point lookup)

SELECT c.name, o.order_date, o.quantity
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 42
ORDER BY o.order_date DESC;

Without the FK index: the planner does a Nested Loop — index scan on customers (find customer 42), then Seq Scan on orders (read all 100K rows to find the 99 that match). Cost: 1,896. Median: 7.22ms.

With the FK index: the same Nested Loop, but the inner scan becomes a Bitmap Index Scan — it jumps directly to the 99 matching rows. Cost drops to 282. Median: 2.05ms — 3.5× faster.

Query 2: Three-way JOIN (orders + customers + products)

SELECT c.name, p.name AS product, o.quantity, o.order_date
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id
WHERE c.id = 42
ORDER BY o.order_date DESC;

With both FK indexes: median 2.87ms. The planner uses a Nested Loop for customer → orders (index probe), then a Hash Join for orders → products (products table is small enough to hash).

Query 3: Aggregate JOIN (top 5 customers by order count)

SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY order_count DESC
LIMIT 5;

Median: 24.43ms. The planner uses a Hash Join — it must read all 100K orders to count them. No index helps here because the query touches every row. This is an analytical query, and Hash Join is the right choice.

Query 4: LEFT JOIN anti-pattern (customers who never ordered)

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Median: 12.84ms. The planner internally rewrites this as a Hash Right Join — it’s cheaper to hash the orders table and probe against customers than vice versa. The word “Right” in the plan means PostgreSQL flipped the tables internally; your query semantics are unchanged. On a 10M-row orders table this would be seconds, not milliseconds.

Benchmark summary (30 runs, median)

Query	Without FK index	With FK index	Speedup
Point lookup (1 customer)	7.22ms	2.05ms	3.5×
Three-way JOIN	—	2.87ms	—
Aggregate (top 5)	—	24.43ms	—
LEFT JOIN (anti-join)	—	12.84ms	—

How to debug a slow JOIN

Use EXPLAIN (ANALYZE, BUFFERS) — Day 3’s tool. Look for:

1. Seq Scan on a large table where you expect an index scan. This means a missing index or a non-selective filter. Add an index on the join column (foreign key).
2. Nested Loop with huge cost on a large join. The planner thinks one side is small but it isn’t. Check if your WHERE clause is selective enough, or if statistics are stale (ANALYZE the table).
3. Hash Join with Hash Batches: N in the output. This means the hash table spilled to disk — the smaller table didn’t fit in work_mem. Either increase work_mem or filter the input more.
4. Sort node before a Merge Join with high cost. The planner had to sort explicitly because no index provided sorted input. An index on the join column could eliminate the sort.

-- The debugging query:
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.name, o.order_date, o.quantity
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Jakarta'
ORDER BY o.order_date DESC
LIMIT 100;

-- Look for:
-- "Seq Scan on orders o" → missing FK index
-- "actual time=... rows=100000" → scanning everything
-- "Sort Method: external merge Disk: 4096kB" → work_mem too low

When JOINs hurt — and what to do

1. The N+1 problem

Your ORM runs 1 query to fetch 100 customers, then 100 separate queries to fetch each customer’s orders. That’s 101 queries instead of 1 JOIN. The fix: use JOIN or WITH (CTE) to fetch everything in one query.

-- BAD: N+1 (ORM does this behind your back)
-- Query 1:  SELECT * FROM customers WHERE city = 'Jakarta';
-- Query 2:  SELECT * FROM orders WHERE customer_id = 1;
-- Query 3:  SELECT * FROM orders WHERE customer_id = 2;
-- ... (100 more)

-- GOOD: one JOIN
SELECT c.name, o.order_date, o.quantity
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Jakarta';

2. Joining too many tables

Every additional JOIN multiplies the planner’s search space. PostgreSQL uses exhaustive search for up to geqo_threshold tables (default 12). Beyond that, it switches to GEQO (Genetic Query Optimization) — a heuristic that finds good-but-not-optimal plans quickly. If you’re joining 10+ tables, consider materialized views or precomputed summary tables (denormalization — Day 13’s tradeoff).

3. Cartesian explosions

If you JOIN without a condition (or with a non-unique condition), you get a Cartesian product — every row paired with every row. This is called CROSS JOIN — and it’s the only JOIN type that doesn’t use an ON clause. 1K customers × 100K orders = 100M rows. Always verify your join condition is selective.

-- DANGER: missing join condition → Cartesian product
SELECT c.name, p.name
FROM customers c
JOIN products p;  -- no ON clause!

-- 1,000 × 1,000 = 1,000,000 rows. Oops.

The connection: JOINs tie everything together

Here’s how JOINs connect to every database lesson so far:

• Day 1 (Indexes): A foreign key index is just a regular index on the FK column. Same B-tree, same seek-vs-scan tradeoff. Without it, JOINs scan.
• Day 3 (EXPLAIN): EXPLAIN (ANALYZE) shows which JOIN algorithm the planner chose and why. Seq Scan on the inner table = missing index.
• Day 6 (Composite Indexes): If you filter by city AND join on customer_id, a composite index on (city, customer_id) serves both — leftmost prefix covers the filter, second column covers the join.
• Day 8 (Connection Pooling): The N+1 problem — 101 queries instead of 1 JOIN — doesn’t just waste time, it wastes pool connections. 100 customers × 1 connection each = pool exhaustion.
• Day 10 (Transactions): JOINs inside transactions get consistent snapshots. A JOIN that reads customers + orders in SERIALIZABLE mode won’t see partial writes.
• Day 13 (Normalization): Normalization created the need for JOINs. The cost of data integrity is reassembly. The cost of reassembly is JOIN overhead. The cost of JOIN overhead is mitigated by indexes.

Normalization split data apart. Indexes make JOINs fast. EXPLAIN shows which algorithm runs.

Transfer questions

1. You have a query that JOINs orders (10M rows) to customers (100K rows) filtering on customers.city = 'Jakarta' (1K matching customers). The planner chooses a Hash Join and it takes 8 seconds. What index would you add, and which algorithm would the planner likely switch to?

2. Your ORM generates SELECT * FROM orders WHERE customer_id = ? for each of 500 customers. That’s 500 round trips. Rewrite this as a single JOIN query. Then explain why the N+1 pattern is especially dangerous when using a connection pool with max_connections = 10.

Quiz