SQL Filter criteria in join criteria or where clause which is more efficient

I wouldn't use performance as the deciding factor here - and quite honestly, I don't think there's any measurable performance difference between those two cases, really.

I would always use case #2 - why? Because in my opinion, you should only put the actual criteria that establish the JOIN between the two tables into the JOIN clause - everything else belongs in the WHERE clause.

Just a matter of keeping things clean and put things where they belong, IMO.

Obviously, there are cases with LEFT OUTER JOINs where the placement of the criteria does make a difference in terms of what results get returned - those cases would be excluded from my recommendation, of course.

Marc

You can run the execution plan estimator and sql profiler to see how they stack up against each other.

However, they are semantically the same underneath the hood according to this SQL Server MVP:

http://www.eggheadcafe.com/conversation.aspx?messageid=29145383&threadid=29145379

I prefer to have any hard coded criteria in the join. It makes the SQL much more readable and portable.

Readability:You can see exactly what data you're going to get because all the table criteria is written right there in the join. In large statements, the criteria may be buried within 50 other expressions and is easily missed.

Portability:You can just copy a chunk out of the FROM clause and paste it somewhere else. That gives the joins and any criteria you need to go with it. If you always use that criteria when joining those two tables, then putting it in the join is the most logical.

For Example:

FROMtable1 t1JOIN table2 t2_ABC ON  t1.c1 = t2_ABC.c1 AND  t2_ABC.c2 = 'ABC'

If you need to get a second column out of table 2 you just copy that block into Notepad, search/repalce "ABC" and presto and entire new block of code ready to paste back in.

Additional:It's also easier to change between an inner and outer join without having to worry about any criteria that may be floating around in the WHERE clause.

I reserve the WHERE clause strictly for run-time criteria where possible.

As for efficiency:If you're referring to excecution speed, then as everyone else has stated, it's redundant.If you're referring to easier debugging and reuse, then I prefer option 1.