ASA SQL User's Guide
  Common Table Expressions

About common table expressions

Common table expressions are temporary views that are known only within the scope of a single SELECT statement. They permit you to write queries more easily, and to write queries that could not otherwise be expressed.

Common table expressions are useful or may be necessary if a query involves multiple aggregate functions or defines a view within a stored procedure that references program variables. Common table expressions also provide a convenient means to temporarily store sets of values.

Recursive common table expressions permit you to query tables that represent hierarchical information, such as reporting relationships within a company. They can also be used to solve parts explosion problems and least distance problems.

For information about recursive queries, see Recursive common table expressions.

For example, consider the problem of determining which department has the most number of employees. The employee table in the sample database lists all the employees in a fictional company and specifies in which department each works. The following query lists the department ID codes and the total number of employees in each department.

SELECT dept_id, count(*) AS n
FROM employee
GROUP BY dept_id

This query can be used to extract the department with the most employees as follows:

SELECT dept_id, n
FROM ( SELECT dept_id, count(*) AS n
       FROM employee GROUP BY dept_id ) AS a
WHERE a.n =
  ( SELECT max(n)
    FROM ( SELECT dept_id, count(*) AS n
           FROM employee GROUP BY dept_id ) AS b )

While this statement provides the correct result, it has some disadvantages. The first disadvantage is that the repeated subquery makes this statement clumsy. The second is that this statement provides no clear link between the subqueries.

One way around these problems is to create a view, then use it to re-express the query. This approach avoids the problems mentioned above.

CREATE VIEW CountEmployees(dept_id, n) AS
    SELECT dept_id, count(*) AS n
    FROM employee GROUP BY dept_id;

SELECT dept_id, n
FROM CountEmployees
WHERE n = ( SELECT max(n)
            FROM CountEmployees );

The disadvantage of this approach is that some overhead is required, as the engine must update the system tables when creating the view. If the view will be used frequently, this approach is reasonable. However, in cases where the view is used only once within a particular SELECT statement, the preferred method is to instead use a common table expression.

Using common table expressions 

Common table expressions are defined using the WITH clause, which precedes the SELECT keyword in a SELECT statement. The content of the clause defines one or more temporary views that may then be referenced elsewhere in the statement. The syntax of this clause mimics that of the CREATE VIEW statement. Using common table expressions, you can express the previous query as follows.

WITH CountEmployees(dept_id, n) AS
  ( SELECT dept_id, count(*) AS n
    FROM employee GROUP BY dept_id )
SELECT dept_id, n
FROM CountEmployees
WHERE n = ( SELECT max(n)
            FROM CountEmployees )

Instead searching for the department with the fewest employees demonstrates that such queries may return multiple rows.

WITH CountEmployees(dept_id, n) AS
  ( SELECT dept_id, count(*) AS n
    FROM employee GROUP BY dept_id )
SELECT dept_id, n
FROM CountEmployees
WHERE n = ( SELECT min(n)
            FROM CountEmployees )

In the sample database, two departments share the minimum number of employees, which is 9.

Multiple correlation names 

Just as when using tables, you can give different correlation names to multiple instances of a common table expression. Doing so permits you to join a common table expression to itself. For example, the query below produces pairs of departments that have the same number of employees, although there are only two departments with the same number of employees in the sample database.

WITH CountEmployees(dept_id, n) AS
    ( SELECT dept_id, count(*) AS n
      FROM employee GROUP BY dept_id )
SELECT a.dept_id, a.n, b.dept_id, b.n
FROM CountEmployees AS a JOIN CountEmployees AS b
ON a.n = b.n AND a.dept_id < b.dept_id

Multiple table expressions 

A single WITH clause may define more than one common table expression. These definitions must be separated by commas. The following example lists the department that has the smallest payroll and the department that has the largest number of employees.

WITH
  CountEmployees(dept_id, n) AS
    ( SELECT dept_id, count(*) AS n
      FROM employee GROUP BY dept_id ),
  DeptPayroll( dept_id, amt ) AS
     ( SELECT dept_id, sum(salary) AS amt
       FROM employee GROUP BY dept_id )
SELECT count.dept_id, count.n, pay.amt
FROM CountEmployees AS count JOIN DeptPayroll AS pay
ON count.dept_id = pay.dept_id
WHERE count.n = ( SELECT max(n) FROM CountEmployees )
   OR pay.amt = ( SELECT min(amt) FROM DeptPayroll )

Where common table expressions are permitted