WHERE NOT EXISTS in the Twelve Days of SQL

When I first saw The Twelve Days of SQL series announced by Brent Ozar (Blog|Twitter), I wondered what my favorite post of the year had been.  It didn’t take me long to decide, and after that it was just a matter of seeing whether any of the twelve participants would have the same choice as me.

One participant came close.  On the tenth day of SQL, Crys Manson (Blog|Twitter) chose a piece by Gail Shaw (Blog|Twitter) as her favorite blog post of the year.  My favorite blog post of the year was one of Gail’s, but a different one… well, not just one post, but a series of five on the same topic.  This series compared SQL clauses that seem similar but perform in different ways.

Gail’s series actually started in 2009 with one post, but the remaining four were in 2010.  The five posts in the series were:

  1. EXISTS vs IN
  2. IN vs INNER JOIN
  3. NOT EXISTS vs NOT IN
  4. LEFT OUTER JOIN vs NOT EXISTS
  5. A roundup of the four posts above.

I always enjoy Gail’s writings, and this series was quite thorough.  The material builds on what came before it with lots of example code and execution plans.  Gail clearly states the similarities and differences between alternative clauses, when it’s best to use one versus or another, or if it doesn’t matter much.

After reading the fourth post in Gail’s series, I began reviewing stored procedures at work and doing my own comparisons using actual data (on test systems, of course).  Results always followed what Gail had reported.  I ended up changing quite a few queries and we saw some nice improvements in performance.

So if you are looking for something to do with the New Years holiday weekend, you can follow all of the links above to find some good reading material (start with Brent’s Twelve Days of SQL post if you weren’t following it earlier this month) .  Oh, and if you haven’t done a recap of your 2010 goals, I highly recommend it, even if you don’t share your findings.

In case this is my last blog post of the year, have a Happy New Year and wonderful 2011!

About Noel McKinney

Noel McKinney is the administrator of noelmckinney.com
This entry was posted in SQL Server and tagged , . Bookmark the permalink.

3 Responses to WHERE NOT EXISTS in the Twelve Days of SQL

  1. Gail Shaw says:

    Thank you for the kind words.

    You may be interested to know there are two more posts on IN/Exists coming (one planned, one written and scheduled)

  2. Pingback: Tweets that mention New Blog: WHERE NOT EXISTS in the Twelve Days of SQL #sqlserver -- Topsy.com

Comments are closed.