SQL conditional functions

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

• coalesce
• ifnull
• nullif
• nvl
• nvl2

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)

nvl2

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

nvl2(expression1, expression2, expression3)

Arguments

• expression1: First expression to test for null. Can be a constant, column, or function, and any combination of operators.
• expression2: Second expression to return if expression1 is not null. Can be a constant, column, or function, and any combination of operators.
• expression3: Expression to return if expression1 is null. Can be a constant, column, or function, and any combination of operators.

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