在写sql的时候,经常会遇到和null相关的问题,经常用到虑空函数。常见的有NVL,NULLIF,COALESCE函数,其他还有很多在此不介绍了。
- 1.NVL-----
NVL
lets you replace null (returned as a blank) with a string in the results of a query. Ifexpr1
is null, thenNVL
returnsexpr2
. Ifexpr1
is not null, thenNVL
returnsexpr1
.
- 2.NULLIF------
NULLIF
comparesexpr1
andexpr2
. If they are equal, then the function returns null. If they are not equal, then the function returnsexpr1
. You cannot specify the literalNULL
forexpr1
. - 类似于:CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END
- 3.
COALESCE-----COALESCE
returns the first non-nullexpr
in the expression list. At least oneexpr
must not be the literalNULL
. If all occurrences ofexpr
evaluate to null, then the function returns null. - 类似于:CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END
- COALESCE (expr1, expr2, ..., exprn), for n>=3 类似于:CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE COALESCE (expr2, ..., exprn) END
- 4.value函数也可以用来处理null
VALUE
takes as its argument a correlation variable (table alias) associated with a row of an object table and returns object instances stored in the object table. The type of the object instances is the same type as the object table.
总结一下:
nvl,表达式1 为空,就返回表达式2。
nullif,表达式 1和表达式2如果相等,返回null,不相等返回表达式1.
coalesce,返回表达式不为空项结束。例子:coalesce(手机号,电话,邮箱) as '联系方式'
VALUE(EXPRESSION1,EXPRESSION2) 表达式1为空,返回表达式2
搬运工来自:SQL Functions