July 11, 2008 at 3:41 am
When joining two tables I know I can use either:
1) a JOIN statement such as INNER JOIN or,
2) have no JOIN statement and just use the WHERE statement to specify that the joining columns equal each other.
They give the same result but is there a good reason to use one instead of the other?
Thanks, Bill
July 11, 2008 at 4:36 am
Hi,
The two techniques are logically equivalent but can have vastly different performance hits depending on how the parser
plans the query.
Not where clause replace Join nor vice versa.
Where clause works on same or between multiple tables but join works on always different table except the self join case.
Join has multiple types, inner, left outer, right outer join while where clause not
Where clause uses multiple comparison operator while join not.
July 11, 2008 at 5:37 am
You can do an outer join with your join information in the where clause.
However, use the JOIN keyword. The old syntax with tables spearated by commas and the join information in the WHERE clause was replaced by the new syntax in the ANSI standard. The old syntax is deprecated and will eventually be completely unsupported.
July 11, 2008 at 5:41 am
As far as performance. The optimization engine will generate the same execution plan for equivalent queries regardless of which syntax you use.
July 11, 2008 at 5:48 am
Michael Earl (7/11/2008)
You can do an outer join with your join information in the where clause.However, use the JOIN keyword. The old syntax with tables spearated by commas and the join information in the WHERE clause was replaced by the new syntax in the ANSI standard. The old syntax is deprecated and will eventually be completely unsupported.
It already is (at least the outer join syntax) In compat mode 90, the following fails
SELECT * FROM sys.objects o, sys.tables t
WHERE o.OBJECT_ID *= t.object_id
Msg 4147, Level 15, State 1, Line 2
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2008 at 6:58 am
Michael Earl (7/11/2008)
As far as performance. The optimization engine will generate the same execution plan for equivalent queries regardless of which syntax you use.
Not for OUTER JOINS though. With OUTER JOINS, you need to be sure that the JOIN criteria is all that is included in the ON clause, because placing other filtering information there can change the result set and execution plans.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 11, 2008 at 7:51 am
That would not be an equivalent query.
Of course if you write one of them wrong, they will get different execution plans.
July 11, 2008 at 8:12 am
Thanks everyone. Looks like if it's likely to be the same speed and is likely to become unsupported I should stick to using JOINs only.
I think SQL that uses JOIN is easier to read and understand too.
All the best,
Bill
July 14, 2008 at 11:26 am
There is a slight difference in that the Where clause is evaluated after the Joins have happened. So there are cases where the old syntax was much more difficult to get results from.
Additionally, there is the case I used to call the inner-outer join. Suppose you have tables Employee(eid, name), Vehicle(cid, make, model, yr), EmployeeVehicle(eid, cid) and are trying to produce a report of all employees and the vechicle assigned to them, if any. The old syntax did not allow EmployeeVechicle to Inner Join to vehicle since it was outer joined to Employee. The new syntax can handle this easily.
Derived tables have changed the landscape from the old syntax, as well.
July 15, 2008 at 12:06 am
brdudley (7/14/2008)
There is a slight difference in that the Where clause is evaluated after the Joins have happened.
Order of clauses depends on the plan the opimiser chooses. Sometimes the where predicates are pushed right down, sometimes the filter is done after the join.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply