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
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
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
WITHclause does not support recursive queries in the
WITH, which is supported in some other database systems.
Standards compliance: Introduced in SQL:1999.
-- 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;
-- Define one subquery at the outer level, and another at the inner level as part of the
-- initial stage of the UNION ALL query.
with t1 as (select 1) (with t2 as (select 2) select * from t2) union all select * from t1;