Using common table expressions

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 non-recursive CTEs

The syntax for a single non-recursive CTE is as follows:

WITH...