A common table expression (CTE) is a container for a single SQL statement that will allow you to query a temporary result set. A CTE allows you to create complex queries in a more readable way. It may be beneficial to use a CTE instead of subqueries because it's easier to read and understand later. CTEs can also be used instead of creating a view. To create a view, you need elevated permissions in a database, but you won't need any additional permissions to use a CTE. Views will be covered in more detail in Chapter 12, Programmable Objects. Additionally, CTEs can help you run recursive queries.
Using common table expressions
Using non-recursive CTEs
The syntax for a single non-recursive CTE is as follows:
WITH...