在寫sql的時候,經常會遇到和null相關的問題,經常用到慮空函數。常見的有NVL,NULLIF,COALESCE函數,其他還有很多在此不介紹了。
- 1.NVL-----
lets you replace null (returned as a blank) with a string in the results of a query. IfNVL
is null, thenexpr1
returnsNVL
. Ifexpr2
is not null, thenexpr1
returnsNVL
.expr1
- 2.NULLIF------
comparesNULLIF
andexpr1
. If they are equal, then the function returns null. If they are not equal, then the function returnsexpr2
. You cannot specify the literalexpr1
forNULL
.expr1
- 類似于:CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END
- 3.
returns the first non-nullCOALESCE-----COALESCE
in the expression list. At least oneexpr
must not be the literalexpr
. If all occurrences ofNULL
evaluate to null, then the function returns null.expr
- 類似于: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
-
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.VALUE
總結一下:
nvl,表達式1 為空,就傳回表達式2。
nullif,表達式 1和表達式2如果相等,傳回null,不相等傳回表達式1.
coalesce,傳回表達式不為空項結束。例子:coalesce(手機号,電話,郵箱) as '聯系方式'
VALUE(EXPRESSION1,EXPRESSION2) 表達式1為空,傳回表達式2
搬運工來自:SQL Functions