> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# CTEs and subqueries

> A common table expression names an intermediate query within one statement. A subquery places a query inside another query. Neither automatically persists its result as a table.

ReferenceApplies to: Unified engine deployments

A common table expression names an intermediate query within one statement. A subquery places a query inside another query. Neither automatically persists its result as a table.

## Common table expressions

Use WITH to make a query easier to read. A CTE is scoped to its statement. Later CTEs can refer to earlier ones. Recursive CTEs and materialization modifiers are unavailable.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH category_prices AS (
  SELECT i_category, AVG(CAST(i_current_price AS DOUBLE)) AS average_price
  FROM demo.tpcds.item
  WHERE i_category IS NOT NULL
  GROUP BY i_category
)
SELECT i_category, average_price
FROM category_prices
ORDER BY average_price DESC, i_category
LIMIT 5;
```

## Derived tables

A subquery in FROM is a derived table. Give it an alias. It is useful for filtering a window result because QUALIFY is unavailable.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT item_key, category, position
FROM (
  SELECT i_item_sk AS item_key, i_category AS category,
         ROW_NUMBER() OVER (PARTITION BY i_category ORDER BY i_item_sk) AS position
  FROM demo.tpcds.item
) AS ranked
WHERE position <= 2
ORDER BY category NULLS LAST, item_key
LIMIT 20;
```

## Scalar subqueries

A scalar subquery supplies a single value. It must return at most one row. Zero rows produce NULL; multiple rows produce an error. Aggregate subqueries are a convenient way to guarantee one row.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT i_item_sk, i_current_price
FROM demo.tpcds.item
WHERE CAST(i_current_price AS DOUBLE) > (
  SELECT AVG(CAST(i_current_price AS DOUBLE)) FROM demo.tpcds.item
)
ORDER BY i_item_sk
LIMIT 10;
```

## EXISTS and IN

EXISTS tests whether its subquery returns any rows. IN compares against returned values. NOT IN can become NULL when its input contains NULL, so do not substitute it for NOT EXISTS without considering null semantics. Correlated and null-aware forms can be restricted by the selected backend; an ordinary key join is often easier to inspect.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT i_item_sk, i_category
FROM demo.tpcds.item
WHERE i_category IN ('Books', 'Electronics')
ORDER BY i_item_sk
LIMIT 10;
```


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.