**Basic Join with Indexes:**

```sql

-- Create indexes on join columns (CRITICAL!)

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

CREATE INDEX idx_customers_id ON customers(id); -- Usually exists as PRIMARY KEY

-- Optimized join query

SELECT

o.order_id,

o.order_date,

c.customer_name,

c.email

FROM orders o

JOIN customers c ON o.customer_id = c.id

WHERE o.order_date > '2024-01-01' -- Filter applied before join

AND o.status = 'completed';

```

**Composite Index for Join + Filter:**

```sql

-- Index includes both join column and filter column

CREATE INDEX idx_orders_customer_date_status

ON orders(customer_id, order_date, status);

-- Query uses index for join AND filtering

SELECT o.*, c.customer_name

FROM orders o

JOIN customers c ON o.customer_id = c.id

WHERE o.order_date > '2024-01-01'

AND o.status = 'completed';

-- Index scan on orders, nested loop join to customers

```

**Covering Index for Join (Hot Queries):**

```sql

-- PostgreSQL: INCLUDE non-key columns

CREATE INDEX idx_orders_covering

ON orders(customer_id, order_date)

INCLUDE (order_id, status, total_amount);

-- Query reads only index, no table access

SELECT

o.order_id,

o.order_date,

o.status,