capn.hector (9/10/2012)
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.
I don't see it as a catch all. I see it as defining the requirements of the query. The join condition is the id, the filter is the tenure and the year. Assessing the question posed, I wouldn't expect to see the results that the author posed; i.e. if class year is NULL, then they didn't teach that year and I don't need them in the results. I just don't understand why the expected results would be different. If someone has been working with LEFT OUTER JOINs for more than 20 minutes... why would they ever expect the query to return rows from the LEFT table when the filter is on the RIGHT? I don't put the filter in the JOIN because it is a filter, not a join condition.
Jared
CE - Microsoft