This topic describes how to use the
WITH clause in a
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.
- 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.
- A CTE can be followed by an SQL statement such as a
UPDATEstatement, or other CTEs in the same
WITHclause. 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;