Why write t-sql this way? From table1, table2 where table1.col = table2.col

Question

Why write t-sql this way? From table1, table2 where table1.col = table2.col

Hawkeye_DBA

SSCarpal Tunnel

Points: 4365
More actions
June 4, 2010 at 9:49 am

#219678

I have always written my t-sql like
SELECT col1, col2, FROM
table 1
INNER JOIN tabl2 ON
table1.col1 = table2.col2
WHERE <any criteria here>
Is there some kind of performance gain or is that just sloppy coding?
The reason I ask is that I am observing more vendors and programmers using this syntax. Maybe I am in the dark ages?
Your feedback is appreciated!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

RP_DBA SSCertifiable Points: 5116 More actions · Answer 1

Seen (and wrote) a lot of legacy code where the JOIN criteria is in the WHERE clause. Unless you're seeing '*=' or '=*', I wouldn't worry too much about it.

_____________________________________________________________________
- Nate

@nate_hughes

Hawkeye_DBA SSCarpal Tunnel Points: 4365 More actions · Answer 2

THanks for your reply! So, basically no performance difference - just different syntax?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Difference between what and what? You only show us one of the 2 options. As far as I can tell, that code is the standard in the industry. Now performance "should" be good assuming sargable arguments in the where clause and appropriate indexes and strong enough server to handle the load.

Hawkeye_DBA SSCarpal Tunnel Points: 4365 More actions · Answer 4

Difference between the parser reading an inner join like table1, table2 where OR table1 INNER JOIN table2....

and yes, there are many variables, but I'm just simply speaking of the syntax (i.e. parser would prefer db.owner.object syntax when writing queries but many just reference the object)

Thanks!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

dbo.t1 inner join dbo.t2 on... is definitly the proper way to do it. The optimiser won't pick a better plan either way but the dev could accidentally make a cross join when using t1, t2 which is really where the problem lies.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

This:

select

d.col1,

d.col2,

e.col4,

e.col5

from

dbo.table1 d, dbo.table2 e

where

d.col1 = e.col1

is the SQL-89 Standard

This:

select

d.col1,

d.col2,

e.col4,

e.col5

from

dbo.table1 d

inner join dbo.table2 e

on (d.col1 = e.col1)

is the SQL-92 Standard.

Preferences? I prefer the SQL-92 Standard, separates the join criteria from the filter criteria making the query more readable in my opinion.