> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-trino-dialect.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Documentation for PREWHERE Clause

# PREWHERE

`PREWHERE` can make filtering more efficient by reducing the amount of data read. By default, ClickHouse applies this optimization, even when a query does not explicitly specify `PREWHERE`, by moving eligible conditions from [`WHERE`](/reference/statements/select/where) to `PREWHERE`. You can specify `PREWHERE` explicitly to control which conditions are applied at this stage.

With `PREWHERE`, ClickHouse first reads only the columns needed to evaluate the condition. It then reads the other columns required by the query only for blocks that contain at least one matching row. This can reduce the amount of data read when the condition uses fewer columns than the rest of the query and filters out many blocks.

<h2 id="controlling-prewhere-manually">
  Controlling `PREWHERE` manually
</h2>

Specify `PREWHERE` manually when a condition references a small number of columns and filters out many rows. This can reduce the amount of data read for the remaining columns.

A query can contain both `PREWHERE` and `WHERE`. In this case, `PREWHERE` is evaluated first.

Set [`optimize_move_to_prewhere`](/reference/settings/session-settings/optimize-move-to-prewhere#optimize_move_to_prewhere) to `0` to prevent ClickHouse from automatically moving conditions from `WHERE` to `PREWHERE`.

For queries with the [`FINAL`](/reference/statements/select/from#final-modifier) modifier, ClickHouse moves conditions from `WHERE` to `PREWHERE` only when both [`optimize_move_to_prewhere`](/reference/settings/session-settings/optimize-move-to-prewhere#optimize_move_to_prewhere) and [`optimize_move_to_prewhere_if_final`](/reference/settings/session-settings/optimize-move-to-prewhere#optimize_move_to_prewhere_if_final) are enabled.

<Note>
  By default, `PREWHERE` is evaluated before `FINAL`, so `FROM ... FINAL` queries may produce unexpected results when `PREWHERE` references columns outside the table's `ORDER BY` key.
</Note>

<h2 id="prewhere-with-join">
  `PREWHERE` with `JOIN`
</h2>

A `PREWHERE` condition in a query with a [`JOIN`](/reference/statements/select/join) can directly reference columns from at most one table. ClickHouse applies the condition to that table's rows before they reach the join.

By contrast, a `WHERE` condition logically filters the joined result, although the optimizer may apply it before the join when doing so does not change the result. Using the same condition in `PREWHERE` and `WHERE` can therefore produce different results, particularly with outer joins.

The following example creates two tables to demonstrate this difference:

```sql theme={null}
CREATE TABLE table_1
(
    `id` UInt32,
    `value` String
)
ENGINE = MergeTree
ORDER BY id;

CREATE TABLE table_2
(
    `id` UInt32,
    `value` String
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO table_1 VALUES (1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO table_2 VALUES (1, 'x'), (2, 'y'), (3, 'z');
```

In the first query, `PREWHERE` filters `table_2` before the `LEFT JOIN`, so the row from `table_1` with `id = 1` remains unmatched:

```sql theme={null}
SELECT
    table_1.id,
    table_1.value,
    table_2.value
FROM table_1
LEFT JOIN table_2 ON table_1.id = table_2.id
PREWHERE table_2.id >= 2
ORDER BY table_1.id;
```

```text theme={null}
   ┌─id─┬─value─┬─table_2.value─┐
1. │  1 │ a     │               │
2. │  2 │ b     │ y             │
3. │  3 │ c     │ z             │
   └────┴───────┴───────────────┘
```

Using the same condition in `WHERE` filters the joined result, removing the row with `id = 1`:

```sql theme={null}
SELECT
    table_1.id,
    table_1.value,
    table_2.value
FROM table_1
LEFT JOIN table_2 ON table_1.id = table_2.id
WHERE table_2.id >= 2
ORDER BY table_1.id;
```

```text theme={null}
   ┌─id─┬─value─┬─table_2.value─┐
1. │  2 │ b     │ y             │
2. │  3 │ c     │ z             │
   └────┴───────┴───────────────┘
```

<h2 id="limitations">
  Limitations
</h2>

`PREWHERE` is only supported by tables from the [\*MergeTree](/reference/engines/table-engines/mergetree-family/index) family.

<h2 id="example">
  Example
</h2>

```sql theme={null}
CREATE TABLE mydata
(
    `A` Int64,
    `B` Int8,
    `C` String
)
ENGINE = MergeTree
ORDER BY A AS
SELECT
    number,
    0,
    if(number between 1000 and 2000, 'x', toString(number))
FROM numbers(10000000);

SELECT count()
FROM mydata
WHERE (B = 0) AND (C = 'x');

1 row in set. Elapsed: 0.074 sec. Processed 10.00 million rows, 168.89 MB (134.98 million rows/s., 2.28 GB/s.)

-- Enable tracing to see which predicates are moved to PREWHERE.
set send_logs_level='debug';

MergeTreeWhereOptimizer: condition "B = 0" moved to PREWHERE
-- ClickHouse automatically moves B = 0 to PREWHERE, but this condition does not filter any rows because B is always 0.

-- Move the more selective C = 'x' predicate to PREWHERE.

SELECT count()
FROM mydata
PREWHERE C = 'x'
WHERE B = 0;

1 row in set. Elapsed: 0.069 sec. Processed 10.00 million rows, 158.89 MB (144.90 million rows/s., 2.30 GB/s.)

-- The query with manually specified PREWHERE processes slightly less data: 158.89 MB instead of 168.89 MB.
```