with — Organize Complex Queries


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 with-queries are only visible in a single statement and can thus be used to improve the structure of this statement without polluting the global namespace.

The 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.

Syntax

with is a prefix to the select keyword1:

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 from clause.

A single with clause can introduce multiple query names by separating them with a comma (the with keyword itself is not repeated). Each of these queries can refer to the query names previously defined within the same with 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.

Performance Considerations

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 from clause.

The SQL standard does not define how to optimize querys—both approaches are conforming.

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 sub-queries.

The basic functionality of with is well-supported. The single area where products behave differently is name resolution. It is especially notable that with is often treated like with 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” the outer with clause8).

Conforming Alternatives

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.

Proprietary Extensions

Oracle: Functions in with

The Oracle Database support function and procedure declarations within the with clause since version 12cR1 (docs).

PostgreSQL: DML in with

Starting with 9.1 the PostgreSQL database supports DML statements (insert, update, delete) within the body of with-queries (docs). When using the (also proprietary) returning clause of the DML statement, the with-query actually returns data (e.g, the just inserted rows).

SQL Server: with as DML prefix

SQL Server 2005 and later accepts with as prefix to DML statements (documentation). The so-defined query-name can also be used as target of the DML statement (basically building an updatable view).

About the Author

Photo of Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing—for that he also published the book SQL Performance Explained.

Footnotes

  1. Table-valued functions come close to first-class citizens but are still aliens 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, not the rule.

  2. It's actually valid for the values and table keywords.

  3. The column names are mandatory if the query returns multiple columns with the same name or the recursive keyword is specified (SQL:2011-2, §7.13, Syntax Rule 3gi).

  4. SQL:2011-2, §7.13, Syntax Rule 3e.

  5. SQL:2011-2, §7.6 Syntax Rule 12a applies so that the “otherwise” paragraph 12c cannot be applied.

  6. See “Allow the CTE (Common Table Expression) optimization fence to be optionally disabled” in the PostgreSQL Todo list.

  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. SQL:2011-2, §5.4 Format: <query name> = <identifier> (a query name cannot be qualified). Also Syntax Rule 5b.

  9. SQL:2011-1, §6.3.3.1 definition of generally contain, which is not used in SQL:2011-2, §7.6.

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

“modern SQL” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy | CC-BY-NC-ND 3.0 license