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 creates a simple CTE to filter and aggregate data before using it in the main query.

You can define multiple CTEs in a single query by separating them with commas.

This example shows how to specify column names explicitly in the CTE definition.

CTEs can also use VALUES statements to create temporary data sets.

This example demonstrates using UNION within a CTE to combine data from multiple sources.

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.