JOIN ON vs WHERE clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

JOIN ON vs WHERE clause

Author

Topic

rekon32
Starting Member

16 Posts

Posted - 2013-08-28 : 21:33:47

Just a general question. Which type of "join" would be faster? join on or where clause?

SELECT
    table1.this, table2.that, table2.somethingelse
FROM
    table1, table2
WHERE
    table1.foreignkey = table2.primarykey
    AND (some other conditions)

v.s.


SELECT
    table1.this, table2.that, table2.somethingelse
FROM
    table1 INNER JOIN table2
    ON table1.foreignkey = table2.primarykey
WHERE
    (some other conditions)

Data Analyst

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-08-28 : 23:10:04

There wouldn't be a performance difference~~, but it makes a difference what version of SQL Server you are using. The where version is no longer supported. It was deprecated in like 2005, still available in 2008 but gone in 2012~~.

In addition to the version issue, the join version is easier to read. I recently had to review code with the where version, and my brain almost exploded. lol

[EDIT] I just read that the where version for inner joins is still supported but not for outer joins. But my point still remains about the performance and the readability. Use proper join syntax!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rekon32
Starting Member

16 Posts

Posted - 2013-08-28 : 23:35:59

quote:
Originally posted by tkizer

There wouldn't be a performance difference~~, but it makes a difference what version of SQL Server you are using. The where version is no longer supported. It was deprecated in like 2005, still available in 2008 but gone in 2012~~.

In addition to the version issue, the join version is easier to read. I recently had to review code with the where version, and my brain almost exploded. lol

[EDIT] I just read that the where version for inner joins is still supported but not for outer joins. But my point still remains about the performance and the readability. Use proper join syntax!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

I hear ya! I recently had to work with an offshore programmer that used nothing but where versions of join. I thought there might have been a benefit I didn't know of.

Thanks!

Data Analyst

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-08-29 : 15:58:16

As a general practice you want to put JOIN criteria in the ANSI compliant join syntax (ON clause) but put any filters in the WHERE clause. Aside from being a best practice, if I am not mistaken, there is a small benefit to doing this in certain scenarios: the engine has more options on where it can apply the filter versus the JOIN criteria.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources