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, 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 that returns results of which will be used in the primary query as if it was a real table. The subquery in parenthesis, after the keyword AS, 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 the WITH query. It is possible to use not only the SELECT statements in CTE but also the INSERT, UPDATE, or DELETE statements...