Home Forums Article Discussions Article Discussions by Author Discuss content posted by Kenneth Fisher Fun with Outer Joins RE: Fun with Outer Joins

  • September 10, 2012 at 1:02 pm

    #1535884

    thisisfutile (9/10/2012)

    I really learned from this article! I've been working with SQL Server for 7 years now and I had no idea the results were different if I took a filter from the WHERE clause and put it into the ON clause...I always assumed the results were going to be the same. I personally knew the right table results were being filtered with the WHERE clause (Dr Sprite and Dr 7-Up were being excluded) because I've been down that bumpy road many times. I've always dealt with this situation by adding "ISNULL" into the where clause.

    AND ISNULL(class.classyear,2011)>=2011

    Not a pretty solution but NOW I know there is a better way!

    Thanks for the article AND it's simplicity. I get what Joe Celko was driving at, but I find simple examples to be refreshing...though it could probably be argued that is why I'm a 7 year veteran and still learning what many DBA's consider elementary.

    Using a function on a column in a where clause will not allow your query to use an index. That is why instead of isnull you want to use "Where col1 = 'a' OR Col1 IS NULL..."