In imperative programming it is common practice to group instructions in small and easily comprehensible units—namely functions or methods. Often to enable code reuse but also to improve readability.
Even though SQL has functions and procedures too, they are not the primary tool to make complex SQL queries comprehensible or to enable code reuse. In SQL neither functions nor procedures are first-class citizens they way sub-queries are0.
In SQL the building block of programs are queries—not instructions. Consequently, SQL has views to store queries natively so they can be reused.
In addition, SQL:1999 introduced the
with clause to
define “views” within queries. These
only visible in a single statement and can thus be used to improve the
structure of this statement without polluting the global namespace.
with feature is also known as common
table expression (CTE) and sub-query
factoring. Note that the
recursive from of the
with clause is covered in another article.
with is a prefix to the
WITH query_name (column_name1, ...) AS (SELECT ...) SELECT ...
The syntax following
with is the same as for
create view: it starts with the query
name and optionally2 in parenthesis the name of the columns it returns. The
as keyword 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 sub-queries it contains) can
refer to the just defined query name in their
with clause can introduce multiple query
names by separating them with a comma (the
itself is not repeated). Each of these queries can refer to the query
names previously defined within the same
clause3 (but 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 name4.
Normally, databases handle
with-queries like they
handle views: they substitute the reference to the query by its definition
and then optimize and execute the overall query.
The PostgreSQL database is different in this regard (as of
9.55): it treads the
with clause as optimization
fence. The queries inside the
with clause are
optimized (planned) in isolation. This can have a significant performance
impact as compared to sub-queries directly written into the
The SQL standard does not define how to optimize querys—both approaches are conforming.
with clause was introduced with SQL:1999 as a set of optional features. Leaving the
variant aside, the features are T121 for top-level
clauses and T122 for
with clauses in
with is well-supported. The single
area where products behave differently is name resolution. It is
especially notable that
with is often treated like
recursive6. The more subtle incompatibilities are related to qualified
table names (
schema.table cannot refer to a
with query7) and views used in the scope of
with-queries (the query inside the view does not “see”
Some use cases can be implemented using views. However, this can quickly lead to an unreasonable number of views (“namespace pollution”). In those cases sub-queries are often the better option.
Oracle: Functions in
The Oracle Database support function and procedure declarations
with clause since version 12cR1 (docs).
PostgreSQL: DML in
Starting with 9.1 the PostgreSQL database supports DML statements
delete) within the body of
When using the (also proprietary)
returning clause of
the DML statement, the
with-query actually returns
data (e.g, the just inserted rows).