Subqueries and CTEs enable dynamic, multi-level filtering where filter criteria come from other queries, enabling complex business logic.

**Subqueries in WHERE:**

Filter based on results from another query.

WHERE column IN (SELECT ...)

WHERE column > (SELECT AVG(...))

WHERE EXISTS (SELECT ...)

**CTEs for Filtering:**

Break complex logic into readable steps.

WITH high_value_customers AS (

SELECT customer_id FROM orders GROUP BY customer_id HAVING SUM(total) > 10000

)

SELECT * FROM customers WHERE id IN (SELECT customer_id FROM high_value_customers);

**Performance:**

- EXISTS often faster than IN

- Correlated subqueries can be slow

- CTEs improve readability, may not improve performance

- Consider JOINs for better performance

**When to Use:**

- Subqueries: Dynamic filtering, aggregates

- CTEs: Complex multi-step logic

- JOINs: Better performance for large datasets

Essential for advanced analytics and complex business logic. Used by data analysts and engineers for sophisticated data selection.

Subquery filtering powers complex business rules: find customers with above-average orders, products in top categories, users matching dynamic criteria.