Implicit vs Explicit 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

General SQL Server Forums

New to SQL Server Programming

Implicit vs Explicit Joins

Author

Topic

kartik.kaveeshwar
Starting Member

18 Posts

Posted - 2008-05-20 : 07:52:56

performance wise what is the difference between following queries

Select * from A, B Where A.ID = B.ID
Select * from A INNER JOIN B ON A.ID = B.ID
(ID is the primary key)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-05-20 : 07:56:05

Old style of doing joins
newer style of doing joins

Option 2 is prefered these days, partially for readability. Also, the older style outer joins (=* and *=) no longer work in SQL 2005.

--
Gail Shaw

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-20 : 07:56:37

Performance-wise there is no difference. Second syntax is more readable, standard and strict while the first one is non-ANSI and error prone.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-20 : 08:29:29

Second option has more readability.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-20 : 08:33:18

quote:
Originally posted by GilaMonster

Also, the older style outer joins (=* and *=) no longer work in SQL 2005.

You mean with compatibility level set to 90?

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

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-05-21 : 02:59:07

quote:
You mean with compatibility level set to 90?

Yup.

--
Gail Shaw

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 03:46:18

In future versions of SQL Server, old style JOINs are not supported even with compatibility level set to a lower value.
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources