IFNULL

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

Remarks

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

The following example uses 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

LEAST

⌘I

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Return Types

Remarks

Examples

​Syntax

​Parameters

​Return Types

​Remarks

​Examples

Syntax

Parameters

Return Types

Remarks

Examples