Common Table Expressions (WITH)

Common Table Expressions (WITH)#

A common table expression (CTE) is a named, temporary result set defined with a leading WITH clause that you reference like a table in the statement that follows. Querona supports non-recursive CTEs on SELECT statements and inside view definitions.

A CTE is often clearer than a repeated derived table: you name a subquery once and reference it by name, including more than once and from a later CTE in the same WITH list.

Syntax#

[ WITH [ XMLNAMESPACES ( <xmlnamespace_item> [ ,...n ] ) , ]
    <cte_name> [ ( <column_name> [ ,...n ] ) ] AS ( <cte_query> )
    [ , <cte_name> [ ( <column_name> [ ,...n ] ) ] AS ( <cte_query> ) [ ,...n ] ]
]
<select_statement>

Arguments#

cte_name

The name used to reference the CTE in the trailing statement’s FROM clause. Each name in the WITH list must be unique. A CTE name shadows a real table or view of the same name within the statement’s scope.

( column_name [ ,…n ] )

An optional column-alias list. When omitted, every column the body projects must already have a name — an explicit alias or a plain column reference. When provided, the number of names must match the number of columns the body projects, and the names must be unique (case-insensitively).

cte_query

The SELECT statement that defines the CTE’s result set. ORDER BY is allowed in the body only when TOP, OFFSET or a FOR clause is also present — the same rule that applies to views and derived tables.

Remarks#

The WITH clause is a leading clause: it appears once, before the SELECT that uses it, and its scope is only that statement.

Multiple CTEs are comma-separated. A later CTE may reference an earlier sibling in the same list; a CTE may not reference itself (that would be recursive) or a later sibling.

A CTE reference behaves like an inline, non-materialized derived table. Each reference expands independently, so referencing a CTE more than once is equivalent to repeating its subquery. When the whole query can run on a single capable source, Querona pushes the expanded query down to that source.

A CTE may appear inside a view definition; the stored definition keeps its WITH clause and expands when the view is used.

When a WITH clause also declares XMLNAMESPACES, the XMLNAMESPACES clause comes first, followed by the CTEs, all comma-separated in one shared WITH.

For malformed definitions Querona returns the same errors as SQL Server — for example a WITH list that is never used, a body column with no name, a column-list count that does not match the body, or a duplicate CTE name.

Not currently supported

  • Recursive CTEs.

  • A WITH clause on INSERT, UPDATE, DELETE or MERGE. These are rejected with a descriptive message.

Permissions#

Requires the permissions for the statement in which the CTE is used — typically SELECT on the objects the CTE body reads.

Examples#

A. A single CTE#

WITH SalesByPerson AS (
    SELECT SalesPersonID, COUNT(*) AS OrderCount
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT SalesPersonID, OrderCount
FROM SalesByPerson
WHERE OrderCount > 100
ORDER BY OrderCount DESC ;

B. Naming the output columns#

The column-alias list renames every column the body projects.

WITH Sales (PersonID, Orders) AS (
    SELECT SalesPersonID, COUNT(*)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT PersonID, Orders
FROM Sales ;

C. Several CTEs, where a later one reads an earlier one#

WITH PerPerson AS (
    SELECT SalesPersonID, SUM(TotalDue) AS Total
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
),
AboveAverage AS (
    SELECT SalesPersonID, Total
    FROM PerPerson
    WHERE Total > (SELECT AVG(Total) FROM PerPerson)
)
SELECT SalesPersonID, Total
FROM AboveAverage
ORDER BY Total DESC ;

D. Referencing a CTE more than once#

Each reference expands independently, so a CTE can be joined to itself.

WITH DailyTotals AS (
    SELECT CAST(OrderDate AS date) AS OrderDay, SUM(TotalDue) AS Total
    FROM Sales.SalesOrderHeader
    GROUP BY CAST(OrderDate AS date)
)
SELECT today.OrderDay, today.Total, today.Total - yesterday.Total AS Change
FROM DailyTotals AS today
JOIN DailyTotals AS yesterday
  ON yesterday.OrderDay = DATEADD(day, -1, today.OrderDay)
ORDER BY today.OrderDay ;

E. A CTE inside a view#

CREATE VIEW Sales.TopSalesPeople AS
WITH PerPerson AS (
    SELECT SalesPersonID, SUM(TotalDue) AS Total
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT SalesPersonID, Total
FROM PerPerson ;

See Also#