WITH Clause
The WITH
clause allows you to create and use one or more subqueries. The subqueries act as temporary tables or views. Each subquery can be a SELECT
or VALUES
statement.
WITH
queries evaluate one time per execution of the primary query, even if the primary query refers to them more than one time. The primary query and WITH
queries execute at the same time.
To create a WITH
clause, specify a name. Optionally, you can provide a list of column names. If you don’t provide column names, the column names are inferred from the subquery.
RECURSIVE
allows a SELECT
subquery to reference itself by name. The subquery syntax is <non_recursive_term> UNION [ ALL | DISTINCT ] <recursive_term>
. Use the recursive self-reference on the right side of the UNION
. Only one recursive self-reference is allowed per query. Make sure that the recursive part of the query eventually returns no tuples, or the query loops indefinitely.
where with_query
is:
This example uses WITH RECURSIVE
to find all direct or indirect subordinates of employee Mary, and their level of indirectness, from a table that shows only direct subordinates.