elasticsearch/docs/reference/sql/functions/conditional.asciidoc

[role="xpack"]
[testenv="basic"]
[[sql-functions-conditional]]
=== Conditional Functions

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

[[sql-functions-conditional-coalesce]]
==== `COALESCE`

.Synopsis:
[source, sql]
----
COALESCE(
    expression, <1>
    expression, <2>
    ...)
----

*Input*:

<1> 1st expression

<2> 2nd expression

...

**N**th 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`.



["source","sql",subs="attributes,callouts,macros"]
----
include-tagged::{sql-specs}/docs/docs.csv-spec[coalesceReturnNonNull]
----

["source","sql",subs="attributes,callouts,macros"]
----
include-tagged::{sql-specs}/docs/docs.csv-spec[coalesceReturnNull]
----

[[sql-functions-conditional-greatest]]
==== `GREATEST`

.Synopsis:
[source, sql]
----
GREATEST(
    expression, <1>
    expression, <2>
    ...)
----

*Input*:

<1> 1st expression

<2> 2nd expression

...

**N**th 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`.



["source","sql",subs="attributes,callouts,macros"]
----
include-tagged::{sql-specs}/docs/docs.csv-spec[greatestReturnNonNull]
----

["source","sql",subs="attributes,callouts,macros"]
----
include-tagged::{sql-specs}/docs/docs.csv-spec[greatestReturnNull]
----

[[sql-functions-conditional-ifnull]]
==== `IFNULL`

.Synopsis:
[source, sql]
----
IFNULL(
    expression, <1>
    expression) <2>
----

*Input*:

<1> 1st expression

<2> 2nd expression


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

.Description

Variant of <<sql-functions-conditional-coalesce>> with only two arguments.
Returns the first of its arguments that is not null.
If all arguments are null, then it returns `null`.



["source","sql",subs="attributes,callouts,macros"]
----
include-tagged::{sql-specs}/docs/docs.csv-spec[ifNullReturnFirst]
----

["source","sql",subs="attributes,callouts,macros"]
----
include-tagged::{sql-specs}/docs/docs.csv-spec[ifNullReturnSecond]
----


[[sql-functions-conditional-isnull]]
==== `ISNULL`

.Synopsis:
[source, sql]
----
ISNULL(
    expression, <1>
    expression) <2>
----

*Input*:

<1> 1st expression

<2> 2nd expression


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

.Description

Variant of <<sql-functions-conditional-coalesce>> with only two arguments.
Returns the first of its arguments that is not null.
If all arguments are null, then it returns `null`.



["source","sql",subs="attributes,callouts,macros"]
----
include-tagged::{sql-specs}/docs/docs.csv-spec[isNullReturnFirst]
----

["source","sql",subs="attributes,callouts,macros"]
----
include-tagged::{sql-specs}/docs/docs.csv-spec[isNullReturnSecond]
----


[[sql-functions-conditional-least]]
==== `LEAST`

.Synopsis:
[source, sql]
----
LEAST(
    expression, <1>
    expression, <2>
    ...)
----

*Input*:

<1> 1st expression

<2> 2nd expression

...

**N**th 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`.



["source","sql",subs="attributes,callouts,macros"]
----
include-tagged::{sql-specs}/docs/docs.csv-spec[leastReturnNonNull]
----

["source","sql",subs="attributes,callouts,macros"]
----
include-tagged::{sql-specs}/docs/docs.csv-spec[leastReturnNull]
----


[[sql-functions-conditional-nullif]]
==== `NULLIF`

.Synopsis:
[source, sql]
----
NULLIF(
    expression, <1>
    expression) <2>
----

*Input*:

<1> 1st expression

<2> 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.


["source","sql",subs="attributes,callouts,macros"]
----
include-tagged::{sql-specs}/docs/docs.csv-spec[nullIfReturnFirst]
----

["source","sql",subs="attributes,callouts,macros"]
----
include-tagged::{sql-specs}/docs/docs.csv-spec[nullIfReturnNull]
----


[[sql-functions-conditional-nvl]]
==== `NVL`

.Synopsis:
[source, sql]
----
NVL(
    expression, <1>
    expression) <2>
----

*Input*:

<1> 1st expression

<2> 2nd expression


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

.Description

Variant of <<sql-functions-conditional-coalesce>> with only two arguments.
Returns the first of its arguments that is not null.
If all arguments are null, then it returns `null`.



["source","sql",subs="attributes,callouts,macros"]
----
include-tagged::{sql-specs}/docs/docs.csv-spec[nvlReturnFirst]
----

["source","sql",subs="attributes,callouts,macros"]
----
include-tagged::{sql-specs}/docs/docs.csv-spec[nvlReturnSecond]
----