Get unmatched records without using oracle minus except not in

Question

Actually I have two table and each having column name, I just want the result which are not there in Table2

Table1
----
Name
---
|A|
|B|
|C|
|D|


Table2
------
|Name|
-----
|A|
|B|

Answer |C| |D|

I am able to do it by using minus

select name from table1
minus
select name from table2

 select name from table1 where name
    not in (
    select name from table2) 

But my Manager ask me to do it with other alternate solution without using minus,except,not in. Is there a way to do that, It will be great if someone can help me on it. I need to do it with oracle pl/sql

Answer 1

The one option left with you is using NOT EXISTS

SELECT t1.name 
  FROM table1 t1 
 WHERE NOT EXISTS (SELECT 'X' 
                     FROM table2 t2 
                    WHERE t2.name = t1.name);

Update: Using Join

with table_ as 
(
  select t1.name t1_name, t2.name t2_name
    from table1 t1
    left join table2 t2 
      on t1.name = t2.name)
select t1_name 
  from table_
 where t2_name is null;

Or just

select t1.name
  from table1 t1
  left join table2 t2 
    on t1.name = t2.name
 where t2.name is null;

Answer 2

Another alternative is to use an outer join and then filter rows that don't have a value in the 2nd table:

with t1 as (select 'A' name from dual union all
            select 'B' name from dual union all
            select 'C' name from dual union all
            select 'D' name from dual),
     t2 as (select 'A' name from dual union all
            select 'B' name from dual)
select t1.name
from   t1
       left outer join t2 on (t1.name = t2.name)
where t2.name is null;

NAME
----
D   
C