📋 Subqueries Inside Subqueries Are Unreadable
CTEs (WITH clause) name your subqueries. Step-by-step logic, easy to debug, self-documenting.
❌ Without CTE
SELECT name, total_orders FROM ( SELECT u.name, COUNT(o.id) as total_orders FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name ) order_stats WHERE total_orders > 5;
✅ With CTE
WITH order_stats AS ( SELECT u.name, COUNT(o.id) as total_orders FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name ) SELECT name, total_orders FROM order_stats WHERE total_orders > 5;
🎯 Multiple CTEs
WITH recent_users AS ( SELECT * FROM users WHERE created_at > '2024-01-01' ), active_orders AS ( SELECT * FROM orders WHERE status = 'completed' ), user_totals AS ( SELECT u.id, u.name, SUM(o.total) as revenue FROM recent_users u JOIN active_orders o ON u.id = o.user_id GROUP BY u.id, u.name ) SELECT name, revenue FROM user_totals WHERE revenue > 1000;
💡 Benefits
- Readable (named steps)
- Maintainable (modify one CTE at a time)
- Reusable (reference same CTE multiple times)
- Self-documenting code
“Nested subqueries gave me headache. CTEs saved me. Now queries read like stories. CTEs are essential for complex SQL.”
