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

# Common table expressions (WITH)

> Declare named subqueries with WITH ... AS (...) and reference them from FROM, JOIN, and UNION branches of the main SELECT.

A common table expression (CTE) is a named subquery declared with `WITH` before the main `SELECT`. Each CTE behaves like a temporary, query-scoped data source that you can reference by name from `FROM`, `JOIN`, and `UNION` branches.

CTEs are useful when the same intermediate result is used in more than one place, when a query reads better as a sequence of named steps, or when you want to keep a complex pre-filter out of the main `SELECT`.

Added in **3.2.0**.

```sql theme={null}
WITH expensive AS (
    SELECT id, name, price
    FROM json(./products.json).data.products
    WHERE price > 400
)
SELECT *
FROM expensive
ORDER BY price DESC
LIMIT 10
```

## Syntax

```sql theme={null}
WITH name AS (select_statement)
    [, name AS (select_statement)] ...
select_statement
```

* A single `WITH` keyword declares any number of comma-separated CTEs.
* Each CTE name must be unique within the `WITH` block.
* Later CTEs can reference earlier ones (forward-only chaining).
* The main `SELECT` and every `JOIN` / `UNION` branch can reference any declared CTE by name in their `FROM` or join source.
* `EXPLAIN` and `EXPLAIN ANALYZE` accept a leading `WITH` block: `EXPLAIN WITH ... SELECT ...`.

## Forward chaining

A CTE body may reference any CTE declared earlier in the same `WITH` block. Mutual recursion and back-references are rejected at parse time.

```sql theme={null}
WITH
    expensive AS (
        SELECT id, name, price, brand.code AS brandCode
        FROM json(./products.json).data.products
        WHERE price > 400
    ),
    expensive_by_brand AS (
        SELECT brandCode, COUNT(id) AS productCount, SUM(price) AS totalPrice
        FROM expensive
        GROUP BY brandCode
    )
SELECT *
FROM expensive_by_brand
ORDER BY totalPrice DESC
```

## Using CTEs in JOIN and UNION

A CTE can appear anywhere a file source can — in the main `FROM`, in any `JOIN`, and in any `UNION` branch.

```sql theme={null}
WITH
    orders_2025 AS (
        SELECT user_id, total
        FROM csv(./orders.csv).*
        WHERE year = 2025
    )
SELECT u.id, u.name, o.total
FROM json(./users.json).data.users AS u
LEFT JOIN orders_2025 AS o
    ON u.id = o.user_id
```

```sql theme={null}
WITH cheap AS (
    SELECT name, price FROM json(./products.json).data.products
    WHERE price <= 100
)
SELECT name, price FROM cheap
UNION
SELECT name, price FROM json(./products.json).data.products
WHERE price >= 400
```

## Fluent API

Register named subqueries on the parent query with `with($name, $query)`. The CTE is then visible to any `JOIN` or `UNION` branch the parent composes. Inspect the registered CTEs with `hasCte()`, `getCte()`, and `getCtes()`.

```php theme={null}
use FQL\Enum\Operator;
use FQL\Query;

$expensive = Query\Provider::fromFileQuery('json(./products.json).data.products')
    ->select('id, name, price')
    ->where('price', Operator::GREATER_THAN, 400);

$results = Query\Provider::fromFileQuery('json(./users.json).data.users')
    ->with('expensive', $expensive)
    ->select('users.id', 'users.name', 'expensive.price')
    ->leftJoin($query->getCte('expensive'))->as('expensive')
        ->on('users.id', Operator::EQUAL, 'expensive.user_id')
    ->execute()
    ->fetchAll();
```

| Method                             | Returns                | Description                                 |
| ---------------------------------- | ---------------------- | ------------------------------------------- |
| `with(string $name, Query $query)` | `static`               | Register a named CTE on the parent query.   |
| `hasCte(string $name)`             | `bool`                 | Whether a CTE with that name is registered. |
| `getCte(string $name)`             | `Query`                | Resolve a registered CTE by name.           |
| `getCtes()`                        | `array<string, Query>` | All registered CTEs, keyed by name.         |

<Note>
  FROM-position CTE references are parser-only — the source stream of an already-constructed `Query` is immutable, so the fluent API uses CTEs through `JOIN` and `UNION` rather than as a `FROM` target. Use the FQL string form when you need a CTE in `FROM`.
</Note>

## Evaluation strategy

FiQueLa picks per-reference between two strategies, so a CTE used exactly once in a `JOIN` never pays an in-memory buffer:

| Reference position                              | Reference count | Strategy                                                                                                                                                      |
| ----------------------------------------------- | --------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `FROM`                                          | any             | **Materialise** — the body runs once and the rows are buffered in memory so the outer clauses (`WHERE`, `GROUP BY`, `ORDER BY`, …) merge into the same query. |
| `JOIN` or `UNION`                               | 1               | **Inline** — the body is built fresh and consumed opaquely. No caching, no in-memory buffer.                                                                  |
| `JOIN` or `UNION`                               | ≥ 2             | **Materialise on first reference**, share the cached stream with the rest.                                                                                    |
| `JOIN` after a `FROM` reference of the same CTE | —               | Reuses the cache already populated by the `FROM` reference for free.                                                                                          |

Cycles (a CTE that transitively references itself) are rejected at build time with a clear error.

## Errors

The parser surfaces problems before any file is opened:

* **Duplicate CTE name** in the same `WITH` block — `ParseException`.
* **`WITH RECURSIVE`** is not supported — `ParseException` (the `RECURSIVE` keyword exists solely to raise a targeted error).
* **Reference to an unknown CTE name** in `FROM` or `JOIN` — `ParseException` listing every declared name in scope, so typos surface immediately.
* **`DESCRIBE WITH ...`** — rejected (`DESCRIBE` expects a source, not a `SELECT`).

## Limitations

* `WITH RECURSIVE` is not supported.
* Nested `WITH` blocks inside subqueries are not supported — declare every CTE at the top level.
* CTE references inside `IN (...)` and `EXISTS (...)` are not supported (subqueries in condition operands are not yet parsed).
* `DESCRIBE` cannot be combined with `WITH`.