DSQL, PSQL

A common table expression or CTE can be described as a virtual table or view, defined in a preamble to a main query, and going out of scope after the main query’s execution. The main query can reference any CTEs defined in the preamble as if they were regular tables or views. CTEs can be recursive, i.e. self-referencing, but they cannot be nested.

Syntax

Example

CTE Notes

  • A CTE definition can contain any legal SELECT statement, as long as it doesn’t have a “WITH…​” preamble of its own (no nesting).

  • CTEs can be referenced from anywhere in the main query.

  • Each CTE can be referenced multiple times in the main query, using different aliases if necessary.

  • When enclosed in parentheses, CTE constructs can be used as subqueries in statements, but also in UPDATEs, MERGEs etc.

  • In PSQL, CTEs are also supported in FOR loop headers:

If a CTE is declared, it must be used later: otherwise, you will get an error like this: ‘CTE “AAA” is not used in query’.

A recursive (self-referencing) CTE is a UNION which must have at least one non-recursive member, called the anchor. The non-recursive member(s) must be placed before the recursive member(s). Recursive members are linked to each other and to their non-recursive neighbour by UNION ALL operators. The unions between non-recursive members may be of any type.

Recursive CTEs require the RECURSIVE keyword to be present right after . Each recursive union member may reference itself only once, and it must do so in a FROM clause.

Execution Pattern

The execution pattern of a recursive CTE is as follows:

  • For each row evaluated, it starts executing each recursive member one by one, using the current values from the outer row as parameters.

  • If the currently executing instance of a recursive member produces no rows, execution loops back one level and gets the next row from the outer result set.

Example of recursive CTEs

The next example returns the pedigree of a horse. The main difference is that recursion occurs simultaneously in two branches of the pedigree.

Notes on recursive CTEs

  • Aggregates (DISTINCT, GROUP BY, HAVING) and aggregate functions (SUM, COUNT, etc) are not allowed in recursive union members.

  • A recursive reference cannot participate in an outer join.