with — Organize Complex Queries


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.

Support My Work

I offer SQL training, tuning and consulting. Buying my book “SQL Performance Explained” (from €9.95) also supports my work on this website.

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

BigQueryabaDb2 (LUW)MariaDBcMySQLOracle DBPostgreSQLdSQL ServerdSQLitedwith on top-levelwith in subqueriesinsert … with … selectwith masks schema objectswith doesn’t imply recursiveviews bypass withqualified names bypass with
  1. Without column list: WITH name AS (SELECT…)
  2. Without column list: WITH name AS (SELECT…) • CTE in subquery cannot see global CTEs
  3. CTE in subquery cannot see global CTEs
  4. 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).

You can’t catch up on 20 years of SQL evolution in one day. Subscribe the newsletter via E-Mail, Twitter or RSS to gradually catch up and to keep modern-⁠sql.com on your radar.

About the Author

Photo of Markus Winand

Markus Winand provides insights into SQL and shows how different systems support it at modern-sql.com. Previously he made use-the-index-luke.com, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via winand.at.

Buy the Book

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy now!
(paperback and/or PDF)

Paperback also available at Amazon.com.

Hire Markus

Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »

Footnotes

  1. Table-valued functions come close to first-class citizens but are still alien to the optimizer. Consider how to push down predicates into a table-valued function. Although it would be possible for functions written in SQL, this is the exception and not the rule.

  2. More precisely: query expressions.

  3. More precisely: it is a prefix for the <query expression body>. That means, is is also valid for values, but not valid right after union, intersect and except (unless if written as a subquery in parenthesis).

  4. The column names are mandatory if the query returns multiple columns with the same name or the recursive keyword is specified (ISO/IEC 9075-2:2023 §7.17 SR 3ji).

  5. ISO/IEC 9075-2:2023 §7.17 SR 3ii.

  6. ISO/IEC 9075-2:2023 §7.6 SR 9ai

  7. With and with recursive differ in their visibility inside the with clause itself: query names defined in a with clause are only visible after their declaration. Query names defined in a with recursive clause are visible everywhere in this clause—even before and inside their own definition.

  8. ISO/IEC 9075-2:2023 §5.4 SR 5b Format: <query name> = <identifier> (a query name cannot be qualified).

  9. ISO/IEC 9075-1:2023 §6.3.3.1 definition of generally contain, which is not used in ISO/IEC 9075-2:2023 §7.6.

    Personally, I’d love it if there was a way to allow with clauses to overwrite tables inside views (like SQLite does)—that opens the door to generic views, i.e. the very same view definition can be used with different base tables. Currently, table functions are the common approach to achieve this behavior.

Connect with Markus Winand

Subscribe mailinglistsSubscribe the RSS feedMarkus Winand on LinkedInMarkus Winand on XINGMarkus Winand on TwitterMarkus Winand on Bluesky
Copyright 2015-2024 Markus Winand. All righs reserved.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR