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

avatar image

GPO
4.8k 40 49 56

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

2 answers: sort voted first

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

avatar image

Kev Riley ♦♦
64k 48 61 81

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

avatar image

Mark
2.6k 24 27 31

(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.

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:

x1066
x149
x31

asked: Sep 23, 2010 at 01:14 PM

Seen: 2206 times

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

Copyright 2016 Redgate Software. Privacy Policy