Does minus operator and not exist result in same result in oracle

Question

Can anyone help to confirm that in oracle minus operator and not exists are returning same result set?

Regards, Mayuran

Answer 1

Here's proof that the minus and the not exist queries do not return the same results:

Minus Query

with t1 as (select 1 id, 'a' val from dual union all
            select 1 id, 'a' val from dual union all
            select 2 id, 'b' val from dual),
     t2 as (select 2 id, 'b' val from dual union all
            select 3 id, 'c' val from dual)
select id, val
from   t1
minus
select id, val
from   t2;

Minus Results

        ID VAL
---------- ---
         1 a  

Not Exists Query

with t1 as (select 1 id, 'a' val from dual union all
            select 1 id, 'a' val from dual union all
            select 2 id, 'b' val from dual),
     t2 as (select 2 id, 'b' val from dual union all
            select 3 id, 'c' val from dual)
select id, val
from   t1
where  not exists (select null
                   from   t2
                   where  t1.id = t2.id
                   and    t1.val = t2.val);

Not Exists Results

        ID VAL
---------- ---
         1 a  
         1 a