With
implies recursive
With
query names are visible in accessed viewsWith
names even mask qualified table names (schema.table
)WITH name AS (SELECT…)
with
don’t hide schema objectsThe with
clause can be used in subqueries to define even more locally scoped “views”:
SELECT …
FROM ( WITH …
SELECT …
FROM …
) table_name
Tutorial: With
— Organize Complex Queries
Other forms: with
at top-level, with recursive
, with recursive
in subquery.
Product specific forms: with
before insert
, DML in with
.
The with recursive
clause in subqueries is defined in ISO/IEC 9075-2:2023 §7.17 as optional feature T122, “WITH
(excluding RECURSIVE
) in subquery”.
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.
The essence of SQL tuning in 200 pages
Buy now!
(paperback and/or PDF)
Paperback also available at Amazon.com.
Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »