title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/select/where.md)

The `WHERE` clause allows you to filter the data that comes from the[`FROM`](/docs/sql-reference/statements/select/from) clause of `SELECT`.

If there is a `WHERE` clause, it must be followed by an expression of type `UInt8`.
Rows where this expression evaluates to `0` are excluded from further transformations or the result.

The expression following the `WHERE` clause is often used with [comparison](/docs/sql-reference/operators#comparison-operators) and [logical operators](/docs/sql-reference/operators#operators-for-working-with-data-sets), or one of the many [regular functions](/docs/sql-reference/functions/regular-functions).

The `WHERE` expression is evaluated on the ability to use indexes and partition pruning, if the underlying table engine supports that.

PREWHERE

There is also a filtering optimization called [`PREWHERE`](/docs/sql-reference/statements/select/prewhere).
Prewhere is an optimization to apply filtering more efficiently.
It is enabled by default even if `PREWHERE` clause is not specified explicitly.

## Testing for `NULL`[​](#testing-for-null "Direct link to testing-for-null")

If you need to test a value for [`NULL`](/docs/sql-reference/syntax#null), use:

- [`IS NULL`](/docs/sql-reference/operators#is_null) or [`isNull`](/docs/sql-reference/functions/functions-for-nulls#isNull)
- [`IS NOT NULL`](/docs/sql-reference/operators#is_not_null) or [`isNotNull`](/docs/sql-reference/functions/functions-for-nulls#isNotNull)

An expression with `NULL` will otherwise never pass.

## Filtering data with logical operators[​](#filtering-data-with-logical-operators "Direct link to Filtering data with logical operators")

You can use the following [logical functions](/docs/sql-reference/functions/logical-functions#and) together with the `WHERE` clause for combining multiple conditions:

- [`and()`](/docs/sql-reference/functions/logical-functions#and) or `AND`
- [`not()`](/docs/sql-reference/functions/logical-functions#not) or `NOT`
- [`or()`](/docs/sql-reference/functions/logical-functions#or) or `NOT`
- [`xor()`](/docs/sql-reference/functions/logical-functions#xor)

## Using UInt8 columns as a condition[​](#using-uint8-columns-as-a-condition "Direct link to Using UInt8 columns as a condition")

In ClickHouse, `UInt8` columns can be used directly as boolean conditions, where `0` is `false` and any non-zero value (typically `1`) is `true`.
An example of this is given in the section [below](#example-uint8-column-as-condition).

## Using comparison operators[​](#using-comparison-operators "Direct link to Using comparison operators")

The following [comparison operators](/docs/sql-reference/operators#comparison-operators) can be used:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Operator Function Description Example|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `a = b` `equals(a, b)` Equal to `price = 100`| `a == b` `equals(a, b)` Equal to (alternative syntax) `price == 100`| `a != b` `notEquals(a, b)` Not equal to `category != 'Electronics'`| `a <> b` `notEquals(a, b)` Not equal to (alternative syntax) `category <> 'Electronics'`| `a < b` `less(a, b)` Less than `price < 200`| `a <= b` `lessOrEquals(a, b)` Less than or equal to `price <= 200`| `a > b` `greater(a, b)` Greater than `price > 500`| `a >= b` `greaterOrEquals(a, b)` Greater than or equal to `price >= 500`| `a LIKE s` `like(a, b)` Pattern matching (case-sensitive) `name LIKE '%top%'`| `a NOT LIKE s` `notLike(a, b)` Pattern not matching (case-sensitive) `name NOT LIKE '%top%'`| `a ILIKE s` `ilike(a, b)` Pattern matching (case-insensitive) `name ILIKE '%LAPTOP%'`| `a BETWEEN b AND c` `a >= b AND a <= c` Range check (inclusive) `price BETWEEN 100 AND 500`| `a NOT BETWEEN b AND c` `a < b OR a > c` Outside range check `price NOT BETWEEN 100 AND 500` | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

## Pattern matching and conditional expressions[​](#pattern-matching-and-conditional-expressions "Direct link to Pattern matching and conditional expressions")

Beyond comparison operators, you can use pattern matching and conditional expressions in the `WHERE` clause.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Feature Syntax Case-Sensitive Performance Best For|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `LIKE` `col LIKE '%pattern%'` Yes Fast Exact case pattern matching|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `ILIKE` `col ILIKE '%pattern%'` No Slower Case-insensitive searching|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `if()` `if(cond, a, b)` N/A Fast Simple binary conditions|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `multiIf()` `multiIf(c1, r1, c2, r2, def)` N/A Fast Multiple conditions|  |  |  |  |  | | --- | --- | --- | --- | --- | | `CASE` `CASE WHEN ... THEN ... END` N/A Fast SQL-standard conditional logic | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

See ["Pattern matching and conditional expressions"](#examples-pattern-matching-and-conditional-expressions) for usage examples.

## Expression with literals, columns or subqueries[​](#expressions-with-literals-columns-subqueries "Direct link to Expression with literals, columns or subqueries")

The expression following the `WHERE` clause can also include [literals](/docs/sql-reference/syntax#literals), columns or subqueries, which are nested `SELECT` statements that return values used in conditions.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Type Definition Evaluation Performance Example|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **Literal** Fixed constant value Query write time Fastest `WHERE price > 100`| **Column** Table data reference Per row Fast `WHERE price > cost`| **Subquery** Nested SELECT Query execution time Varies `WHERE id IN (SELECT ...)` | | | | | | | | | | | | | | | | | | | |

You can mix literals, columns, and subqueries in complex conditions:

```
-- Literal + Column
WHERE price > 100 AND category = 'Electronics'

-- Column + Subquery
WHERE price > (SELECT AVG(price) FROM products) AND in_stock = true

-- Literal + Column + Subquery
WHERE category = 'Electronics' 
  AND price < 500
  AND id IN (SELECT product_id FROM bestsellers)