Criteria in Join (1 Viewer)

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:02
Joined
Jan 20, 2009
Messages
12,849
SQL Server (2008R2 at least) queries support joins I have not seen before today when I discovered it in somoeone else's stored procedure.

Criteria that would normally be in the Where clause can be included in the Join statement. (No. Access doesn't support it if you were wondering.;))

Are there any advantages or disadvantages to this construct over using a Where condition?

Code:
SELECT *
FROM tableA
JOIN tableB
ON tableA.somefield = tableB.somefield AND tableB.anotherfield = 1
 

SQL_Hell

SQL Server DBA
Local time
Today, 16:02
Joined
Dec 4, 2003
Messages
1,360
Hello,

Yes, it is generally quicker because the criteria is dealt with at join time rather than join and then ommit through where clause and personally I think it's more readable.
 

ButtonMoon

Registered User.
Local time
Today, 16:02
Joined
Jun 4, 2012
Messages
304
Are there any advantages or disadvantages to this construct over using a Where condition?
For an INNER join (like the example you give here) there is no difference. A condition in the ON clause gets treated the same as if you put it in the WHERE clause. Compare the execution plans to be sure.

In the case of an OUTER join there may be a big difference. Logically speaking an OUTER JOIN query has to behave as though anything in the ON clause of an OUTER join is evaluated before the WHERE clause. Moving a condition from ON to WHERE or vice versa when an OUTER join is involved may change the results of the query. As far as performance is concerned the query optimizer may rewrite the actual order of operations behind the scenes, just as it may do for any query.
 

Users who are viewing this thread

Top Bottom