NOT IN Vs. NOT EXISTS

For some time, I have been pondering the use of NOT EXISTS in my subquery statements.

A friend of mine guided me towards this method of excluding certain records quite a while ago. So I have been using NOT EXISTS but did not quite fully understand the difference between NOT IN and NOT EXISTS.

In my particular situation a query with a four table join needed some optimizing plus certain records excluded from the query.

I played around a bit and found that using NOT EXISTS produced a substantial difference in performance. Using NOT IN on the where clause of the query took in this case 9 minutes and 35 seconds.

Using NOT EXISTS produced the results in exactly 688 milliseconds.

This is a monumental difference.

I also got a very clear understanding of what’s going on when I came across this post: NOT EXISTS vs NOT IN.

While the post ascertains that in certain circumstances, there is no difference between the two, the best thing is to test both. I will forever keep this in mind. Thanks to my colleague Bill, to Gail from SQL in the Wild and a mention to the O’Reilly book Oracle Performance Tuning which also directs you in this path. The book is authored by Mark Gurry and Peter Corrigan.

So this is what we’re talking about:

The fight between not exists and not in!

Who wins this match?

——————————————————————–
Select [columns]

From [table1]

Join [table2] on [table1.column] = [table2.column]
Join [table3] on [table1.column] = [table3.column]
Join [table4] on [table1.column] = [table4.column]

Where condition1 = ‘x’
And table1.column NOT IN ( Select [column]
From [table1] tableAlias
Where tableAlias.column = ‘xvalue’)

That took a whopping 9:35 minutes to complete.

Whereas the query below, took all of 688 milliseconds!
———————————————————————-
Select [columns]

From [table1]

Join [table2] on [table1.column] = [table2.column]
Join [table3] on [table1.column] = [table3.column]
Join [table4] on [table1.column] = [table4.column]

Where condition1 = ‘x’
And NOT EXISTS ( Select ‘xvar’
From [table1] tableAlias
Where tableAlias.column = ‘xvalue’
And tableAlias.column = table1.column)

I think in this case, we know who the winner is!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: