Skip to main content
Version: 2.3.x

Conditional Functions

Conditional functions are a collection of functions used to perform different operations based on specific conditions.They include conditional statements, which can help you choose to execute different code logic based on different situations.Conditional functions are very useful for implementing program flow control and decision making.

coalesce

Returns its first non-null argument.If all parameters are null, then return null.This function is typically used to replace default values with null values.

coalesce(expression1[, ..., expression_n])
ParametersDescription
expression1, expression_nIf the previous expression is null, then use the expression.Can be a constant, column, or function, and any combination of arithmetic operators.Pass as many expression parameters as needed.
View coalesce Example
SELECT coalesce(temperature, null, station) FROM air;
+--------------------------------------------+
| coalesce(air.temperature,NULL,air.station) |
+--------------------------------------------+
| 69.0 |
| 78.0 |
| 62.0 |
| 79.0 |
| 53.0 |
| 72.0 |
| 71.0 |
| 69.0 |
| 80.0 |
| 74.0 |
| 70.0 |
| 70.0 |
| 70.0 |
+--------------------------------------------+

nullif

If expression1 is equal to expression2, it returns null; otherwise, it will return expression1.The inverse operation that can be used to perform coalesce.

nullif(expression1, expression2)
ParametersDescription
expression1If equal to expression2, the expression to be compared and returned.Can be a constant, column, or function, and any combination of arithmetic operators.
expression2The expression to compare with expression1.Can be a constant, column, or function, and any combination of arithmetic operators.
View nullif Example
SELECT nullif(temperature, 70) FROM air;
+-----------------------------------+
| nullif(air.temperature,Int64(70)) |
+-----------------------------------+
| 69 |
| 78 |
| 62 |
| 79 |
| 53 |
| 72 |
| 71 |
| 69 |
| 80 |
| 74 |
| |
| |
| |
+-----------------------------------+