WHERE Clause

WHERE clause allows to filter the data that is coming from FROM clause of SELECT.

If there is a WHERE clause, it must contain an expression with the UInt8 type. This is usually an expression with comparison and logical operators. Rows where this expression evaluates to 0 are excluded from further transformations or result.

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

note

There is a filtering optimization called PREWHERE.

If you need to test a value for NULL, use IS NULL and IS NOT NULL operators or isNull and isNotNull functions. Otherwise an expression with NULL never passes.

Example

To find numbers that are multiples of 3 and are greater than 10 execute the following query on the numbers table:

Result:

Queries with NULL values:

Result: