Where common table expressions are permitted

Common table expression definitions are permitted in only three places, although they may be referenced throughout the body of the query or in any subqueries.

  • Top-level SELECT statement   Common table expressions are permitted within top-level SELECT statements, but not within subqueries.
    WITH DepartmentPayroll( DepartmentID, amount ) AS
      ( SELECT DepartmentID, SUM( Salary ) AS amount
        FROM Employees GROUP BY DepartmentID )
    SELECT DepartmentID, amount
    FROM DepartmentPayroll
    WHERE amount = ( SELECT MAX( amount )
                  FROM DepartmentPayroll );

  • The top-level SELECT statement in a view definition   Common table expressions are permitted within the top-level SELECT statement that defines a view, but not within subqueries within the definition.


    CREATE VIEW LargestDept ( DepartmentID, Size, pay ) AS
       WITH
         CountEmployees( DepartmentID, n ) AS
           ( SELECT DepartmentID, COUNT( * ) AS n
             FROM Employees GROUP BY DepartmentID ),
         DepartmentPayroll( DepartmentID, amount ) AS
           ( SELECT DepartmentID, SUM( Salary ) AS amount
             FROM Employees GROUP BY DepartmentID )
      SELECT count.DepartmentID, count.n, pay.amount
      FROM CountEmployees count JOIN DepartmentPayroll pay
      ON count.DepartmentID = pay.DepartmentID
      WHERE count.n = ( SELECT MAX( n ) FROM CountEmployees )
         OR pay.amount = ( SELECT MAX( amount ) FROM DepartmentPayroll );

  • A top-level SELECT statement in an INSERT statement   Common table expressions are permitted within a top-level SELECT statement in an INSERT statement, but not within subqueries within the INSERT statement.


    CREATE TABLE LargestPayrolls ( DepartmentID INTEGER, Payroll NUMERIC, CurrentDate DATE );
    INSERT INTO LargestPayrolls( DepartmentID, Payroll, CurrentDate )
      WITH DepartmentPayroll( DepartmentID, amount ) AS
        ( SELECT DepartmentID, SUM( Salary ) AS amount
          FROM Employees
          GROUP BY DepartmentID )
      SELECT DepartmentID, amount, CURRENT TIMESTAMP
      FROM DepartmentPayroll
      WHERE amount = ( SELECT MAX( amount )
                    FROM DepartmentPayroll );

 See also