Common table expressions

Although SQL is a declarative language, it provides a way of implementing the logic of sequential execution of code or of reusing code.

Common table expressions (CTE) is a feature that makes it possible to define a subquery once, give it a name, and then use it at several places in the main query.

The simplified syntax diagram for CTE is as follows:

WITH <subquery name> AS (<subquery code>) [, ...]
SELECT <Select list> FROM <subquery name>;

In the preceding syntax diagram, subquery code is a query whose results will be used later in the primary query as if it was a real table. The subquery in parentheses after the AS keyword is a common table expression. It can also be called a substatement or an auxiliary statement. The query after the WITH block is the primary or main query. The whole statement itself is called a WITH query.

It is possible to use not only the SELECT statements in a CTE, but also the INSERT, UPDATE, or DELETE statements. It is...