Non-Recursive Common Table Expressions Overview
Common Table Expressions (CTEs) are a standard SQL feature, and are essentially temporary named result sets. There are two kinds of CTEs: Non-Recursive, which this article covers; and Recursive.
Non-Recursive CTEs
The WITH keyword signifies a CTE. It is given a name, followed by a body (the main query) as follows:
CTEs are similar to derived tables. For example
A non-recursive CTE is basically a query-local VIEW. There are several advantages and caveats to them. The syntax is more readable than nested FROM (SELECT ...)
.
A CTE can refer to another and it can be referenced from multiple places.
A CTE referencing Another CTE
Using this format makes for a more readable SQL than a nested FROM(SELECT ...)
clause. Below is an example of this:
Multiple Uses of a CTE
This can be an 'anti-self join', for example:
Or, for year-over-year comparisons, for example:
Another use is to compare individuals against their group. Below is an example of how this might be executed:
This page is licensed: CC BY-SA / Gnu FDL
Last updated
Was this helpful?