A Named Temporary Result
A common table expression or CTE is a named query you define with the WITH keyword and then reference in the main statement. It acts like a temporary view that lives only for that single query.
Why People Use Them
- They break a long nested query into clear named steps.
- The same CTE can be referenced more than once in the outer query.
- They read top to bottom, which is easier to follow than deeply nested subqueries.
How It Looks
You write WITH a name AS the inner query, then SELECT from that name as if it were a table. You can chain several CTEs separated by commas, where later ones may use earlier ones.
A Caution
A CTE is mostly a readability tool. In many engines it does not change the query plan, though some older systems treated it as an optimization fence that blocked pushing filters inside.
Key idea
A CTE names a subquery with WITH so a complex query reads as clear ordered steps instead of tangled nesting.