A Common Table Expression (CTE) is a named query result introduced with WITH and referenced by the statement that follows. CTEs let you describe a complex query as readable steps. Recursive CTEs extend this idea to hierarchical or iterative relationships, with support and syntax details that vary by database.
Key Takeaways at a Glance:
- •Common Table Expressions (WITH clause) define named, temporary result sets within the execution scope of a single query.
- •CTEs significantly improve readability and modularity over deeply nested derived tables.
- •Multiple CTEs can be chained together sequentially, separated by commas, where later CTEs reference earlier CTEs.
- •In modern PostgreSQL (v12+) and MySQL (v8+), non-recursive CTEs are inlined into the outer query by default unless specified as MATERIALIZED.
In Simple Words (The Mental Model)
A CTE is like creating a named variable for a query result. Instead of nesting queries inside queries like Russian nesting dolls (which gives developers headaches), you write: 'WITH clean_orders AS (SELECT ...), top_customers AS (SELECT ...) SELECT * FROM top_customers'. It reads naturally from top to bottom.
Real-World Analogy
Think of cooking a complex recipe. Instead of trying to chop onions, boil pasta, grate cheese, and sear chicken all inside one single pot at the exact same moment, you prep ingredients into clean bowls first: Bowl 1 (Cooked Pasta), Bowl 2 (Chopped Veggies). Then your final step simply mixes Bowl 1 and Bowl 2 together.