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);
│ 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;
│ 1 │ ᴺᵁᴸᴸ │
│ 2 │ 3 │