t-sql condition placement

Should SQL Server yield the same results for both of the queries below? The primary difference is the condition being placed in the WHERE clause with the former, and with the latter being placed as a condition upon the join itself.

SELECT *
FROM cars c
INNER JOIN parts p
   ON c.CarID = p.CarID
WHERE p.Desc LIKE '%muffler%'

SELECT *
FROM cars c
INNER JOIN parts p
   ON c.CarID = p.CarID
   AND p.Desc LIKE '%muffler%'

Thanks in advance for any help that I receive up开发者_Python百科on this!

For INNER JOINS it will make no difference to semantics or performance. Both will give the same plan. For OUTER JOINs it does make a difference though.

/*Will return all rows from cars*/
SELECT c.*
FROM cars c
LEFT JOIN parts p
   ON c.CarID = p.CarID AND c.CarID <> c.CarID


/*Will return no rows*/
SELECT c.*
FROM cars c
LEFT JOIN parts p
   ON c.CarID = p.CarID 
WHERE c.CarID <> c.CarID

For inner joins the only issue is clarity. The JOIN condition should (IMO) only contain predicates concerned with how the two tables in the JOIN are related. Other unrelated filters should go in the WHERE clause.

For inner joins the two queries should yield exactly the same results. Are you seeing a difference?

Yes, they both get the same results. The difference is when the condition is checked, if during the join or afterwards.

The execution plan will be identical in your example. Next to the parse button should be the "Show execution plan" button. It will give you a clearer picture.

I think in a more complex query with many joins it can be an issue in efficiency, as stated above, before or after.

EDIT: sorry assuming your using sql server management studio.

My recommendation for this kind of situation would be:

• put the JOIN condition (what establishes the "link" between the two tables) - and only that JOIN condition - after the JOIN operator

• any additional conditions for one of the two joined tables belongs in the regular WHERE clause

So based on that, I would always recommend to write your query this way:

SELECT
     (list of columns)
FROM 
     dbo.cars c
INNER JOIN 
     dbo.parts p ON c.CarID = p.CarID
WHERE 
     p.Desc LIKE 'muffler%'

It seem "cleaner" and more expressive that way - don't "hide" additional conditions behind a JOIN clause if they don't really belong there (e.g. help establish the link between the two tables being joined).