MaxCompute:Common table expressions

Description

A CTE can be considered a temporary result set that is defined within the execution scope of a DML statement. Similar to a derived table, a CTE is not stored as an object. It is used only during queries. CTEs improve the readability of SQL statements and simplify complex queries.

Syntax

with 
     <cte_name> as
    (
        <cte_query>
    )
    [,<cte_name2>  as 
     (
     <cte_query2>
     )
    ,……]

• cte_name: required. The name of a CTE. The name must be unique within a WITH clause. After you define a CTE, you can use the value of this parameter to indicate the CTE in the query.
• cte_query: required. A SELECT statement. The result set of the SELECT statement is filled in the specified CTE.

Example

Sample code without CTEs:

insert overwrite table srcp partition (p='abc')
select * from (
    select a.key, b.value
    from (
        select * from src where key is not null    ) a
    join (
        select * from src2 where value > 0    ) b
    on a.key = b.key
) c
union all
select * from (
    select a.key, b.value
    from (
        select * from src where key is not null    ) a
    left outer join (
        select * from src3 where value > 0    ) b
    on a.key = b.key and b.key is not null
)d;

In the preceding code, the two JOIN clauses on both sides of UNION use the output of the same subquery statement as their left tables. Therefore, the subquery statement is repeated in the code.

with 
  a as (select * from src where key is not null),
  b as (select  * from src2 where value > 0),
  c as (select * from src3 where value > 0),
  d as (select a.key, b.value from a join b on a.key=b.key),
  e as (select a.key,c.value from a left outer join c on a.key=c.key and c.key is not null)
insert overwrite table srcp partition (p='abc')
select * from d union all select * from e;

In the preceding code, the subquery that corresponds to a is written only once and is subsequently reused as a CTE. You can specify multiple subqueries as CTEs in the same WITH clause. This way, you can repeatedly use them in the statement the same way as you use variables. CTEs also eliminate the need to repeatedly nest subqueries.