In software engineering, it is common practice to group instructions as small and easily comprehensible units—namely functions or methods. This makes the code reusable and improves readability.
Even though SQL has functions and procedures as well, they are not the right tools for building easily understandable and reusable units. In SQL, neither functions nor procedures are first-class citizens in the same way that subqueries are.0 The building block of SQL are queries1—not instructions.
To make queries reusable, SQL-92 introduced views. Once created, a view has a name in the database schema so that other queries can use it like a table.
SQL:1999 added the with
clause to define “statement scoped views”. They are not stored in the database schema: instead, they are only valid in the query they belong to. This makes it possible to improve the structure of a statement without polluting the global namespace.
Syntax
The with
clause is, simply put, an optional prefix for select
:2
WITH query_name (column_name1, ...) AS
(SELECT ...)
SELECT ...
The syntax after the keyword with
is the same as it is for create view
: it starts with the query name, and optionally3 and in parenthesis the name of the columns it returns. The keyword as
finally introduces the definition itself (the query)—again in parentheses.
With
is not a stand alone command like create view
is: it must be followed by select
. This query (and subqueries it contains) can refer to the just defined query name in their from
clause.
A single with
clause can introduce multiple query names by separating them with a comma (the with
keyword is not repeated). Each of these queries can refer to the query names previously defined within the same with
clause4 (an often neglected rule—see Compatibility):
WITH query_name1 AS (
SELECT ...
)
, query_name2 AS (
SELECT ...
FROM query_name1
...
)
SELECT ...
Query names defined using with
mask existing tables or views with the same name.5
Performance Considerations
Most databases process with
-queries in the same way that they process views: they replace the reference to the query by its definition and optimize the overall query.
The PostgreSQL database was different until version 12: it optimized each with
query and the main statement independent of each other.
If a with
query is referred to multiple times, some databases cache (i.e. “materialize”) its result to prevent double execution.
Read more about this in “with
Clause: Performance Impacts”.
On my Own Behalf
I make my living from SQL training, SQL tuning and consulting and my book “SQL Performance Explained”. Learn more at https://winand.at/.
Use Cases
Compatibility
The with
clause was introduced with SQL:1999 as a set of optional features. Leaving the recursive
variant aside, the features are T121 for top-level with
clauses and T122 for with
clauses in subqueries.
The basic functionality of with
is well-supported. The single area where products behave differently is name resolution. It is especially noteworthy that with
is often treated like with recursive
.6 The more subtle incompatibilities are related to qualified table names (schema.table
cannot refer to a with
query)7 and views used in the scope of with
queries (the query inside the view does not “see” the outer with
clause).8
- Without column list:
WITH name AS (SELECT…)
- Without column list:
WITH name AS (SELECT…)
• CTE in subquery cannot see global CTEs - CTE in subquery cannot see global CTEs
- Supports proprietary syntax
with … insert … select
Conforming Alternatives
Views can cover some of the use cases. However, this can easily lead to an unreasonable number of views (“namespace pollution”). In those cases, subqueries are often the better option.
Proprietary Extensions
with
as DML prefix (PostgreSQL, SQL Server, SQLite)
Some databases accept with
as a prefix to DML statements (docs: PostgreSQL, SQL Server, SQLite).
SQL Server can also use a with
query as a target for DML statements (basically building an updatable view).
Functions in with
(Oracle)
The Oracle Database supports function and procedure declarations within the with
clause since version 12cR1 (documentation).
DML in with
(PostgreSQL)
Starting with 9.1, the PostgreSQL database supports DML statements (insert
, update
, delete
) within the body of with
queries (documentation). When using the (also proprietary) returning
clause of the DML statement, the with
query actually returns data (e.g. the rows just inserted).