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
, orUPDATE
statement, or other CTEs in the sameWITH
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;