Efficiency of JOINs - 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

SQL Server Development (2000)

Efficiency of JOINs

Author

Topic

richie_b_97
Starting Member

8 Posts

Posted - 2008-06-05 : 05:12:35

Hi all, quick question.

Someone has submitted to me some SQL as follows:

SELECT TS_NAME, ST_STEP_NAME, RN_TEST_ID
from STEP,RUN, TEST
where RN_TEST_ID = TS_TEST_ID
and RN_RUN_ID = ST_RUN_ID

This looked really odd to me as there's no JOINs. From an efficiency point of view, would converting the above to

SELECT TS_NAME, ST_STEP_NAME, RN_TEST_ID
from STEP
INNER JOIN RUN on RN_RUN_ID = ST_RUN_ID
INNER JOIN TEST on RN_TEST_ID = TS_TEST_ID

make a difference? The tables are large (e.g. one of them has 500,000 rows).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-05 : 05:16:46

500 000 records are not considered large tables.
And no, there is no efficiency difference between the two queries.
However, the first query style is deprecated and will probably not work in future releases of Microsoft SQL Server.

E 12°55'05.25"
N 56°04'39.16"

richie_b_97
Starting Member

8 Posts

Posted - 2008-06-05 : 05:29:06

Hi Peso, thanks for the help.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-05 : 05:37:41

Also with first style, you carry the risk of forgetting to add join condition in case of more than 2 tables and thus creating cartesian product.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources