with

The with clause (subquery factoring) is resolved as a temporary table to store the intermediate results of subqueries for further reference.

Using with clause has the following advantages:

  • It simplifies complex SQL queries and improves readability.

  • Repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being re-queried by each reference.

  • It reduces the memory overhead of variable definitions as the temporary table is released after query execution.

Syntax

with table_name_1[(colNames..)] as (select_statement_1),
table_name_2[(colNames..)] as (select_statement_2),
...
table_name_n[(colNames..)] as (select_statement_n)
final_select_statement

Note:

  • The subquery after with cannot end with a semicolon (";").

  • Separate multiple subqueries with a comma (","). Do not end the last subquery with ",".

  • with as is not supported in user-defined functions.

  • with supports distributed queries to access data from DFS tables.

Arguments

table_name is the name of temporary table.

colNames.. are variables used to rename the columns returned by as clause. The number of colNames must be the same as the number of returned columns.

select_statement can be select or exec statement.

Examples

t1 = table(1 3 4 5 8 as id, 2 2.5 2.4 2.2 2.9 as val)
t2 = table(1 2 4 6 8 as id, `a`a`b`d`c as sym)

with tmp as (select * from t1 inner join t2 on t1.id=t2.id) select count(*) from tmp
3