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...