Common Table Expressions

Let's talk about Common Table Expressions (CTEs). A table expressions is a named query. A CTE is a query you put at the front of the statement that you can later refer back to in the rest of the SQL. You start using the keyword WITH, and then enclose your CTE SQL in parentheses. You follow that with the keyword AS, and provide an alias.

If you have multiple CTEs, you separate them with commas. Each CTE needs its own unique alias. Then in the rest of your SQL statement, you can refer back to the CTE via the alias. The results of the CTE are temporarily stored as a derived table for the duration of the query. You cannot refer to the CTE outside of this query.

CTEs help with readability. They are also helpful if there is some data that you need to grab outside of the main query where you are assembling the results. A good example of where you can benefit from a CTE is as part of a PIVOT.

There is a special kind of CTE called a recursive CTE. You need two parts for this. The first is an anchor which is stand alone. The second is one that will repeats for each level of recursion. You UNION these two pieces together. The recursion continues until there are 0 rows left.