SELECT, JOIN or WHERE - 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 2008 Forums

Transact-SQL (2008)

SELECT, JOIN or WHERE

Author

Topic

regata
Starting Member

4 Posts

Posted - 2012-02-08 : 21:53:06

Hi
i need advise
I have 3 tables lets say

1: ITEMS [id,name]
2: CUSTOMERS [id,name]
3: SALE [date,customername,itmid,quantity,discount ... ]

The question
which one is better in perfermance

1st one
================================================

SELECT
S.Date,
(select i.name from items i where i.id = S.Itmid) as ITEM,
(select c.name from customers c where c.id = S.customername) as CUSTOMER,
S.QUANTITY,
S.DISCOUNT,
.
.
.
FROM
STOCK AS S

2nd one
================================================

SELECT
S.Date,
i.name as ITEM,
c.name as CUSTOMER,
S.QUANTITY,
S.DISCOUNT,
.
.
.
FROM
STOCK AS S
join items i on i.id = S.Itmid
join customers c on c.id = S.customername

3rd one
================================================

SELECT
S.Date,
i.name as ITEM,
c.name as CUSTOMER,
S.QUANTITY,
S.DISCOUNT,
.
.
.
FROM
STOCK S,items i, customers c
where i.id = S.Itmid and c.id = S.customername

i have few records in database so far and i don't see any difference but what if record count grows lets say over 100000... and so

tnx

Nurlan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-08 : 22:02:08

i think 2nd and 3 rd options will be better (they both are equivalent only difference being only written in old join syntax and other in ANSI join syntax. I prefer 2 as its ANSI join syntax and adds more clarity to the query.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-09 : 03:58:46

The first one will throw errors if the subqueries ever return more than one row. The 2nd is the recommended, the 3rd is the really old way of writing joins.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources