Common table expressions (WITH)

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.

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

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.

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.

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

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().

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.

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.

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.

Get Started

Core Concepts

Querying

Functions Reference

Advanced

Common table expressions (WITH)

Syntax

Forward chaining

Using CTEs in JOIN and UNION

Fluent API

Evaluation strategy

Errors

Limitations

​Syntax

​Forward chaining

​Using CTEs in JOIN and UNION

​Fluent API

​Evaluation strategy

​Errors

​Limitations

Syntax

Forward chaining

Using CTEs in JOIN and UNION

Fluent API

Evaluation strategy

Errors

Limitations