NULL related Oracle functions

NVL

--
--     Demonstrating that NVL is not evaluating lazily.
--

create table tq84_a (
  a number          primary key,
  b varchar2(10)
);

create table tq84_b (
  c number          primary key,
  d number
);

create function tq84_f(a in number) return varchar2
is
begin
 
    dbms_output.put_line('function was called: ' || a);

    return '* ' || a || ' *';

end tq84_f;
/


insert into tq84_a values (1, 'one'  );
insert into tq84_a values (2,  null  );
insert into tq84_a values (3,  null  );
insert into tq84_a values (4, 'four' );

insert into tq84_b values (2,  20);
insert into tq84_b values (4,  40);

select
  substr(tq84_f(a), 1, 10) a,
  b,
  substr(nvl (
    b,
   (select tq84_f(d) from tq84_b where c = a)
  )
  ,1,10) c
from
  tq84_a
order by a;
  

drop table tq84_a purge;
drop table tq84_b purge;

drop function tq84_f;

NVL2

nvl2(tested_value, 'value if not null', 'value if null')

create table tq84_nvl2 (
  a number
);

insert into tq84_nvl2 values (null);
insert into tq84_nvl2 values (   0);
insert into tq84_nvl2 values (  42);

select
  a,
  nvl2(a, 'not null', 'null')
from
  tq84_nvl2;

drop table tq84_nvl2 purge;

LNNVL

create table tq84_lnnvl (
       col1  number,
       col2  number
);

insert into tq84_lnnvl values (null, null);
insert into tq84_lnnvl values (null,    1);
insert into tq84_lnnvl values (   1,    1);
insert into tq84_lnnvl values (   1,    2);

select * from tq84_lnnvl where lnnvl(col1=col2);
--
--       COL1       COL2
-- ---------- ----------
-- 
--                     1
--          1          2

select * from tq84_lnnvl where not lnnvl(col1=col2);
--       COL1       COL2
-- ---------- ----------
--          1          1

drop table tq84_lnnvl purge;

NULLIF

with expr as (
  select 'foo' val from dual union all
  select 'baz' val from dual union all
  select 'bar' val from dual union all
  select  null val from dual
)
select
  first.val, second.val, 
  nullif(first.val, second.val) "Null if equal, first otherwise",
  case when first.val = second.val then null else first.val end "same same"
from
  expr first, expr second
where
  first.val is not null;

See also