Subqueries

Subquery Performance Traps

Senior Data Analyst
February 26, 2026
5 min read

The Common Traps

1. **Correlated Subqueries in SELECT**: Run once per row. Avoid on large tables.

2. **IN with a massive list**: Can be slow. Consider temp tables or JOINs.

3. **Uncached CTEs**: Some databases re-run a CTE every time it's referenced. Check your database's behavior.

The Fix

  • Use `EXPLAIN ANALYZE` to see actual performance.
  • Rewrite correlated subqueries as JOINs or Window Functions.
  • Materialize CTEs into temp tables for very large datasets.

    • *Day 58: LATERAL Joins (The Hidden Feature).*

    Ready to put your knowledge into practice?

    Join SQL Mastery and learn through interactive exercises.