Join optimization is about making multi-table queries fast. When you join tables, the database picks one of three algorithms: Nested Loop (loops through rows), Hash Join (builds hash table), or Merge Join (merges sorted data). Your job: create the right indexes, filter early, and let the optimizer choose the best algorithm.

Think of it like organizing a meeting between two groups. Bad approach: check every person in group A against every person in group B (nested loop without index). Good approach: create a directory (index), filter who needs to attend (WHERE clause), then match efficiently.

**Three Join Algorithms:**

1. **Nested Loop Join** - Loops through outer table, looks up each row in inner table

- Best for: Small outer table (<1000 rows), indexed inner table

- Speed: O(n * m) without index, O(n * log m) with index

- Example: 100 orders joining 1M customers with index on customer_id

- When used: Small datasets, indexed lookups, OLTP queries

2. **Hash Join** - Builds hash table from smaller table, probes with larger table

- Best for: Large tables, equality joins, no indexes

- Speed: O(n + m) - linear time

- Memory: Needs RAM for hash table (smaller table)

- Example: 1M orders joining 500K customers on customer_id

- When used: Analytics, data warehouses, large batch queries

3. **Merge Join** - Merges two sorted datasets

- Best for: Both tables sorted/indexed on join column

- Speed: O(n + m) - linear time

- Requirement: Data must be sorted

- Example: Orders and payments both indexed on order_id

- When used: Range joins, sorted data, indexed columns

**Join Order Matters:**

Database optimizer picks join order to minimize intermediate results.

Bad order:

orders (1M rows) → order_items (5M rows) → products (100K rows)

Intermediate: 5M rows after first join

Good order:

orders (1M rows) → products (100K rows) → order_items (5M rows)

Intermediate: 1M rows after first join (filtered by WHERE)

**Filter Early (Critical!):**

Apply WHERE filters BEFORE joining to reduce data volume.

Bad (filter after join):

SELECT * FROM orders o

JOIN customers c ON o.customer_id = c.id

WHERE o.order_date > '2024-01-01' -- Filters 5M joined rows

Good (filter before join):

SELECT * FROM orders o

JOIN customers c ON o.customer_id = c.id

WHERE o.order_date > '2024-01-01' -- Optimizer filters orders first (500K rows), then joins

Result: 10x faster by joining 500K rows instead of 5M.

**Index Strategy for Joins:**