June 11, 2015 at 8:34 am
I am curious as to which of my two options would generally perform better when dealing with a table with 5+ million rows. Option 1 is the left join TABLE A to TABLE B on the key and then say WHERE TABLE B's key IS NULL. Option 2 is to have a WHERE clause using NOT EXISTS( select key from TABLE B where B.key = A.key)
From what I understand, using a NOT condition makes SQL unable to use indexes effectively. This would lead me to think that option 2 is not as good as option 1, where the two tables are joined on the index and then filtered. However, option 1 requires a much larger dataset prior to filtering...
Any thoughts?
BTW, I did a search on this topic and found lots of stuff on EXISTS but not a lot on NOT EXISTS.
June 11, 2015 at 8:45 am
NOT EXISTS is generally the more performant, because of the short-circuiting built into that construction.
But you should verify it for yourself by testing and examining execution plans.
June 11, 2015 at 8:51 am
Gail Shaw has a series of articles on her blog where she tests this options. This specific test is done here: http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/
However, I would insist on following Phil's advice on doing some testing of your own.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply