Impala Conditional Functions
Impala supports the following conditional functions for testing equality, comparison operators, and nullity:
- CASE
- CASE2
- COALESCE
- DECODE
- IF
- IFNULL
- ISFALSE
- ISNOTFALSE
- ISNOTTRUE
- ISNULL
- ISTRUE
- NONNULLVALUE
- NULLIF
- NULLIFZERO
- NULLVALUE
- NVL
- NVL2
- ZEROIFNULL
- CASE a WHEN b THEN c [WHEN d THEN e]... [ELSE f] END
-
Purpose: Compares an expression to one or more possible values, and returns a
corresponding result when a match is found.
Return type: same as the initial argument value, except that integer values are promoted to
BIGINT
and floating-point values are promoted toDOUBLE
; useCAST()
when inserting into a smaller numeric columnUsage notes:
In this form of the
CASE
expression, the initial valueA
being evaluated for each row it typically a column reference, or an expression involving a column. This form can only compare against a set of specified values, not ranges, multi-value comparisons such asBETWEEN
orIN
, regular expressions, orNULL
.Examples:
Although this example is split across multiple lines, you can put any or all parts of a
CASE
expression on a single line, with no punctuation or other separators between theWHEN
,ELSE
, andEND
clauses.select case x when 1 then 'one' when 2 then 'two' when 0 then 'zero' else 'out of range' end from t1;
- CASE WHEN a THEN b [WHEN c THEN d]... [ELSE e] END
-
Purpose: Tests whether any of a sequence of expressions is
TRUE
, and returns a corresponding result for the first true expression.Return type: same as the initial argument value, except that integer values are promoted to
BIGINT
and floating-point values are promoted toDOUBLE
; useCAST()
when inserting into a smaller numeric columnUsage notes:
CASE
expressions without an initial test value have more flexibility. For example, they can test different columns in differentWHEN
clauses, or use comparison operators such asBETWEEN
,IN
andIS NULL
rather than comparing against discrete values.CASE
expressions are often the foundation of long queries that summarize and format results for easy-to-read reports. For example, you might use aCASE
function call to turn values from a numeric column into category strings corresponding to integer values, or labels such as "Small", "Medium" and "Large" based on ranges. Then subsequent parts of the query might aggregate based on the transformed values, such as how many values are classified as small, medium, or large. You can also useCASE
to signal problems with out-of-bounds values,NULL
values, and so on.By using operators such as
OR
,IN
,REGEXP
, and so on inCASE
expressions, you can build extensive tests and transformations into a single query. Therefore, applications that construct SQL statements often rely heavily onCASE
calls in the generated SQL code.Because this flexible form of the
CASE
expressions allows you to perform many comparisons and call multiple functions when evaluating each row, be careful applying elaborateCASE
expressions to queries that process large amounts of data. For example, when practical, evaluate and transform values throughCASE
after applying operations such as aggregations that reduce the size of the result set; transform numbers to strings after performing joins with the original numeric values.Examples:
Although this example is split across multiple lines, you can put any or all parts of a
CASE
expression on a single line, with no punctuation or other separators between theWHEN
,ELSE
, andEND
clauses.select case when dayname(now()) in ('Saturday','Sunday') then 'result undefined on weekends' when x > y then 'x greater than y' when x = y then 'x and y are equal' when x is null or y is null then 'one of the columns is null' else null end from t1;
- COALESCE(type v1, type v2, ...)
-
Purpose: Returns the first specified argument that is not
NULL
, orNULL
if all arguments areNULL
.Return type: same as the initial argument value, except that integer values are promoted to
BIGINT
and floating-point values are promoted toDOUBLE
; useCAST()
when inserting into a smaller numeric column - DECODE(type expression, type search1, type result1 [, type search2, type result2 ...] [, type default] )
-
Purpose: Compares the first argument,
expression
, to thesearch
expressions using theIS NOT DISTINCT
operator, and returns:-
The corresponding
result
when a match is found. -
The first corresponding
result
if there are more than one matchingsearch
expressions. -
The
default
expression if none of the search expressions matches the first argumentexpression
. -
NULL
if the finaldefault
expression is omitted and none of thesearch
expressions matches the first argument.
Return type: Same as the first argument with the following exceptions:-
Integer values are promoted to
BIGINT
. -
Floating-point values are promoted to
DOUBLE
. -
Use
CAST()
when inserting into a smaller numeric column.
Usage notes:
-
Can be used as shorthand for a
CASE
expression. -
The first argument,
expression
, and the search expressions must be of the same type or convertible types. - The result expression can be a different type, but all result expressions must be of the same type.
-
Returns a successful match if the first argument is
NULL
and a search expression is alsoNULL
. -
NULL
can be used as a search expression.
Examples:
The following example translates numeric day values into weekday names, such as 1 to Monday, 2 to Tuesday, etc.
SELECT event, DECODE(day_of_week, 1, "Monday", 2, "Tuesday", 3, "Wednesday", 4, "Thursday", 5, "Friday", 6, "Saturday", 7, "Sunday", "Unknown day") FROM calendar;
-
The corresponding
- IF(BOOLEAN condition, type ifTrue, type ifFalseOrNull)
-
Purpose: Tests an expression and returns a corresponding result depending on
whether the result is
TRUE
,FALSE
, orNULL
.Return type: Same as the
ifTrue
argument value - IFNULL(type a, type ifNull)
-
Purpose: Alias for the
ISNULL()
function, with the same behavior. To simplify porting SQL with vendor extensions to Impala.Added in: Impala 1.3.0
- ISFALSE(BOOLEAN expression)
-
Purpose: Returns
TRUE
if the expression isFALSE
. ReturnsFALSE
if the expression isTRUE
orNULL
.Same as the
IS FALSE
operator.Similar to
ISNOTTRUE()
, except it returns the opposite value for aNULL
argument.Return type:
BOOLEAN
Added in: Impala 2.2.0
Usage notes:
In Impala 2.11 and higher, you can use the operators
IS [NOT] TRUE
andIS [NOT] FALSE
as equivalents for the built-in functionsISTRUE()
,ISNOTTRUE()
,ISFALSE()
, andISNOTFALSE()
. - ISNOTFALSE(BOOLEAN expression)
-
Purpose: Tests if a Boolean expression is not
FALSE
(that is, eitherTRUE
orNULL
). ReturnsTRUE
if so. If the argument isNULL
, returnsTRUE
.Same as the
IS NOT FALSE
operator.Similar to
ISTRUE()
, except it returns the opposite value for aNULL
argument.Return type:
BOOLEAN
Usage notes: Primarily for compatibility with code containing industry extensions to SQL.
Added in: Impala 2.2.0
Usage notes:
In Impala 2.11 and higher, you can use the operators
IS [NOT] TRUE
andIS [NOT] FALSE
as equivalents for the built-in functionsISTRUE()
,ISNOTTRUE()
,ISFALSE()
, andISNOTFALSE()
. - ISNOTTRUE(BOOLEAN expression)
-
Purpose: Tests if a Boolean expression is not
TRUE
(that is, eitherFALSE
orNULL
). ReturnsTRUE
if so. If the argument isNULL
, returnsTRUE
.Same as the
IS NOT TRUE
operator.Similar to
ISFALSE()
, except it returns the opposite value for aNULL
argument.Return type:
BOOLEAN
Added in: Impala 2.2.0
Usage notes:
In Impala 2.11 and higher, you can use the operators
IS [NOT] TRUE
andIS [NOT] FALSE
as equivalents for the built-in functionsISTRUE()
,ISNOTTRUE()
,ISFALSE()
, andISNOTFALSE()
. - ISNULL(type a, type ifNull)
-
Purpose: Tests if an expression is
NULL
, and returns the expression result value if not. If the first argument isNULL
, returns the second argument.Compatibility notes: Equivalent to the
NVL()
function from Oracle Database orIFNULL()
from MySQL. TheNVL()
andIFNULL()
functions are also available in Impala.Return type: Same as the first argument value
- ISTRUE(BOOLEAN expression)
-
Purpose: Returns
TRUE
if the expression isTRUE
. ReturnsFALSE
if the expression isFALSE
orNULL
.Same as the
IS TRUE
operator.Similar to
ISNOTFALSE()
, except it returns the opposite value for aNULL
argument.Return type:
BOOLEAN
Usage notes: Primarily for compatibility with code containing industry extensions to SQL.
Added in: Impala 2.2.0
Usage notes:
In Impala 2.11 and higher, you can use the operators
IS [NOT] TRUE
andIS [NOT] FALSE
as equivalents for the built-in functionsISTRUE()
,ISNOTTRUE()
,ISFALSE()
, andISNOTFALSE()
. - NONNULLVALUE(type expression)
-
Purpose: Returns
TRUE
if the expression is non-null and returnsFALSE
if the expression isNULL
.Same as the
IS NOT NULL
operator.The converse of
NULLVALUE()
.Return type:
BOOLEAN
Usage notes: Primarily for compatibility with code containing industry extensions to SQL.
Added in: Impala 2.2.0
- NULLIF(type expr1, type expr2)
-
Purpose: Returns
NULL
if the two specified arguments are equal. If the specified arguments are not equal, returns the value of expr1. The data types of the expressions must be compatible, according to the conversion rules from Data Types. You cannot use an expression that evaluates toNULL
for expr1; that way, you can distinguish a return value ofNULL
from an argument value ofNULL
, which would never match expr2.Usage notes: This function is effectively shorthand for a
CASE
expression of the form:CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END
It is commonly used in division expressions, to produce a
NULL
result instead of a divide-by-zero error when the divisor is equal to zero:select 1.0 / nullif(c1,0) as reciprocal from t1;
You might also use it for compatibility with other database systems that support the same
NULLIF()
function.Return type: same as the initial argument value, except that integer values are promoted to
BIGINT
and floating-point values are promoted toDOUBLE
; useCAST()
when inserting into a smaller numeric columnAdded in: Impala 1.3.0
- NULLIFZERO(type numeric_expr)
-
Purpose: Returns
NULL
if the numeric expression evaluates to 0, otherwise returns the result of the expression.Usage notes: Used to avoid error conditions such as divide-by-zero in numeric calculations. Serves as shorthand for a more elaborate
CASE
expression, to simplify porting SQL with vendor extensions to Impala.Return type: Same type as the input argument
Added in: Impala 1.3.0
- NULLVALUE(type expression)
-
Purpose: Returns
TRUE
if the expression isNULL
, and returnsFALSE
otherwise.Same as the
IS NULL
operator.The converse of
NONNULLVALUE()
.Return type:
BOOLEAN
Usage notes: Primarily for compatibility with code containing industry extensions to SQL.
Added in: Impala 2.2.0
- NVL(type a, type ifNull)
-
Purpose: Alias for the
ISNULL()
function. Returns the first argument if the first argument is notNULL
. Returns the second argument if the first argument isNULL
.Equivalent to the
NVL()
function in Oracle Database orIFNULL()
in MySQL.Return type: Same as the first argument value
Added in: Impala 1.1
- NVL2(type a, type ifNotNull, type ifNull)
-
Purpose: Returns the second argument,
ifNotNull
, if the first argument is notNULL
. Returns the third argument,ifNull
, if the first argument isNULL
.Equivalent to the
NVL2()
function in Oracle Database.Return type: Same as the first argument value
Added in: Impala 2.9.0
Examples:
SELECT NVL2(NULL, 999, 0); -- Returns 0 SELECT NVL2('ABC', 'Is Not Null', 'Is Null'); -- Returns 'Is Not Null'
- ZEROIFNULL(type numeric_expr)
-
Purpose: Returns 0 if the numeric expression evaluates to
NULL
, otherwise returns the result of the expression.Usage notes: Used to avoid unexpected results due to unexpected propagation of
NULL
values in numeric calculations. Serves as shorthand for a more elaborateCASE
expression, to simplify porting SQL with vendor extensions to Impala.Return type: Same type as the input argument
Added in: Impala 1.3.0