CTE is the abbreviation for Common Table Expression. A CTE is an expression that can be thought of as a temporary result set that is defined within the execution of a single SQL statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.

A CTE can reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained. A recursive CTE contains 3 elements...

• An initializer – this is like the data to start
• Recursive part – this is where the initializer feeds the data for recursion
• Termination – defines when the recursion must end