Conditional Functions

ClickHouse supports conditional functions, which are executed based on the specified condition. The syntax is given below.

Syntax:

if (condition, then_expression, else_expression)

If the condition evaluates to Null or Zero, the then_expression is executed and the results are returned. If the condition evaluates to zero, the else_expression is executed and the results are returned.

Example:

Let us look at an example here. In this example, we will check whether a string is empty or non-empty.

chistadata :) SELECT if(length('Hello')>0, 'Non-Empty', 'Empty');

SELECT if(length('Hello') > 0, 'Non-Empty', 'Empty')

Query id: af76e587-cd67-429d-81a5-2b8db568f88d

┌─if(greater(length('Hello'), 0), 'Non-Empty', 'Empty')─┐
│ Non-Empty                                             │
└───────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.001 sec. 

Since the string ‘Hello’ is not empty, we have ‘Non-Empty’ as result of this.

chistadata :) SELECT if(length('')>0, 'Non-Empty', 'Empty');

SELECT if(length('') > 0, 'Non-Empty', 'Empty')

Query id: a28bd765-869f-47a5-b880-81fdf84f94d1

┌─if(greater(length(''), 0), 'Non-Empty', 'Empty')─┐
│ Empty                                            │
└──────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.001 sec.

In this case, since the string is empty, we have ‘Empty’ as the result.

Reference:

https://clickhouse.com/docs/en/sql-reference/functions/conditional-functions#ternary-operator