which one is better Query? - 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 2000 Forums

Transact-SQL (2000)

which one is better Query?

Author

Topic

Mckay
Starting Member

8 Posts

Posted - 2005-11-08 : 11:18:56

hi

i have 2 Query:

select * from tbReturDet a with(nolock), tbPurchaseDet b with(nolock), MsCategory c with(nolock)
where a.ProductID = b.ProductID and b.CategoryID = c.CategoryID and a.ReturID = 'qqq'

select * from tbReturDet a with(nolock)
join tbPurchaseDet b with(nolock) on a.ProductID = b.ProductID
join MsCategory c with(nolock) on b.CategoryID = c.CategoryID
where a.ReturID = 'qqq'

it's gave a same result ... but which one is better?

thx

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-08 : 11:28:02

Neither, because the use of SELECT * is discouraged. But of the two, JOIN syntax is the preferred method of linking datasets primarily for clarity of coding.

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-08 : 11:42:20

They are equivalant

Type in [CTRL]+K and then run both...you will see the query plan..they should be identical.

However

Query 2 is ANSI syntax and probably should be how you code everything

Also (NoLock) allows dirty reads

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

namasteall2000
Starting Member

20 Posts

Posted - 2005-11-09 : 15:33:32

Mckay - FYI - you don't need to put no lock with every table - if you put nolock ones on the first table - SQL server automatically locks all other tables used in that Query.

Second query is more efficent and joins takes less resources than In or any other function.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-09 : 17:14:02

quote:
Second query is more efficent and joins takes less resources than In or any other function.

That is way to general a statement. Much of the time, the optimizer will generate the same plan regardless of which method you use. The preference for JOIN is, again, largely a matter of clarity.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-11-10 : 08:07:06

"That is way to general a statement. Much of the time, the optimizer will generate the same plan regardless of which method you use. The preference for JOIN is, again, largely a matter of clarity."

Unless you move to Yukon (SQL 2005). Then if you start using *= or =*, it will bomb. ANSI JOIN syntax is mandatory.

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources