WITH Clause

A clause that can be added before a


     SELECT

statement, to define aliases for complicated expressions that are referenced multiple times within the body of the


     SELECT

. Similar to


     CREATE VIEW

, except that the table and column names defined in the


     WITH

clause do not persist after the query finishes, and do not conflict with names used in actual tables or views. Also known as "subquery factoring" . You can rewrite a query using subqueries to work the same as with the


     WITH

clause. The purposes of the


     WITH

clause are: Convenience and ease of maintenance from less repetition with the body of the query. Typically used with queries involving


     UNION

, joins, or aggregation functions where the similar complicated expressions are referenced multiple times. SQL code that is easier to read and understand by abstracting the most complex part of the query into a separate block. Improved compatibility with SQL from other database systems that support the same clause (primarily Oracle Database).

Note: The Impala


      WITH

clause does not support recursive queries in the


      WITH

, which is supported in some other database systems.

-- Define 2 subqueries that can be referenced from the body of a longer query.
with t1 as (select 1), t2 as (select 2) insert into tab select * from t1 union all select * from t2;