Filter on Joins vs Where clause?

  • Im trying to optimzae my coding and just wondering if its ok to do most of my filtering conditions in my joins as opposed to the where clause. Ideally eliminating the where clause altogether assuming I can. Is their any benefit of filtering data on joins vs the where clause? For example

    select *

    from table a

    inner join tabe b

    on a.id=b.id

    and a.column='x'

    and a.column=x

    vs

    select *

    from table a

    inner join tabe b

    on a.id=b.id

    where a.column='x'

    and a.column=x

  • The Query processor applies filters on Where and JOin's at different times in the process. When you are doing INNER JOIN's you will probably be okay, but with OUTER JOIN's where you put your criteria can change the results.

    In my opinion, Filter criteria should be put in the WHERE clause, except for when you are filtering the right side of a LEFT JOIN.

  • Makes sense. I just figured since the join clause is evaluated before the where clause it would be a good idea to do as much filtering as possible on it.

  • craig (11/13/2008)


    Makes sense. I just figured since the join clause is evaluated before the where clause it would be a good idea to do as much filtering as possible on it.

    For inner joins - it won't matter at all. SQL Server will build the same execution plan (in most cases). I am not aware of any specific cases where the execution plan would be different - but, I am sure there are some.

    As was stated before - for outer joins you have to place the filtering in the join clause, else the statements would be equivelant to an inner join anyways.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply