CODEWITHSUNDEEP
sqloracle-database
kyleqian
kyleqian

Reputation: 385

'not exists' and 'not in', which one has better performance?

select objectid
      ,name
      ,address
 from library_t l 
 where not exists (select *
                     from d107 d 
                    where l.objectid = d.objectid
                   )

select objectid
      ,name
      ,address
  from library_t l 
  where l.objectid not in (select objectid 
                             from d107
                          )

If both objectid columns have indexs, which one is better in oracle?

Upvotes: 4

Views: 13742

Answers (3)

Nick Krasnov
Nick Krasnov

Reputation: 27261

[NOT] IN and [NOT] EXISTS operators are processed differently. [NOT] IN is processed more like a join whereas [NOT] EXISTS is processed more like a loop with IF condition. Choosing one over another, of course, depends on a situation: on volume of data that driven and driving queries return. In the case of [NOT] IN operator inner query (..where id in (select id from table )) is a driving query whereas in the case of [NOT] EXISTS outer query is a driving query. So if the sub-query (inner query) returns small amount of data because of either a table in the sub-query contains small number of rows or there is an intensive filtering applied to the sub-query [NOT] IN operator may give better performance. If the sub-query returns large volume of data or the major filtering is happening in outer-query [NOT] EXISTS operator is preferable.

Upvotes: 3

Marlin Pierce
Marlin Pierce

Reputation: 10099

Both of them do a subquery. Faster would be:

select lib.objectid, lib.name, lib.address
from library_t lib
     left outer join d107 dd on lib.objectid = dd.objectid
where dd is null

Upvotes: 2

John Woo
John Woo

Reputation: 263933

Use NOT EXISTS.

The execution plans may be the same at the moment but if either column is altered in the future to allow NULLs the NOT IN version will need to do more work.

The most important thing to note about NOT EXISTS and NOT IN is that, unlike EXISTS and IN, they are not equivalent in all cases. Specifically, when NULLs are involved they will return different results. To be totally specific, when the subquery returns even one null, NOT IN will not match any rows.

Upvotes: 13

Related Questions