Handling Null Values

Cloud9QL provides several ways to work with missing and null values.

IFNULL

Returns an alternate value to be used in case the specified field does not exist or the value is NULL.

IFNULL(<field name>, <alternate value>)

select IFNULL(CustomerName, "N/A")

You can also specify an alternate column in place of an alternate value:

IFNULL(<field name>, <another field name>)

select IFNULL(CustomerName, CustomerId)

IS NULL / IS NOT NULL

Tests whether a value is null.

select * where Notes is not null

Note that an empty string is not treated as null, so IS NOT NULL will match rows containing empty strings. To exclude blank values as well, use NOT_EMPTY.

Other functions that handle null values

Cloud9QL provides several other ways to work with nulls, documented alongside their related functions:

• FILL_NULL – forward-fills null values with the last non-null value seen, based on row order. Useful for sparse time series and status columns.
• NOT_EMPTY – aggregate that returns the first non-null, non-blank value within each group.
• ARRAY – accepts an optional filter-out-null flag to exclude nulls when combining values into an array.
• JOIN – accepts an optional skip-null argument so missing values don't produce extra separators.
• EXPAND_ARRAYS_WITH_DEFAULTS – fills in blank fields with a default value when expanded arrays differ in size.