Updated on 2024-12-13 GMT+08:00

WITH

The WITH clause defines the naming relationship of query clauses, which can flatten nested queries or simplify subquery statements.

For example, the following query statements are equivalent:

SELECT name, maxprice FROM (SELECT name, MAX(price) AS maxprice FROM fruit GROUP BY name) AS x;

WITH x AS (SELECT name, MAX(price) AS maxprice FROM fruit GROUP BY name) SELECT name, maxprice FROM x;
  • Multiple Subqueries
    with 
    t1 as(select name,max(price) as maxprice from fruit group by name),
    t2 as(select name,avg(price) as avgprice from fruit group by name)
    select t1.*,t2.* from t1 join t2 on t1.name = t2.name;
  • WITH Chain Form
    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;