This topic describes how to use the WITH clause in a SELECT statement.

You can use the WITH clause to define subqueries by using common table expressions (CTEs) in a SELECT statement. The subqueries defined in the WITH clause can be referenced in the SELECT statement. The WITH clause can be used to flatten nested queries or simplify subqueries. Each subquery is performed only once in the SELECT statement. This improves the query performance.

Note
  • A CTE is a named temporary result set, and is valid only in a single SQL statement such as a SELECT, INSERT, or DELETE statement.
  • A CTE is valid only during the period when the SQL statement is executing.

Precautions

  • A CTE can be followed by an SQL statement such as a SELECT, INSERT, or UPDATE statement, or other CTEs in the same WITH clause. Separate multiple CTEs with commas (,). Otherwise, the CTEs will not take effect.
  • The paging feature is not supported in a CTE.

Use the WITH clause

  • The following queries are equivalent: 
    SELECT a, b
FROM (SELECT a, MAX(b) AS b FROM t GROUP BY a) AS x;                   
    WITH x AS (SELECT a, MAX(b) AS b FROM t GROUP BY a)
SELECT a, b FROM x;
  • The WITH clause can be used to define multiple subqueries. 
    WITH
t1 AS (SELECT a, MAX(b) AS b FROM x GROUP BY a),
t2 AS (SELECT a, AVG(d) AS d FROM y GROUP BY a)
SELECT t1.*, t2. *
FROM t1 JOIN t2 ON t1.a = t2.a;
  • After a subquery is defined, other subqueries following this subquery in the same WITH clause can reference this subquery. 
    WITH
x AS (SELECT a FROM t),
y AS (SELECT a AS b FROM x),
z AS (SELECT b AS c FROM y)
SELECT c FROM z;