with clause (subquery factoring) is resolved as a temporary table to store the intermediate results of subqueries for further reference.
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.
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
The subquery after with cannot end with a semicolon (“;”).
Separate multiple subqueries with a comma (“,”). Do not end the last subquery with “,”.
with asis not supported in user-defined functions.
withsupports distributed queries to access data from DFS tables.
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
$ 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