WITH
clause provides a way to define auxiliary statements (referred by their alias names), that can be used within a more complex query sets. They are also known as Common Table Expressions (CTEs).
WITH
clause precedes the primary statement it is attached to and contains a list of auxiliary statements with corresponding aliases.
primary_statement
: has to be one of the following: SELECT
, INSERT
, UPDATE
, DELETE
with_statement_body
: has to be a SELECT
statement (it can refer to aliases defined earlier in the query)AS b
part) as in the WITH
clause. It can be changed by explicitly setting a new alias upon usage.
WITH
clauses are useful when you want to refactor some complex query to make it more readable. You can extract subqueries or even reuse them in several places, having only one definition. Thanks to code insertion, each use of a query will be optimized separately, specifically for the usage of its results by the parent query. For example:
math_grades
query, but each of them filters it using different keys. This way, both scans will only read a part of the table. If materialized CTE was used (which we don’t support yet), the query engine would need to scan the whole table first and then filter the result twice, for each subquery.
WITH
clause. However, if you create nested SELECT
statements, each of them can have their own WITH
clauses, creating their own contexts for defined aliases.
2, 1
as an output.
When referencing an alias we use the context, which was defined at the nested query level. If it does not define the referenced alias, we move up one level and repeat searching for an alias definition.
ERROR: relation "b" does not exist
, as b
is not defined in this context or any of the above.