**LEFT JOIN Performance:**

1. **Processes All Left Table Rows:**

LEFT JOIN must process every left table row:

- Cannot skip rows (all must appear in result)

- Slower than INNER JOIN which can skip non-matches

Example: 1M customers, 100K have orders

- INNER JOIN: Processes 100K customers (those with orders)

- LEFT JOIN: Processes 1M customers (all customers)

2. **Index Join Columns:**

Critical for performance:

CREATE INDEX idx_orders_customer ON orders(customer_id);

Without index: Full table scan for each left row

With index: Fast lookup for each left row

3. **Filter Left Table Early:**

Good (filter before join):

SELECT c.name, o.total

FROM customers c

LEFT JOIN orders o ON c.id = o.customer_id

WHERE c.status = 'active'; -- Reduces left table rows

This filters customers first, then joins fewer rows.

4. **WHERE Clause on Right Table:**

Slow (converts to INNER JOIN):

SELECT *

FROM customers c

LEFT JOIN orders o ON c.id = o.customer_id

WHERE o.total > 100; -- Excludes customers without orders

Fast (filter in ON clause):

SELECT *

FROM customers c

LEFT JOIN orders o ON c.id = o.customer_id AND o.total > 100;

-- Preserves all customers

5. **COUNT with LEFT JOIN:**

Use COUNT(column) not COUNT(*):

SELECT c.name, COUNT(o.id) AS order_count

FROM customers c

LEFT JOIN orders o ON c.id = o.customer_id

GROUP BY c.id, c.name;

COUNT(o.id) excludes NULL (correct)

COUNT(*) includes NULL (wrong for LEFT JOIN)

6. **FULL OUTER JOIN Performance:**