Where statement Joins vs join statement joins
-
Can anyone tell me if there is any performance difference in joining tables via a where statement or via join statements.
i.e. select columns from t1,t2 where t1.id = t2.id
or
select columns from t1 inner join t2 on (t1.id = t2.id)
-
Not always. But when there is the inner join usually wins. It's also more obvious of what you want to do and it also avoids hidden cross joins!!! which can be a huge problem with returning incorrect data.
-
I've never found a performance difference between the Ansi INNER JOIN and the Equi-joins found in a WHERE clause. And, contrary to what Remi has experienced (not an argument, just a different experience), I've found that all forms of joins are subject to cross-join mistakes when written by folks who don't really pay attention when writing the joins. I agree that there should be less of a chance in writing accidental cross-joins using INNER JOIN... it's just not what I've observed.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The most important thing about the 'old' syntax is that it will not be supported in the future per Microsoft. The *= and =* is already unsupported in SQL 2005.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
TheSQLGuru has the best reason. The old syntax will get dropped.
I think the inner join syntax reduces mistakes and it's easier to read, but that's personal preference. I never had too much trouble with the old syntax myself.
-
When you want to switch to OUTER JOIN for testing purposes JOIN syntax wins out right.
Regards
"Fix the problem, not the blame!"
Hiding under a desk from SSIS Implemenation Work :crazy:
-
Knowing it would be better for the long term, I recently recast a bunch of 'old' style stored procedures to use JOIN. Most SELECTed... WHERE against an input parameter from the result of the join, which I replaced with another AND on the JOIN condition. You have to be sure of your logic, but I quite like the tidy appearance, and – at least for my data - the execution plan is the same. But I wonder whether it is good or bad style?
eg
SELECT t1.col1, t2.col2
FROM t1, t2
WHERE t1.fk1 = t2.fk2
AND t2.val1 = @InParambecomes
SELECT t1.col1, t2.col2
FROM t1 INNER JOIN t2
ON t1.fk1 = t2.fk2
AND t2.val1 = @InParam -
Be VERY careful with where you put your "WHERE" clause components when doing JOINs. With OUTER joins you can get vastly different results depending on whether the AND is up in the JOIN clause or down in the WHERE because of when the restrictions are placed on the resultset.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
I am seconding the SQLGuru here...
IMHO, the query that best addresses the needs addressed by the queries posted by Ewan is
SELECT t1.col1, t2.col2
FROM t1 INNER JOIN t2 ON t1.fk1 = t2.fk2
where t2.val1 = @InParamThis way we can make a proper distinction between join conditions and filter criteria; what say?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply