This tutorial explains the different types of operators in ClickHouse, how they work, and how to use them in real queries. Operators are fundamental to querying because they allow you to perform calculations, comparisons, logical decisions, and null handling directly inside SQL.
We will cover:
- Mathematical operators
- Comparison operators
- Logical operators
- NULL-check operators
Each section includes queries and their textual table results so you can clearly see the output.
1. What is an Operator?
In programming and SQL, an operator is a symbol or keyword that performs a specific action such as addition, comparison, or logical evaluation.
Operators allow you to:
- Perform calculations
- Filter data
- Apply conditions
- Handle NULL values
- Build analytical queries
ClickHouse supports both operator syntax and equivalent functions for most operations, which gives flexibility in query writing.
2. Mathematical Operators
Mathematical operators are used for arithmetic calculations.
ClickHouse supports:
- Addition (+)
- Subtraction (-)
- Multiplication (*)
- Division (/)
- Modulo (%)
Each operator has an equivalent function.
2.1 Addition
Query (Operator)
SELECT 5 + 3 AS result;
Result
| result |
|---|
| 8 |
Query (Function)
SELECT plus(5, 3) AS result;
Result
| result |
|---|
| 8 |
Both produce the same output.
2.2 Subtraction
Query
SELECT 10 - 4 AS result;
Result
| result |
|---|
| 6 |
2.3 Multiplication
Query
SELECT 6 * 7 AS result;
Result
| result |
|---|
| 42 |
2.4 Division
Query
SELECT 20 / 5 AS result;
Result
| result |
|---|
| 4 |
2.5 Modulo
Query
SELECT 10 % 3 AS result;
Result
| result |
|---|
| 1 |
Modulo returns the remainder.
3. Comparison Operators
Comparison operators compare two values and return Boolean results (1 = true, 0 = false).
Common operators:
- = (equals)
- != (not equals)
- (greater than)
- < (less than)
- = (greater or equal)
- <= (less or equal)
Each has an equivalent function.
3.1 Equality Check
Query (Operator)
SELECT 5 = 5 AS result;
Result
| result |
|---|
| 1 |
Query (Function)
SELECT equals(5, 5) AS result;
Result
| result |
|---|
| 1 |
3.2 Greater Than
Query
SELECT 10 > 3 AS result;
Result
| result |
|---|
| 1 |
3.3 Not Equal
Query
SELECT 5 != 2 AS result;
Result
| result |
|---|
| 1 |
4. Logical Operators
Logical operators combine or negate conditions.
ClickHouse supports:
- AND
- OR
- NOT
Equivalent functions:
- and()
- or()
- not()
4.1 Logical AND
Query
SELECT (5 > 3) AND (10 > 7) AS result;
Result
| result |
|---|
| 1 |
Both conditions are true.
4.2 Logical OR
Query
SELECT (5 > 10) OR (10 > 7) AS result;
Result
| result |
|---|
| 1 |
At least one condition is true.
4.3 Logical NOT
Query
SELECT NOT (5 > 3) AS result;
Result
| result |
|---|
| 0 |
Negates the condition.
5. NULL-Checking Operators
Handling NULL values is critical in analytics.
ClickHouse provides:
- IS NULL
- IS NOT NULL
Equivalent functions:
- isNull()
- isNotNull()
5.1 Check for NULL
Query
SELECT NULL IS NULL AS result;
Result
| result |
|---|
| 1 |
5.2 Check for NOT NULL
Query
SELECT 1 IS NOT NULL AS result;
Result
| result |
|---|
| 1 |
5.3 Using Functions
Query
SELECT isNull(NULL) AS result;
Result
| result |
|---|
| 1 |
Query
SELECT isNotNull(2) AS result;
Result
| result |
|---|
| 1 |