SQL conditional functions

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

• coalesce
• greatest
• ifnull
• least
• 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)

greatest

Returns the greatest value in a list of expressions. Returns null if all expressions are null.

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

Arguments

• expression1, expression_n: Expressions to compare and return the greatest value. Can be a constant, column, or function, and any combination of arithmetic operators. Pass as many expression arguments as necessary.

SELECT time, greatest(a, b) AS greatest FROM numbers LIMIT 4

ifnull

Alias of nvl.

least

Returns the least value in a list of expressions. Returns null if all expressions are null.

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

Arguments

• expression1, expression_n: Expressions to compare and return the least value. Can be a constant, column, or function, and any combination of arithmetic operators. Pass as many expression arguments as necessary.

SELECT time, least(a, b) AS least FROM numbers LIMIT 4

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)