Compares two expressions. Returns <expression1> if it’s non-NULL, otherwise returns <expression2>.
Syntax
IFNULL(<expression1>, <expression2>)
Parameters
| Parameter | Description | Supported input types |
|---|
<expression1>, <expression2> | Expressions that evaluate to any data type that Firebolt supports. | Any |
Return Types
Same as input type
Use ZEROIFNULL(<expression>) as a synonym shorthand for IFNULL(<expression>, 0).
IFNULL(a, b) has the same behaviour as COALESCE(a, b).
Examples
The following example shows the core behavior: IFNULL returns the original value when it is non-NULL, and returns the replacement when it is NULL:
SELECT val, IFNULL(val, 0) AS val_or_zero
FROM UNNEST(ARRAY[1, NULL, 3, NULL, 5]) AS t(val)
ORDER BY val NULLS LAST;
| val int null | val_or_zero int |
|---|
| 1 | 1 |
| 3 | 3 |
| 5 | 5 |
| NULL | 0 |
| NULL | 0 |
Rows: 5Execution time: 4.15ms
IFNULL to replace the nextlevel value with 0 for the final level in the levels table, which has no next level (NULL):
SELECT level, name, nextlevel, IFNULL(nextlevel, 0) AS next_level_or_zero
FROM levels
ORDER BY level;
| level int null | name text null | nextlevel int null | next_level_or_zero int |
|---|
| 1 | Thunderbolt Circuit | 2 | 2 |
| 2 | Velocity Vale | 3 | 3 |
| 3 | Raceway Ridge | 4 | 4 |
| 4 | Nitro Narrows | 5 | 5 |
| 5 | Thunder Road | 6 | 6 |
| 6 | Burnout Boulevard | 7 | 7 |
| 7 | Speed Street | 8 | 8 |
| 8 | Racing Ravine | 9 | 9 |
| 9 | Drift District | 10 | 10 |
| 10 | Acceleration Alley | NULL | 0 |
Rows: 10Execution time: 6.00ms
