IMPORTANT: No additional bug fixes or documentation updates will be released for this version. For the latest information, see the current release documentation.

« Type Conversion Functions System Functions »

› › ›

Conditional Functions

edit

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

« Type Conversion Functions System Functions »