NULLIF()、NULLIFZERO()、 ZEROIFNULL()用法

原文：

nullif(expr1,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 to  NULL for  expr1; that way, you can distinguish a return value of  NULL from an argument value of  NULL, 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 to DOUBLE; use CAST() when inserting into a smaller numeric column

Added in: Impala 1.3.0

翻译：

用途:返回NULL如果指定的两个参数是相等的。如果指定的参数不相等,返回expr1的价值。表达式的数据类型必须一致,根据数据类型的转换规则。您不能使用一个表达式expr1评估为NULL;这样,你可以区分的返回值为空的参数值NULL,这永远不会匹配expr2。

CASE
  WHEN expr1 = expr2 THEN NULL
  ELSE expr1
END

部门中常用表达式,生产一个空的结果,而不是被零除错误当除数等于零:

你也可以用它来与其他数据库系统的兼容性,支持相同的NULLIF()函数。

select 1.0 / nullif(c1,0) as reciprocal from t1;

返回类型:初始参数值一样,除了整数值提升为BIGINT和浮点值提升到两倍;使用CAST()当插入到一个较小的数字列。

原文：

nullifzero(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 as the initial argument value, except that integer values are promoted to BIGINT and floating-point values are promoted to DOUBLE; use CAST() when inserting into a smaller numeric column

Added in: Impala 1.3.0

翻译：

用途:返回NULL如果数值表达式的求值结果为0,否则返回表达式的结果。
使用notes:用于避免在数值计算错误条件,如被零除。作为速记更复杂的表达式,简化移植与供应商扩展SQL黑斑羚。
返回类型:初始参数值一样,除了整数值提升为BIGINT和浮点值提升到两倍;使用CAST()当插入到一个较小的数字列

原文：

zeroifnull(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 elaborate CASEexpression, to simplify porting SQL with vendor extensions to Impala.

Return type: same as the initial argument value, except that integer values are promoted to BIGINT and floating-point values are promoted to DOUBLE; use CAST() when inserting into a smaller numeric column

翻译：

用途:返回0如果数值表达式的求值结果为空,否则返回表达式的结果。
使用注意:用于避免意想不到的结果由于意想不到的传播NULL值的数值计算。作为更复杂的CASEexpression速记,简化移植与供应商扩展SQL黑斑羚。
返回类型:初始参数值一样,除了整数值提升为BIGINT和浮点值提升到两倍;使用CAST()当插入到一个较小的数字列。