Oracle: using OUTER JOIN in place of NOT IN

Using NOT IN in (Oracle) SQL is expensive and that a way to overcome this problem is to collect the non-matching rows with an OUTER JOIN.


Not In Example:

Select bookname from book
where publisherid not in
(select publisherid from publisher)

--------------------------------------------
Above query rewritten with Outer Join
Select bookname from book, publisher
where book.publisherid = publisher.publisherid (+)
and publisher.publisherid is null

Get More Query Optimization Tips:
http://nimishgarg.blogspot.in/2012/10/query-optimization-tips-for-oracle.html

No comments:

Post a Comment