asked
JOIN versus WHERE
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: SELECT bt.blah ,st.bleep FROM big_things bt JOIN small_things st ON bt.big_things_id = st.big_things_id and st.start_date >= '20070701' and st.end_date = '20070701' and st.end_date = '20070701' and st.end_date = '20070701' OR st.start_date is null) and (st.end_date
You need to be careful about mixing up JOIN conditions and WHERE filters. There's a similar discussion here , and I'll repeat here what I said there.... It is best practice to use JOIN to specify the join conditions BOL: >Specifying the join conditions in the FROM clause helps separate them from any other search conditions that may be specified in a WHERE clause, and is the recommended method for specifying joins 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 SELECT bt.blah ,st_ilv.bleep FROM big_things bt LEFT JOIN (select st.big_things_id, st.bleep from small_things st where st.start_date >= '20070701' and st.end_date
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.
