First I would like to appologize if my choice of test data is throwing people off. The data and table layout was meant to be simple and easy to follow so it didn't get in the way of the description of the difference between the ON clause and the WHERE clause.
Second I'll address a few points.
The correct terms are "preserved table " and "unpreserved table"
Never heard of either. What are they the correct terms for?
and your design is wrong.
My design is correct for the purpose. It's simple. I didn't need or want a complicated design to demonstrate a subject that can be complicated enough for a lot of people.
we do not use BIT flags in SQL
"We" do use BITs quite often. They are a nice small data type where I can fit 8 of them into a byte. Unless you have a good reason not to use them that I've never heard of I think I'll continue to not waste disk space.
Your life will be easier if you learn to use row constructors:
INSET INTO Professors
VALUES (1, 'Dr Coke', '2011-01-01'),
(2, 'Dr Sprite', '2011-01-01'),
(3, 'Dr 7-up', '2011-01-01'),
(4, 'Mr Pepper', NULL),
(5, 'Mr Fanta', NULL);
I do in fact know how to use this method of inserting. However it only works on 2008 and above and not everyone (believe it or not) is done using 2005 (and even lower).
Now try your queries with a proper schema.
My queries work exactly the same. Which is why I chose to use a very simple table layout. If I had chosen to make a realistic schema I could have added quite a few more columns, tables, defaults, constraints, foreign keys etc. That not being the point of the article I chose not to.
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)
You can absolutly do it this way. Personally I prefer the other, and I do consider the ClassYear part of the join in this case. But that comes down to personal preference I think. I will say that you should consider yourself lucky to understand it so well. I still get around 1 or 2 questions a week on this subject. Which is why I wrote the article in the first place. It tends to be a difficult subject for some people. Not everyone, but in my experience the vast majority.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]