How to use Common Table Expressions CTEs

Short answer

A Common Table Expression (CTE) is a named temporary result set defined with WITH that can be referenced within a subsequent SELECT, INSERT, UPDATE, or DELETE.

Steps

1. Simple CTE: WITH active_users AS (SELECT * FROM users WHERE active = 1) SELECT * FROM active_users;
2. Multiple CTEs: WITH a AS (...), b AS (...) SELECT ...
3. Recursive CTE for hierarchies: WITH RECURSIVE subordinates AS (...) SELECT * FROM subordinates;
4. Reference a CTE multiple times in the main query.
5. Use CTEs to break complex logic into named, testable steps.

Tips

• CTEs improve readability but may not always improve performance compared to derived tables.
• Recursive CTEs require a base case and a recursive case to avoid infinite loops.

Common issues

• CTEs are not indexed; materializing large CTEs into temp tables can help performance.
• Some databases optimize CTEs differently; always verify execution plans for critical queries.r