Docs
-->
Elasticsearch Reference [7.0] » SQL access » Functions and Operators » Conditional Functions
«  Type Conversion Functions     System Functions  »

Conditional Functions

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

COALESCE

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

Description. Returns 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

GREATEST

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

Description. Returns 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

IFNULL

Synopsis:  

IFNULL(
    expression, 
    expression)

Input:

1st expression

2nd expression

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

Description. Variant 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

Synopsis:  

ISNULL(
    expression, 
    expression)

Input:

1st expression

2nd expression

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

Description. Variant 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

LEAST

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

Description. Returns 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

NULLIF

Synopsis:  

NULLIF(
    expression, 
    expression)

Input:

1st expression

2nd expression

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

Description. Returns 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

NVL

Synopsis:  

NVL(
    expression, 
    expression)

Input:

1st expression

2nd expression

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

Description. Variant 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
«  Type Conversion Functions     System Functions  »