I'm mainly writing this to get a bit of validation for some assumptions that I'm making. Please correct me if anything I say here is wrong. For a while I couldn't understand why people put a lot of WHERE-style restrictions in the JOIN part of the FROM clause. For example:
I wondered why they couldn't just write it like normal people (i.e. me) and say:
Surely they're equivalent? Well, yes and no, as it turns out. Yes the above three restrictions have the same effect whether they're in the JOIN or in the WHERE, but this is not always the case. Consider this:
To write the equivalent in a WHERE clause, you'd have to write:
So my assumption is that people put the WHERE stuff in the JOINs simply to be succinct when using outer joins. And then keep doing it with inner joins to keep the code consistent. Am I on the right track here or are there other gotchas to be aware of?
You need to be careful about mixing up JOIN conditions and WHERE filters.
There's a similar discussion here http://ask.sqlservercentral.com/questions/7290/and-clause-in-where-vs-join, and I'll repeat here what I said there....
It is best practice to use JOIN to specify the join conditions
In most cases it will make the query clearer as to what the JOIN conditions are, rather than what the WHERE filter is specified as.
Execution plans may hardly differ (especially for inner joins), but your understanding of the query might.
Also as you have shown, changing from an INNER JOIN to an OUTER JOIN, severely changes the meaning of the query if you specify conditions in the JOIN clause rather than the WHERE clause.
--- Edit : just a thought
Another way of writing the left join, but making it crystal clear what is being used as a filter and what is being used as a join condition, is to filter the table first, using an inline view, or derived table, and then join on that
Ideally, you could use a primary key, or at least foreign key(s), to join any needed tables and put the rest in the WHERE clause. When you're joining tables, think of the columns that need to join to make the relation work right, then put the rest in the where clause. In effect, the JOIN only makes the tables work together as one table.For example, to me it doesn't make sense to include the dates in the JOIN part since it has nothing to do with the relation between the two tables; you're just limiting the information you want to see with the dates. So the date qualifications need to be in the WHERE clause.
answered Sep 23 '10 at 01:55 PM