Greg Dodd unleashes a monster:
We now have all of the data, but the question is, how do we stop the Person’s name coming back if the HireDate is after 1 Jan 2000?
We can’t simply put it in a where clause, because then the Course Title won’t come back. We could put it as part of the join condition to Instructor, but that will only limit the hire date coming back. We could use that to build a case statement around the FirstName and LastName fields, but what other options are there?
What we want to do is limit the join to Person to only return if Instructor returns, and then we could put the join condition on Instructor.
I’ll admit that whenever I see this syntax, I tend to remove it and replace with a subquery or APPLY operation. I don’t like multi-joins at all for the reasons Greg mentions at the end: it’s uncommon and difficult for a T-SQL developer to parse mentally.