Comparison Operators in ClickHouse: Symbols, Functions, and Examples

  

When querying data in ClickHouse, comparing values is a fundamental part of filtering results. Whether you're filtering rows based on numeric thresholds, matching text patterns, or evaluating ranges, comparison operators make it possible.

 

ClickHouse supports both symbolic operators (e.g., =, !=, <) and their corresponding function equivalents (e.g., equals, greater, notEquals). Internally, these symbols are converted to functions during parsing based on operator precedence and associativity.

 

This post provides a clear guide to the comparison operators available in ClickHouse, their functional equivalents, and real-world examples.

 

What is a Comparison Operator?

A comparison operator is used to evaluate two values and return a boolean result (true or false). These are mainly used in WHERE, HAVING, and conditional clauses to filter data.

 

Comparison Operators in ClickHouse

Symbol(s)	Function	Description	Example (Symbol)	Example (Function)
=, ==	equals(a, b)	Equality check	a = b	equals(a, b)
!=, <>	notEquals(a, b)	Not equal to	a != b or a <> b	notEquals(a, b)
>	greater(a, b)	Greater than	a > b	greater(a, b)
<	less(a, b)	Less than	a < b	less(a, b)
>=	greaterOrEquals	Greater than or equal to	a >= b	greaterOrEquals(a, b)
<=	lessOrEquals	Less than or equal to	a <= b	lessOrEquals(a, b)
a LIKE b	like(a, b)	Pattern match using % and _	name LIKE 'John%'	like(name, 'John%')
a NOT LIKE b	notLike(a, b)	Pattern not match	name NOT LIKE 'A%'	notLike(name, 'A%')
a BETWEEN b AND c		Range inclusive	a BETWEEN 10 AND 20	a >= 10 AND a <= 20
a NOT BETWEEN b AND c		Range exclusive	a NOT BETWEEN 10 AND 20	a < 10 OR a > 20

 

In ClickHouse, the results of comparison expressions like 5 = 5 or greater(15, 10) are returned as:

 

·      1 for true

·      0 for false

 

ClickHouse doesn’t return literal true/false values like some other systems.

-- EQUALITY
SELECT equals(5, 5);           -- returns 1
SELECT equals(5, 6);           -- returns 0
SELECT 5 = 5;                  -- returns 1
SELECT 5 = 6;                  -- returns 0

-- INEQUALITY
SELECT notEquals(10, 20);      -- returns 1
SELECT notEquals(10, 10);      -- returns 0
SELECT 10 != 20;               -- returns 1
SELECT 10 != 10;               -- returns 0
SELECT 10 <> 20;               -- returns 1
SELECT 10 <> 10;               -- returns 0

-- GREATER
SELECT greater(15, 10);        -- returns 1
SELECT greater(10, 15);        -- returns 0
SELECT 15 > 10;                -- returns 1
SELECT 10 > 15;                -- returns 0

-- LESS
SELECT less(5, 9);             -- returns 1
SELECT less(9, 5);             -- returns 0
SELECT 5 < 9;                  -- returns 1
SELECT 9 < 5;                  -- returns 0

-- GREATER OR EQUAL
SELECT greaterOrEquals(7, 7);  -- returns 1
SELECT greaterOrEquals(9, 7);  -- returns 1
SELECT greaterOrEquals(5, 7);  -- returns 0
SELECT 7 >= 7;                 -- returns 1
SELECT 5 >= 7;                 -- returns 0

-- LESS OR EQUAL
SELECT lessOrEquals(3, 4);     -- returns 1
SELECT lessOrEquals(4, 4);     -- returns 1
SELECT lessOrEquals(5, 4);     -- returns 0
SELECT 3 <= 4;                 -- returns 1
SELECT 5 <= 4;                 -- returns 0

-- LIKE
SELECT like('ClickHouse', 'Click%');        -- returns 1
SELECT like('ClickHouse', 'House%');        -- returns 0
SELECT 'ClickHouse' LIKE 'Click%';          -- returns 1
SELECT 'ClickHouse' LIKE '%SQL';            -- returns 0

-- NOT LIKE
SELECT notLike('Alice', 'B%');              -- returns 1
SELECT notLike('Bob', 'B%');                -- returns 0
SELECT 'Alice' NOT LIKE 'B%';               -- returns 1
SELECT 'Bob' NOT LIKE 'B%';                 -- returns 0

-- BETWEEN (assuming value = 20)
SELECT 20 BETWEEN 18 AND 25;                -- returns 1
SELECT 30 BETWEEN 18 AND 25;                -- returns 0
SELECT 20 >= 18 AND 20 <= 25;               -- returns 1
SELECT 30 >= 18 AND 30 <= 25;               -- returns 0

-- NOT BETWEEN (assuming value = 90)
SELECT 90 NOT BETWEEN 60 AND 80;            -- returns 1
SELECT 70 NOT BETWEEN 60 AND 80;            -- returns 0
SELECT 90 < 60 OR 90 > 80;                  -- returns 1
SELECT 70 < 60 OR 70 > 80;                  -- returns 0

   

In summary, ClickHouse supports a wide range of comparison operators that can be used in both symbolic (a > b) and functional (greater(a, b)) formats. Internally, symbolic operators are converted to function calls during parsing. Understanding both styles provides flexibility and clarity when querying and filtering large datasets.

 

Previous                                                    Next                                                    Home