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).
Standards compliance: Introduced in SQL:1999.
Examples:
-- 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;