Advanced query rewriting techniques transform complex queries into semantically equivalent but faster forms. Unlike basic patterns (OR to UNION, NOT IN to LEFT JOIN), these techniques handle complex scenarios: nested subqueries, unnecessary joins, expression simplification, and execution plan control.

Think of it like refactoring code - same output, better performance. The key: preserve correctness while improving speed.

**1. Predicate Pushdown**

Move WHERE conditions as close to data source as possible.

Slow (filter after join):

SELECT * FROM (

SELECT * FROM orders o

JOIN customers c ON o.customer_id = c.id

) t

WHERE t.status = 'active';

-- Joins all orders (10M), then filters

-- Time: 45 seconds

Fast (filter before join):

SELECT * FROM (

SELECT * FROM orders WHERE status = 'active'

) o

JOIN customers c ON o.customer_id = c.id;

-- Filters to 1M active orders, then joins

-- Time: 5 seconds (9x faster)

Rule: Push filters to innermost subquery.

**2. Join Elimination**

Remove joins that don't contribute to result.

Slow (unnecessary join):

SELECT o.order_id, o.total

FROM orders o

JOIN customers c ON o.customer_id = c.id

WHERE o.status = 'completed';

-- Joins with customers but doesn't use customer data

-- Time: 15 seconds

Fast (no join needed):

SELECT order_id, total

FROM orders

WHERE status = 'completed';

-- No join, just filter

-- Time: 2 seconds (7x faster)

Rule: Remove joins if no columns used and no filtering on joined table.

**3. Subquery Flattening**

Convert nested subqueries to joins.

Slow (nested subqueries):

SELECT * FROM orders