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.

Syntax

[WITH [RECURSIVE] <with_query>[, ...]]
SELECT [ALL | DISTINCT [ON (<expression>[, ...])]]
...
[{UNION | INTERSECT | EXCEPT} [ALL | DISTINCT] <select>]
...

where with_query is:

<with_query_name>[(<column_name>[, ...])] AS (<select>)

Examples

Basic CTE Usage

This example creates a simple CTE to filter and aggregate data before using it in the main query.

WITH high_value_orders AS (
    SELECT customer_id, order_date, total_amount
    FROM orders
    WHERE total_amount > 1000
)
SELECT customer_id, count(*) AS order_count, avg(total_amount) AS avg_amount
FROM high_value_orders
GROUP BY customer_id;

Multiple CTEs

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

WITH sales_summary AS (
    SELECT customer_id, sum(amount) AS total_sales
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY customer_id
),
customer_info AS (
    SELECT customer_id, customer_name, region
    FROM customers
    WHERE status = 'active'
)
SELECT ci.customer_name, ci.region, ss.total_sales
FROM customer_info ci
JOIN sales_summary ss ON ci.customer_id = ss.customer_id
ORDER BY ss.total_sales DESC;

CTE with Explicit Column Names

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

WITH regional_sales(region_name, total_revenue, order_count) AS (
    SELECT region, sum(amount), count(*)
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    GROUP BY region
)
SELECT region_name, total_revenue, order_count,
       total_revenue / order_count AS avg_order_value
FROM regional_sales
WHERE total_revenue > 50000;

CTE with VALUES Statement

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

WITH month_names(month_num, month_name) AS (
    VALUES (1, 'January'), (2, 'February'), (3, 'March'),
           (4, 'April'), (5, 'May'), (6, 'June'),
           (7, 'July'), (8, 'August'), (9, 'September'),
           (10, 'October'), (11, 'November'), (12, 'December')
)
SELECT mn.month_name, coalesce(sum(o.amount), 0) as monthly_sales
FROM month_names mn
LEFT JOIN orders o ON EXTRACT(MONTH FROM o.order_date) = mn.month_num
    AND EXTRACT(YEAR FROM o.order_date) = 2024
GROUP BY mn.month_num, mn.month_name
ORDER BY mn.month_num;

CTE with Set Operations

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

WITH all_contacts AS (
    SELECT customer_id, email, 'customer' AS contact_type
    FROM customers
    WHERE email IS NOT NULL
    UNION ALL
    SELECT vendor_id, email, 'vendor' AS contact_type
    FROM vendors
    WHERE email IS NOT NULL
)
SELECT contact_type, count(*) as contact_count
FROM all_contacts
GROUP BY contact_type;

Recursive CTE for Hierarchical Data

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.

WITH RECURSIVE employee_recursive(distance, employee_name, manager_name) AS (
    SELECT 1, employee_name, manager_name
    FROM employee
    WHERE manager_name = 'Mary'
    UNION ALL
    SELECT er.distance + 1, e.employee_name, e.manager_name
    FROM employee_recursive er, employee e
    WHERE er.employee_name = e.manager_name
    )
SELECT distance, employee_name FROM employee_recursive;