SQL conditional functions

The InfluxDB Clustered SQL implementation supports the following conditional functions for conditionally handling null values:

• coalesce
• ifnull
• nullif
• nvl

coalesce

Returns the first of its arguments that is not null. Returns null if all arguments are null. This function is often used to substitute a default value for null values.

coalesce(expression1[, ..., expression_n])

Arguments

• expression1, expression_n: Expression to use if previous expressions are null. Can be a constant, column, or function, and any combination of arithmetic operators. Pass as many expression arguments as necessary.

SELECT
  val1,
  val2,
  val3,
  coalesce(val1, val2, val3, 'quz') AS coalesce
FROM
  (values ('foo', 'bar', 'baz'),
          (NULL, 'bar', 'baz'),
          (NULL, NULL, 'baz'),
          (NULL, NULL, NULL)
  ) data(val1, val2, val3)

ifnull

Alias of nvl.

nullif

Returns null if expression1 equals expression2; otherwise it returns expression1. This can be used to perform the inverse operation of coalesce.

nullif(expression1, expression2)

Arguments

• expression1: Expression to compare and return if equal to expression2. Can be a constant, column, or function, and any combination of arithmetic operators.
• expression2: Expression to compare to expression1. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT
  value,
  nullif(value, 'baz') AS nullif
FROM
  (values ('foo'),
          ('bar'),
          ('baz')
  ) data(value)

nvl

Returns expression2 if expression1 is null; otherwise it returns expression1.

nvl(expression1, expression2)

Arguments

• expression1: Return this expression if not null. Can be a constant, column, or function, and any combination of arithmetic operators.
• expression2: Return this expression if expression1 is null. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT
  value,
  nvl(value, 'baz') AS nvl
FROM
  (values ('foo'),
          ('bar'),
          (NULL)
  ) data(value)