Conditional Functions

edit

This functionality is in beta and is subject to change. The design and code is less mature than official GA features and is being provided as-is with no warranties. Beta features are not subject to the support SLA of official GA features.

Functions that return one of their arguments by evaluating in an if-else manner.

COALESCE

edit

Synopsis:

COALESCE(
    expression, 
    expression, 
    ...)

Input:

1st expression

2nd expression

…​

Nth expression

COALESCE can take an arbitrary number of arguments.

Output: one of the expressions or null

DescriptionReturns the first of its arguments that is not null. If all arguments are null, then it returns null.

SELECT COALESCE(null, 'elastic', 'search') AS "coalesce";

    coalesce
---------------
elastic
SELECT COALESCE(null, null, null, null) AS "coalesce";

    coalesce
---------------
null

IFNULL

edit

Synopsis:

IFNULL(
    expression, 
    expression) 

Input:

1st expression

2nd expression

Output: 2nd expression if 1st expression is null, otherwise 1st expression.

DescriptionVariant of COALESCE with only two arguments. Returns the first of its arguments that is not null. If all arguments are null, then it returns null.

SELECT IFNULL('elastic', null) AS "ifnull";

    ifnull
---------------
elastic
SELECT IFNULL(null, 'search') AS "ifnull";

    ifnull
---------------
search

ISNULL

edit

Synopsis:

ISNULL(
    expression, 
    expression) 

Input:

1st expression

2nd expression

Output: 2nd expression if 1st expression is null, otherwise 1st expression.

DescriptionVariant of COALESCE with only two arguments. Returns the first of its arguments that is not null. If all arguments are null, then it returns null.

SELECT ISNULL('elastic', null) AS "isnull";

    isnull
---------------
elastic
SELECT ISNULL(null, 'search') AS "isnull";

    isnull
---------------
search

NVL

edit

Synopsis:

NVL(
    expression, 
    expression) 

Input:

1st expression

2nd expression

Output: 2nd expression if 1st expression is null, otherwise 1st expression.

DescriptionVariant of COALESCE with only two arguments. Returns the first of its arguments that is not null. If all arguments are null, then it returns null.

SELECT NVL('elastic', null) AS "nvl";

    nvl
---------------
elastic
SELECT NVL(null, 'search') AS "nvl";

    nvl
---------------
search

NULLIF

edit

Synopsis:

NULLIF(
    expression, 
    expression) 

Input:

1st expression

2nd expression

Output: null if the 2 expressions are equal, otherwise the 1st expression.

DescriptionReturns null when the two input expressions are equal and if not, it returns the 1st expression.

SELECT NULLIF('elastic', 'search') AS "nullif";
    nullif
---------------
elastic
SELECT NULLIF('elastic', 'elastic') AS "nullif";

    nullif:s
---------------
null

GREATEST

edit

Synopsis:

GREATEST(
    expression, 
    expression, 
    ...)

Input:

1st expression

2nd expression

…​

Nth expression

GREATEST can take an arbitrary number of arguments and all of them must be of the same data type.

Output: one of the expressions or null

DescriptionReturns the argument that has the largest value which is not null. If all arguments are null, then it returns null.

SELECT GREATEST(null, 1, 2) AS "greatest";

    greatest
---------------
2
SELECT GREATEST(null, null, null, null) AS "greatest";

    greatest
---------------
null

LEAST

edit

Synopsis:

LEAST(
    expression, 
    expression, 
    ...)

Input:

1st expression

2nd expression

…​

Nth expression

LEAST can take an arbitrary number of arguments and all of them must be of the same data type.

Output: one of the expressions or null

DescriptionReturns the argument that has the smallest value which is not null. If all arguments are null, then it returns null.

SELECT LEAST(null, 2, 1) AS "least";

    least
---------------
1
SELECT LEAST(null, null, null, null) AS "least";

    least
---------------
null