SQLKnowItAll (9/10/2012)
I suppose I don't understand the point. We know that when records do not exist in the RIGHT table that NULLs are returned. So why would we expect a filter condition on the RIGHT table to treat NULLs differently than we would otherwise?i.e. Simply change the filter condition to account for NULLs
WHERE Professor.HasTenure = 'True'AND (Class.ClassYear >= 2011 OR Class.ClassYear IS NULL)
id say because the above query is the classic catch all. if we can avoid the catch all performance should increase. (as always the it depends please test applies).
if we have an index on Class with ProfessorID and ClassYear we can use that index in the join. we also eliminating records we dont need early in execution and have a lower overall row count.
To Kenneth, Great article and a gave me a great way to explain join conditions vs where conditions.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]