COALESCE:返回第一个非null的值
如果都是null,则返回null
当有A、B两列,如果要求 A ≠ null 返回 A,否则返回B的值。
大家一般都是下面的写法:
-- tgt:创建测试用临时表tgt
WITH tgt AS (SELECT NULL A ,'222' B FROM dual)
SELECT
CASE
WHEN A <> NULL
THEN A
ELSE B
END
FROM tgt
如果使用COALESCE 就会使sql简洁很多
WITH tgt AS (SELECT NULL A ,'222' B FROM dual)
SELECT COALESCE(A ,B) AS result FROM tgt
用函数decode也可以实现相同的结果
-- A = null:返回 B,否则返回A
WITH tgt AS (SELECT NULL A ,'222' B FROM dual)
SELECT decode(A ,NULL ,B,A) AS result FROM tgt
-- A = null:返回 B,否则返回A
WITH tgt AS (SELECT 111 A ,'222' B FROM dual)
SELECT decode(A ,NULL ,B,A) AS result FROM tgt
注意:null * + - / (加减乘除)任何数都是null
SELECT COALESCE(NULL ,null) + 2 AS addition FROM dual
SELECT COALESCE(NULL ,null) - 2 AS subtraction FROM dual
SELECT COALESCE(NULL ,null) * 2 AS multiplication FROM dual
SELECT COALESCE(NULL ,null) / 2 AS division FROM dual
总结:DECODE:可以判定与任何值是否相等
COALESCE:只能判断参数是否等于null