FIltering on the join?

sql-server inner-join
Craig picture Craig · Feb 9, 2011 · Viewed 31.5k times · Source

Is there any argument, performance wise, to do filtering in the join, as opposed to the WHERE clause?

For example,

SELECT blah FROM TableA a
INNER JOIN TableB b
ON b.id = a.id
AND b.deleted = 0
WHERE a.field = 5

As opposed to

SELECT blah FROM TableA a
INNER JOIN TableB b
ON b.id = a.id
WHERE a.field = 5
  AND b.deleted = 0

I personally prefer the latter, because I feel filtering should be done in the filtering section (WHERE), but is there any performance or other reasons to do either method?

Answer

btilly picture btilly · Feb 9, 2011

If the query optimizer does its job, there is no difference at all (except clarity for others) in the two forms for inner joins.

That said, with left joins a condition in the join means to filter rows out of the second table before joining. A condition in the where means to filter rows out of the final result after joining. Those mean very different things.

Related questions

How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More
SQL Server - inner join when updating

I have the below query which does not work. What am I doing wrong? Is this even possible? UPDATE ProductReviews AS R INNER JOIN products AS P ON R.pid = P.id SET R.status = '0' WHERE R.id = …

Read More
Difference between JOIN and INNER JOIN

Both these joins will give me the same results: SELECT * FROM table JOIN otherTable ON table.ID = otherTable.FK vs SELECT * FROM table INNER JOIN otherTable ON table.ID = otherTable.FK Is there any difference between the statements in performance …

Read More