sql - NVL faster than is not null condition? -

i asking myself if nvl funtion faster not null condition. searched on google if talk found nothing respond @ question.

so here's situation have sql request , want know faster. in example simplify query have it's give idea of want know.

here's request nvl function

select ta.mnt tablea ta ta.id not in(select nvl(tb.id,-1) tableb tb);

here's not null condition

select ta.mnt tablea ta ta.id not in(select tb.id tableb tb tb.id not null);

so 1 return faster?

they're not same logic.

consider start null, 0, 1 in tableb.
- nvl version changes -1, 0, 1
- where not null changes 0, 1.

this means not in has fewer items check in where not null, that's why can faster.

that said, using not in not fastest. 2 standard options left join , not exists.

select   tablea.*   tablea left join   tableb     on tableb.id = tablea.id   tableb.id null

select   tablea.*   tablea   not exists (select *                 tableb                tableb.id = tablea.id              )

YU