Get rows in one table that are not in other based on two columns in oracle sql-CodePudding

I have data in two tables as below. --Table1

Col1	col2
1	2
3	4
6	7
9	10

--Table2

col1	col2
1	2
3	5
8	7
11	12

I need to fetch only the rows in table1 that dont exist in table2 i.e. i need to filter out the data where

both col1 and col2 have matching data in both tables
either col1 or col2 has matching data in both tables

and fetch only the remaining rows. In the data set shown here i should be fetching only the following row. Need to fatch only the values 9 and 10 that are present in table1 and not in table2 How can i achieve this oracle sql?

col1	col2
9	10

CodePudding user response：

Simply use NOT EXISTS:

select t1.*
from t1
where not exists (select * from t2
                  where t2.c1 = t1.c1
                     or t2.c2 = t1.c2)

CodePudding user response：

I'd suggest something like this, faster than the "not exists" which runs subqueries - can be costly with large tables:

select t1.*
  from table1 t1 
  left join table2 t2 
    on t1.col1 = t2.col1 or t1.col2 = t2.col2
 where t2.col1 is null

HTH