Excellent article. You touched on it briefly when you referred to the "INNER JOIN equivalent," but it should be made clear that referencing a column from the right table in the WHERE clause does not literally change the LEFT JOIN to an INNER... it simply makes it appear to behave like one.
Since NULL comparisons always return false, the query is behaving exactly as one would expect a LEFT JOIN with a WHERE clause to behave. We have written the query incorrectly in regards to our intent, but it hasn't formally become an INNER JOIN simply because the result sets look the same.
This can be illustrated by changing the WHERE clause in your second query to...
WHERE ISNULL(OH.TotalDue, 5001) > 5000
If the JOIN is now literally an INNER, we should not see different results from your second example, as we are now INNER JOINing ON C.CustomerID = OH.CustomerID. But instead we get the same results as the third example, indicating that the join is still a LEFT; we've simply corrected the error in the WHERE clause.
All that being said, this is still very poor practice and all db developers should know to avoid it. So I'm not disagreeing with you at all, just offering a technical clarification.
commented on Mar 30 2010 7:53AM