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])
| Parameters | Description |
|---|---|
expression1, expression_n | If 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)
| Parameters | Description |
|---|---|
expression1 | If equal to expression2, the expression to be compared and returned.Can be a constant, column, or function, and any combination of arithmetic operators. |
expression2 | The 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 |
| |
| |
| |
+-----------------------------------+