Conditional Functions
editConditional Functions
editFunctions that return one of their arguments by evaluating in an if-else manner.
COALESCE
editSynopsis:
Input:
…
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
GREATEST
editSynopsis:
Input:
…
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
IFNULL
editSynopsis:
Input:
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
editSynopsis:
Input:
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
LEAST
editSynopsis:
Input:
…
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
NULLIF
editSynopsis:
Input:
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
NVL
editSynopsis:
Input:
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