CTEs (Common Table Expressions) and subqueries solve similar problems but with different advantages.

Subquery Example

SELECT u.name,
       (SELECT COUNT(*) FROM orders WHERE user_id = u.id) as order_count
FROM users u
WHERE (SELECT COUNT(*) FROM orders WHERE user_id = u.id) > 5;

Problem: Same subquery runs twice. Hard to read and maintain.

CTE Example

WITH order_counts AS (
  SELECT user_id, COUNT(*) as order_count
  FROM orders
  GROUP BY user_id
)
SELECT u.name, oc.order_count
FROM users u
JOIN order_counts oc ON u.id = oc.user_id
WHERE oc.order_count > 5;

Benefits: Named, reusable, easier to debug.

When to Use CTEs

• ✅ Complex queries with multiple steps
• ✅ Need to reference the same subquery multiple times
• ✅ Recursive queries (CTEs only feature)
• ✅ Improving query readability for your team

When to Use Subqueries

• ✅ Simple, one-time calculations
• ✅ Single column/value results
• ✅ Correlated subqueries (row-by-row evaluation)

Performance Note

Both are usually optimized to the same execution plan. Choose based on readability, not performance.