Conditional Functions¶
This page lists all conditional functions available in Spark SQL.
between¶
input [NOT] between lower AND upper - evaluate if input is [not] in between lower and upper
Arguments:
- input - An expression that is being compared with lower and upper bound.
- lower - Lower bound of the between check.
- upper - Upper bound of the between check.
Examples:
> SELECT 0.5 between 0.1 AND 1.0;
true
Since: 1.0.0
case¶
CASE expr1 WHEN expr2 THEN expr3 [WHEN expr4 THEN expr5]* [ELSE expr6] END - When expr1 = expr2, returns expr3; when expr1 = expr4, return expr5; else return expr6.
Arguments:
- expr1 - the expression which is one operand of comparison.
- expr2, expr4 - the expressions each of which is the other operand of comparison.
- expr3, expr5, expr6 - the branch value expressions and else value expression should all be same type or coercible to a common type.
Examples:
> SELECT CASE col1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE '?' END FROM VALUES 1, 2, 3;
one
two
?
> SELECT CASE col1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' END FROM VALUES 1, 2, 3;
one
two
NULL
Since: 1.0.1
coalesce¶
coalesce(expr1, expr2, ...) - Returns the first non-null argument if exists. Otherwise, null.
Examples:
> SELECT coalesce(NULL, 1, NULL);
1
Since: 1.0.0
if¶
if(expr1, expr2, expr3) - If expr1 evaluates to true, then returns expr2; otherwise returns expr3.
Examples:
> SELECT if(1 < 2, 'a', 'b');
a
Since: 1.0.0
ifnull¶
ifnull(expr1, expr2) - Returns expr2 if expr1 is null, or expr1 otherwise.
Examples:
> SELECT ifnull(NULL, array('2'));
["2"]
Since: 2.0.0
nanvl¶
nanvl(expr1, expr2) - Returns expr1 if it's not NaN, or expr2 otherwise.
Examples:
> SELECT nanvl(cast('NaN' as double), 123);
123.0
Since: 1.5.0
nullif¶
nullif(expr1, expr2) - Returns null if expr1 equals to expr2, or expr1 otherwise.
Examples:
> SELECT nullif(2, 2);
NULL
Since: 2.0.0
nullifzero¶
nullifzero(expr) - Returns null if expr is equal to zero, or expr otherwise.
Examples:
> SELECT nullifzero(0);
NULL
> SELECT nullifzero(2);
2
Since: 4.0.0
nvl¶
nvl(expr1, expr2) - Returns expr2 if expr1 is null, or expr1 otherwise.
Examples:
> SELECT nvl(NULL, array('2'));
["2"]
Since: 2.0.0
nvl2¶
nvl2(expr1, expr2, expr3) - Returns expr2 if expr1 is not null, or expr3 otherwise.
Examples:
> SELECT nvl2(NULL, 2, 1);
1
Since: 2.0.0
when¶
CASE WHEN expr1 THEN expr2 [WHEN expr3 THEN expr4]* [ELSE expr5] END - When expr1 = true, returns expr2; else when expr3 = true, returns expr4; else returns expr5.
Arguments:
- expr1, expr3 - the branch condition expressions should all be boolean type.
- expr2, expr4, expr5 - the branch value expressions and else value expression should all be same type or coercible to a common type.
Examples:
> SELECT CASE WHEN 1 > 0 THEN 1 WHEN 2 > 0 THEN 2.0 ELSE 1.2 END;
1.0
> SELECT CASE WHEN 1 < 0 THEN 1 WHEN 2 > 0 THEN 2.0 ELSE 1.2 END;
2.0
> SELECT CASE WHEN 1 < 0 THEN 1 WHEN 2 < 0 THEN 2.0 END;
NULL
Since: 1.0.1
zeroifnull¶
zeroifnull(expr) - Returns zero if expr is equal to null, or expr otherwise.
Examples:
> SELECT zeroifnull(NULL);
0
> SELECT zeroifnull(2);
2
Since: 4.0.0