# `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`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/select.md) or [`VALUES`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/values.md) 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

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

where `with_query` is:

```sql
<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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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;
```