question about left join in sql2005

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 2005 Forums

Transact-SQL (2005)

question about left join in sql2005

Author

Topic

yeung_lcd
Starting Member

5 Posts

Posted - 2010-07-13 : 23:03:13

My company start to migrate MS-SQL2000 to MS-SQL2005 and I found some store procedures and DTS are using old style non-ansi join.
for example:
--Query 1
select * from
a,b,c,d,
where
a.ab_id=b.b_id and
a.ac_id=c.c_id and
b.bd_id*=d.d_id and d.d_status=1

So i need to rewrite the query 1 in order to work in sql2005 and I find two different styles:

--Query 2:
select * from
a
inner join b on a.ab_id=b.b_id
inner join c on a.ac_id=c.c_id
left join d on b.bd_id=d.d_id and d.d_status=1

--Query 3:
select * from
a,
b left join d on b.bd_id=*d.d_id and d.d_status=1 ,
c,
where
a.ab_id=b.b_id and
a.ac_id=c.c_id

which one is better in terms of performance?
Thanks for your help.
yeung

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-13 : 23:18:56

All should give you the same query plan.
However, the NON ANSI join method will not be supported in SQL 2005.

Meaning, Query 1 and 3 will not be able to run under 2005

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources