[SQL] COALESCE and NVL

[COALESCE]

• A part of ANSI-92 standard.
• 回傳第一個不為 null 的值。

COALESCE(expression1,...n) 等於下列 CASE 運算式：
CASE
    WHEN (expression1 IS NOT NULL) THEN expression1
    WHEN (expression2 IS NOT NULL) THEN expression2
    ...
    ELSE expressionN
END


[NVL]

• NVL(expression1,expression2)
• expression1 ? expression1 : expression2
• 若 expression1 不為 null 則回傳 expression1，若為 null 則回傳 expression。
• Is Oracle specific, it was introduced in 80's before there were any standards.

In case of two values, they are synonyms. However, they are implemented differently.

* Reference
- Oracle Differences between NVL and Coalesce
- COALESCE (Transact-SQL)
- Coalesce Function
- SQL 設計小技巧--用 ISNULL 或 NVL 達到選擇性條件的下法
- PL/SQL NVL(轉換null)，如何取代Null?