SQL Server Performance

JOIN Condition Vs WHERE clause

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by atulgoswami, May 26, 2009.

  1. atulgoswami New Member

    Hi,
    I read few posts where it is mentioned that putting condition within JOIN has some performance gain over placing the condition in WHERE clause.
    (1)
    Select a.name,b.deptname
    from EMP a INNER JOIN DEPT b ON a.deptid=b.deptid and a.cityid = 21

    OR

    (2)
    Select a.name,b.deptname
    from EMP a INNER JOIN DEPT b ON a.deptid=b.deptid
    WHERE a.cityid = 21

    I tried to check through execution plan and could not find any difference however after clearing the buffer, ran both the queries and query#2 took less time.
    The time difference was in 20 milli seconds with 700 records.

    Can i get some information on which one is better in terms of performance with very large data?
    Thanks
    Aero
  2. Adriaan New Member

    It depends - is CityID on your EMP table indexed? Is there much variation in the values on that column?
  3. Sandy New Member

    Hi Aero,
    If you analyze your above query in terms of Execution Plan there is absolutely no difference. But yes as Adriaan told it depends and also the column is used in where or join filter is indexed or not.
    But I have a strange experience that may not be correct, In one of my query I have used filter condition in where clause but it was not returning data as per my requirements but when I added that same condition in my JOIN statement I got my required result set.
    So I think May be JOIN condition filters the JOIN too.
    Thanks,
    Sandy.
  4. FrankKalis Moderator

    I think in the case of an INNER JOIN it all boils down to "personal preferences". Not sure how you tested, but I wouldn't say that 20 ms are a significant difference, but when you examine the execution plan, you're likely to see that the optimizer has rewritten both versions to an INNER JOIN construct anyway. In that case, execution (and therefore performance) are the same.
    Things are different with OUTER JOINs. There it does have an effect on the query result if you put a condition into the JOIN clause or into the WHERE clause. It is up to your requirement in such cases where a condition goes to.
    And because it does have this effect I tend to specify the JOIN condition in the JOIN clause and the filtering condition in the WHERE clause throughout my code. That way it is more consistent, easier to read and maintain. And it still should give good performance. [:)]
  5. atulgoswami New Member

    I think then putting condition in inner join has some advantage as optimizer does not need to change anything but in case of condition in where clause, optimizer has to rewrite this again.
    What do you say?
    I agree that this is true only for inner join.
    Thanks
  6. FrankKalis Moderator

    [quote user="atulgoswami"]
    I think then putting condition in inner join has some advantage as optimizer does not need to change anything but in case of condition in where clause, optimizer has to rewrite this again.
    What do you say?
    [/quote]
    There might be a slight advantage during compilation phase when the execution plan is generated, but once this is done, there is no difference any longer. I'm not sure if that slight advantage is significant though.
  7. moh_hassan20 New Member

    Although the syntax is different, the optimizer treat them with the same way
    Optimizer transform the syntax into an equivalent internal representation before executing the query, so the different syntax makes no execution effect.
    try to see the predicate property (which is how the optimizer search) In execution plan for the two queries , you will find them the same


    your case 1 match the Ansi Standard (SQL-92) (which is recommended for most engines)

Share This Page