# WITH

> For the complete documentation index, see [llms.txt](https://docs.redpanda.com/llms.txt). Component-specific: [cloud-data-platform-full.txt](https://docs.redpanda.com/cloud-data-platform-full.txt)

---
title: WITH
latest-operator-version: v26.1.4
latest-console-tag: v3.7.3
latest-connect-version: 4.93.0
latest-redpanda-tag: v26.1.9
docname: sql/sql-clauses/with
page-component-name: cloud-data-platform
page-version: master
page-component-version: master
page-component-title: Cloud
page-relative-src-path: sql/sql-clauses/with.adoc
page-edit-url: https://github.com/redpanda-data/cloud-docs/edit/main/modules/reference/pages/sql/sql-clauses/with.adoc
description: The WITH clause defines auxiliary statements (Common Table Expressions) for use within a larger query.
page-topic-type: reference
page-git-created-date: "2026-05-26"
page-git-modified-date: "2026-05-26"
---

<!-- Source: https://docs.redpanda.com/cloud-data-platform/reference/sql/sql-clauses/with.md -->

The `WITH` clause defines auxiliary statements (referred to by their alias names) for use within a larger query. These auxiliary statements are also known as Common Table Expressions (CTEs).

## [](#syntax)Syntax

The `WITH` clause precedes the primary statement it is attached to and contains a list of auxiliary statements with corresponding aliases.

```sql
WITH [with_statement_alias AS (with_statement_body)]+ primary_statement;
```

-   `primary_statement`: A `SELECT`, `INSERT`, `UPDATE`, or `DELETE` statement.

-   `with_statement_body`: A `SELECT` statement. It can refer to aliases defined earlier in the query.


## [](#semantics)Semantics

Redpanda SQL only supports non-materialized CTEs. Each auxiliary query alias is replaced with its corresponding body at the early stages of query processing. The following query:

```sql
WITH a AS (SELECT 77), b AS (SELECT * FROM a) SELECT * FROM b
```

is effectively turned into:

```sql
SELECT * FROM (SELECT * FROM (SELECT 77) AS a) AS b
```

The auxiliary query gets the same alias (`AS b` part) as in the `WITH` clause. To change it, set a new alias on usage.

```sql
WITH b AS (SELECT 1 AS c1) SELECT b.c1, b1.c1 FROM b CROSS JOIN b AS b1;
```

## [](#usage)Usage

Non-materialized `WITH` clauses are useful when you want to refactor a complex query to make it more readable. You can extract subqueries or reuse them in several places, having only one definition. Each use of a query is optimized separately, specifically for how the parent query uses its results. For example:

```sql
WITH math_grades AS (SELECT g_date, semester_id, grade FROM grades WHERE subject="Math")
SELECT * FROM
(SELECT AVG(grade) FROM math_grades WHERE semester_id=2137) AS avg_semester_grades,
(SELECT AVG(grade) FROM math_grades WHERE g_date >= (CURRENT_TIMESTAMP() - INTERVAL '1 y')) AS avg_year_grades
```

Both subqueries use the same auxiliary `math_grades` query, but each filters it using different keys. As a result, both scans only read part of the table. With a materialized CTE (not yet supported), the query engine would scan the whole table first and then filter the result twice, once for each subquery.

## [](#alias-context)Alias context

You cannot create more than one CTE with the same alias within a single `WITH` clause. However, nested `SELECT` statements can each have their own `WITH` clauses, creating their own contexts for defined aliases.

> 📝 **NOTE**
>
> The same alias can be defined in more than one context.

```sql
WITH a AS ( -- creates context 1
    SELECT 1
)
SELECT * FROM (
    WITH a AS (SELECT 2) -- creates context 2
    SELECT * FROM a -- uses context 2
) CROSS JOIN a; -- uses context 1
```

The query returns `2, 1` as output.

When referencing an alias, the context defined at the nested query level is used. If the nested context does not define the referenced alias, the search moves up one level and repeats until an alias definition is found.

```sql
WITH a AS (
    SELECT 1
)
SELECT * FROM (
    WITH b as (SELECT 2)
    SELECT * FROM b
) CROSS JOIN b; -- error
```

The query returns `ERROR: relation "b" does not exist`, because `b` is not defined in this context or any of the contexts above.