sql - Why does NVL2 returns NULL when it's not expected? -

let preparation structure be

create table resource1(id number, valuea varchar2(255)); create table resource2(id number, valueb varchar2(255)); create table ids(id number);  insert ids   select 1 dual   union select 2 dual   union select 3 dual   union select 4 dual;  insert resource1       select 1, 'abc' dual union select 2, 'def' dual;  insert resource2       select 3, 'ghi' dual union select 4, 'jkl' dual; 

the following query

select p.valuea, q.valueb      , nvl2(p.valuea, q.valueb, p.valuea) from_nvl2       , case when p.valuea null q.valueb else p.valuea end from_case   ids   left join resource1 p on p.id = ids.id   left join resource2 q on q.id = ids.id     order ids.id; 

produces

valuea  valueb  from_nvl2   from_case  abc    (null)    (null)       abc  def    (null)    (null)       def (null)   ghi      (null)       ghi (null)   jkl      (null)       jkl 

why from_nvl2 column contain nulls? i've been expecting from_nvl2 result in same values from_case did.

the logic nvl2() described as:

nvl2 lets determine value returned query based on whether specified expression null or not null. if expr1 not null, nvl2 returns expr2. if expr1 null, nvl2 returns expr3.

the equivalent case be:

(case when p.valuea not null q.valueb else p.valuea end)