WITH

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.

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;