sql - 为什么 NVL2 在预期之外返回 NULL？

Question

让准备结构为

create table RESOURCE1(id number, valueA varchar2(255));
create table RESOURCE2(id number, valueB varchar2(255));
create table IDS(id number);

insert into IDS
  select 1 from DUAL
  union select 2 from DUAL
  union select 3 from DUAL
  union select 4 from DUAL;

insert into RESOURCE1
      select 1, 'ABC' from dual
union select 2, 'DEF' from dual;

insert into RESOURCE2
      select 3, 'GHI' from dual
union select 4, 'JKL' from dual;

以下查询

select P.VALUEA, Q.VALUEB
     , NVL2(P.VALUEA, Q.VALUEB, P.VALUEA) FROM_NVL2 
     , case when P.VALUEA is null then Q.VALUEB else P.VALUEA end FROM_CASE
  from IDS
  left join RESOURCE1 P on P.ID = IDS.ID
  left join RESOURCE2 Q on Q.ID = IDS.ID
    order by ids.id;

产生

VALUEA  VALUEB  FROM_NVL2   FROM_CASE
 ABC    (null)    (null)       ABC
 DEF    (null)    (null)       DEF
(null)   GHI      (null)       GHI
(null)   JKL      (null)       JKL

为什么 FROM_NVL2 列包含所有空值？我一直期待 FROM_NVL2 会产生与 FROM_CASE 相同的值。

Answer 1

NVL2() 的逻辑是 described作为:

NVL2 lets you determine the value returned by a query based on whether a specified expression is null or not null. If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3.

等效 case将是:

(case when P.VALUEA is not null then Q.VALUEB else P.VALUEA end)