`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

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

where with_query is:

1<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.

1WITH high_value_orders AS (
2    SELECT customer_id, order_date, total_amount
3    FROM orders
4    WHERE total_amount > 1000
5)
6SELECT customer_id, COUNT(*) AS order_count, AVG(total_amount) AS avg_amount
7FROM high_value_orders
8GROUP BY customer_id;

Multiple CTEs

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

1WITH sales_summary AS (
2    SELECT customer_id, SUM(amount) AS total_sales
3    FROM orders
4    WHERE order_date >= '2024-01-01'
5    GROUP BY customer_id
6),
7customer_info AS (
8    SELECT customer_id, customer_name, region
9    FROM customers
10    WHERE status = 'active'
11)
12SELECT ci.customer_name, ci.region, ss.total_sales
13FROM customer_info ci
14JOIN sales_summary ss ON ci.customer_id = ss.customer_id
15ORDER BY ss.total_sales DESC;

CTE with Explicit Column Names

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

1WITH regional_sales(region_name, total_revenue, order_count) AS (
2    SELECT region, SUM(amount), COUNT(*)
3    FROM orders o
4    JOIN customers c ON o.customer_id = c.customer_id
5    GROUP BY region
6)
7SELECT region_name, total_revenue, order_count,
8       total_revenue / order_count AS avg_order_value
9FROM regional_sales
10WHERE total_revenue > 50000;

CTE with VALUES Statement

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

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

CTE with Set Operations

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

1WITH all_contacts AS (
2    SELECT customer_id, email, 'customer' AS contact_type
3    FROM customers
4    WHERE email IS NOT NULL
5    UNION ALL
6    SELECT vendor_id, email, 'vendor' AS contact_type
7    FROM vendors
8    WHERE email IS NOT NULL
9)
10SELECT contact_type, COUNT(*) as contact_count
11FROM all_contacts
12GROUP 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.

1WITH RECURSIVE employee_recursive(distance, employee_name, manager_name) AS (
2    SELECT 1, employee_name, manager_name
3    FROM employee
4    WHERE manager_name = 'Mary'
5    UNION ALL
6    SELECT er.distance + 1, e.employee_name, e.manager_name
7    FROM employee_recursive er, employee e
8    WHERE er.employee_name = e.manager_name
9    )
10SELECT distance, employee_name FROM employee_recursive;

DID THIS ARTICLE SOLVE YOUR ISSUE?
Let us know so we can improve!

WITH Clause