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

> Documentation for hypothetical (what-if) projections

# HYPOTHETICAL PROJECTION

Hypothetical projections are virtual, session-scoped projections that you can attach to a `MergeTree` family table without actually building or storing them. They exist only inside the current session and are listed by [`EXPLAIN WHATIF`](/docs/reference/statements/explain#explain-whatif).

`EXPLAIN WHATIF` does not estimate the benefit of a hypothetical projection yet — it reports each one with `status: not_applicable`. Defining them is useful today for validating a definition against the table without materializing it, and for tooling that reads [`system.hypothetical_projections`](/docs/reference/system-tables/hypothetical_projections).

<h2 id="create-hypothetical-projection">
  CREATE HYPOTHETICAL PROJECTION
</h2>

```sql theme={null}
CREATE HYPOTHETICAL PROJECTION [IF NOT EXISTS] name
    ON [db.]table_name (SELECT <columns> [WHERE ...] [GROUP BY ...] [ORDER BY ...]) [WITH SETTINGS (...)]

CREATE HYPOTHETICAL PROJECTION [IF NOT EXISTS] name
    ON [db.]table_name INDEX <expression> TYPE <projection_index_type> [WITH SETTINGS (...)]
```

The syntax mirrors `ALTER TABLE ... ADD PROJECTION`, and the definition is validated exactly the same way, so a projection rejected here could not have been materialized either. Nothing is built or written — only the description is stored, in the current session.

* `name` — projection name; must be unique within `(database, table)` for this session, and must not collide with a real projection on the table.
* The body accepts the same forms as a real projection: a reordering projection with `ORDER BY`, an aggregating one with `GROUP BY`, a filtered one with `WHERE`, or the projection-index form `INDEX <expression> TYPE <projection_index_type>`.
* `WITH SETTINGS (...)` is accepted and preserved; the settings are visible in `system.hypothetical_projections`.

The target table must be a `MergeTree` family table in an `Atomic` database (it must have a UUID), because the session store keys entries by table UUID. The restrictions a real `ADD PROJECTION` enforces apply here too: tables with `UNIQUE KEY`, non-`Ordinary` merging modes under `deduplicate_merge_projection_mode = throw`, old-syntax `MergeTree`, and immutable disks are rejected.

**Example**

```sql theme={null}
CREATE HYPOTHETICAL PROJECTION p_by_b ON t (SELECT a, b ORDER BY b);
CREATE HYPOTHETICAL PROJECTION p_idx ON t INDEX b TYPE basic;
```

<h2 id="drop-hypothetical-projection">
  DROP HYPOTHETICAL PROJECTION
</h2>

```sql theme={null}
DROP HYPOTHETICAL PROJECTION [IF EXISTS] name ON [db.]table_name
```

Removes a hypothetical projection from the current session.

<h2 id="drop-all-hypothetical-projections">
  DROP ALL HYPOTHETICAL PROJECTIONS
</h2>

```sql theme={null}
DROP ALL HYPOTHETICAL PROJECTIONS
```

Clears every hypothetical projection defined in the current session, regardless of table. It leaves hypothetical indexes untouched; `DROP ALL HYPOTHETICAL INDEXES` does the reverse.

<h2 id="scope-and-lifetime">
  Scope and lifetime
</h2>

* Hypothetical projections live only in the **current session** — they are invisible to other sessions and discarded when the session ends.
* Defining or dropping one builds no projection and never affects ordinary queries against the table.
* Inspect the current session's hypothetical projections via [`system.hypothetical_projections`](/docs/reference/system-tables/hypothetical_projections).

<h2 id="required-privileges">
  Required privileges
</h2>

`CREATE HYPOTHETICAL PROJECTION` requires `ALTER ADD PROJECTION` on the table — the same privilege the real `ALTER TABLE ... ADD PROJECTION` needs — because it validates the definition against the table's columns. It reads no table data, so `SELECT` on the projection's columns is not required yet; when `EXPLAIN WHATIF` starts estimating projections it will read those columns and column-level `SELECT` will be required then, as it already is for [`CREATE HYPOTHETICAL INDEX`](/docs/reference/statements/hypothetical-index#required-privileges).

`DROP HYPOTHETICAL PROJECTION` requires the same privilege, so that naming a table in a drop cannot reveal whether it exists or is eligible. `DROP ALL HYPOTHETICAL PROJECTIONS` names no table and requires no privilege.

<h2 id="see-also">
  See also
</h2>

* [`CREATE HYPOTHETICAL INDEX`](/docs/reference/statements/hypothetical-index)
* [`EXPLAIN WHATIF`](/docs/reference/statements/explain#explain-whatif)
* [`system.hypothetical_projections`](/docs/reference/system-tables/hypothetical_projections)
* [Projections](/docs/reference/engines/table-engines/mergetree-family/mergetree#projections)