Common Table Expressions

Common Table Expressions (or CTE's) are wonderful little devices, they allow for a derived table to be declared above a query and referenced by the main select. One advantage of using a common table expression is that they can be created to be recursive (self-referencing) which can allow for a lot of cool things!

CTE's are inline, in the sense that they are only valid for the query in which they are defined, if you've got two different queries to run which would make use of a declared CTE it may be worth looking at table variables, temporary tables and other options down that line of things.

Syntax

A cte can be declared in the following way:

with cte as (select 1 [val1], 'One' [val2])
select *
from cte

Obviously this wont' actually do very much as we're not pulling from a table and there are only two values available to the cte but it proves the syntax is working as we'd hope. As we're using cte's in this way and referring to them as tables there's no reason we couldn't pull information from one cte into another. Here's an example of one being pulled into another cte, having some processing done on the data pulled from there and then the final select using only the second cte:

with cte as (select 1 [val1], 'One' [val2])
, cte2 as (select cte.val1, cte.val2, cte.val1+1 [Next] from cte)
select *
from cte2

This demonstrates that cte's are to be used as tables and as such will need to be referred to in the from statement of any query that wants to use them (i.e. you can't simply refer to the first cte in the from clause as you can for derived tables). Once that reference is there you can use a cte in any way that you would an ordinary table.