When should you use a CTE instead of a subquery?

Use CTEs for complex multi-step logic, reusable subqueries, or improved readability. Use subqueries for simple one-time filters. CTEs make complex queries maintainable.

Why is EXISTS often faster than IN with subqueries?

EXISTS stops at first match (short-circuits), while IN evaluates entire subquery. EXISTS also handles NULL correctly. For large subqueries, EXISTS is significantly faster.

Find customers who placed orders above average

SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE total > (SELECT AVG(total) FROM orders));

Nested subqueries: inner calculates average, middle filters orders, outer filters customers.