CODEWITHSUNDEEP
sqloracle-database
Ion C
Ion C

Reputation: 323

"Where not exists" vs "left outer join" in oracle sql

I have a simple SQL query:

SELECT  
       columnA, columnB, columnC...  
 FROM  
       (SELECT    
             columnA, columnB, columnC...  
        FROM   
              SomeTable) Table1  
  WHERE NOT EXISTS  
        (SELECT   
               columnA  
          FROM  
               SomeOtherTable st  
          WHERE  
               st.columnB = Table1.columnB)

Can anyone give me a suggestion how to rewrite this query for better performance? I mean to include the WHERE NOT EXISTS clause in Table1.

Upvotes: 3

Views: 22866

Answers (2)

TechDo
TechDo

Reputation: 18659

How about this:

SELECT columnA, columnB, columnC... 
FROM SomeTable
WHERE (SELECT COUNT(*) FROM  SomeOtherTable st  WHERE  st.columnB = SomeTable.columnB)=0;

Upvotes: 1

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174397

You can use this:

select Table1.*
from (select * from SomeTable) Table1
left outer join SomeOtherTable sot
    on Table1.columnB = sot.columnB
where sot.columnB is null;

For the performance it is important to have indexes on columnB on both tables.

Upvotes: 7

Related Questions