WHERE clause allows to filter the data that is coming from FROM clause of
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.
There is a filtering optimization called PREWHERE.
To find numbers that are multiples of 3 and are greater than 10 execute the following query on the numbers table:
SELECT number FROM numbers(20) WHERE (number > 10) AND (number % 3 == 0);
┌─number─┐ │ 12 │ │ 15 │ │ 18 │ └────────┘
CREATE TABLE t_null(x Int8, y Nullable(Int8)) ENGINE=MergeTree() ORDER BY x; INSERT INTO t_null VALUES (1, NULL), (2, 3); SELECT * FROM t_null WHERE y IS NULL; SELECT * FROM t_null WHERE y != 0;
┌─x─┬────y─┐ │ 1 │ ᴺᵁᴸᴸ │ └───┴──────┘ ┌─x─┬─y─┐ │ 2 │ 3 │ └───┴───┘