x

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 < '20080701'
            and st.small_thing_type = '2094'

I wondered why they couldn't just write it like normal people (i.e. me) and say:

SELECT bt.blah
       ,st.bleep
FROM   big_things bt
       JOIN small_things st
       ON   bt.big_things_id = st.big_things_id
WHERE  st.start_date >= '20070701'
       and st.end_date < '20080701'
       and st.small_thing_type = '2094'

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:

SELECT bt.blah
       ,st.bleep
FROM   big_things bt
       LEFT JOIN small_things st
            ON   bt.big_things_id = st.big_things_id
                 and st.start_date >= '20070701'
                 and st.end_date < '20080701'
                 and st.small_thing_type = '2094'

To write the equivalent in a WHERE clause, you'd have to write:

SELECT bt.blah
       ,st.bleep
FROM   big_things bt
       left JOIN small_things st
            ON   bt.big_things_id = st.big_things_id
WHERE  (st.start_date >= '20070701' OR st.start_date is null)
       and (st.end_date < '20080701' OR st.end_date is null)
       and (st.small_thing_type = '2094' OR st.small_thing_type is null)
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?
more ▼

asked Sep 23, 2010 at 01:14 PM in Default

GPO gravatar image

GPO
2.1k 34 37 41

(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

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

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 < '20080701'
        and     st.small_thing_type = '2094') st_ilv
  ON   bt.big_things_id = s_ilvt.big_things_id
more ▼

answered Sep 23, 2010 at 01:56 PM

Kev Riley gravatar image

Kev Riley ♦♦
53.1k 47 49 76

...or even a CTE I suppose, which to my eye seem to be easier to read than ILVs.
Sep 24, 2010 at 06:07 PM GPO
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Sep 23, 2010 at 01:55 PM

Mark gravatar image

Mark
2.6k 23 25 27

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x987
x118
x17

asked: Sep 23, 2010 at 01:14 PM

Seen: 1717 times

Last Updated: Sep 23, 2010 at 02:32 PM