Old style Joins Vs New ANSI Joins

SQL Lion » SQL Troubleshoot

Old style Joins Vs New ANSI Joins

(4 posts) (4 voices)

Started 1 year ago by zeph2home
Latest reply from arunmallick

Activate e-mail notification in your profile settings to get updates. [Help ?]

Tags:

zeph2home
Member
From the past 1 and half years i have been working as a SQL DBA.
I am curious to know about pros and cons in between Old style Joins and the new Ansi Joins.
For example, getting employee with department info for :
Old Style
```
select E.*,D.*,L.*
 from Employee E, Dept D, Location L
 where E.empid = D.empid
 and D.locid = L.locid
```
New ANSI Style
```
select E.*,D.*,L.*
 from Employee E inner join Dept D
 on E.empid = D.empid
 inner join Location L
 on D.locid = L.locid
```
I want to list down any plus points for one over another.
Thanks in advance.
Posted 1 year ago #
jenloph282
Member

Hi zeph2home,
It’s still a controversial topic regarding the usage of joins over Old and ANSI version.
Although it shows same Execution plans for both the cases (I checked with inner joins). But it is always recommended to use the ANSI joins.
Anyways, the old joins are no longer supported by SQL Server after the version 8.0 (i.e. from SQL 2005 onwards) especially in case of left outer join, right outer join or full outer join. The keywords “*=” or “-*” will not work in the newer versions of SQL Server.
Even if you want to go for old Joins then you have make sure that your database is running on the compatibility version of 8.0 atleast.

Posted 1 year ago #
sqltech56
Member

I don’t think it is correct to categorize these two types of joins into “ANSI” vs. "Old
Style". Both are compliant with the ANSI standards SQL92, SQL99 AND
SQL2003. Its largely a matter of style and flexibility of the user who is queering either to specify join conditions using the INNER JOIN i.e. ANSI style or not. If you ask me I usually go with the Inner Join style :) ; even sometimes I prefer where clause style. I don't always follow my own rules though!! :-)

Posted 1 year ago #
arunmallick
Member
Hi all,
its true that it is showing same Execution Plans for both the type of joins (Old and ANSI joins). But to my personal experience i would suggest you to go for ANSI joins because:
• In old style, the where clause have to take care of both the criteria i.e. joining column conditions and where conditions

For example, you want employee information with his department for a particular location named "Chicago", then the where clause will have to take care of four conditions; 2 for joining and 1 for filtration.
```
select E.*,D.*,L.*
 from Employee E, Dept D, Location L
 where E.empid = D.empid
 and D.locid = L.locid
 and L.locName = 'Chicago'
```
• In ANSI style, for the above example, 2 joining conditions will be taken care of by INNER JOIN and 1 by WHERE clause, making it more simple for readability and understanding. It act as a self documentation.
```
select E.*,D.*,L.*
 from Employee E inner join Dept D
 on E.empid = D.empid
 inner join Location L
 on D.locid = L.locid
 where L.locName = 'Chicago'
```
Let me know your suggestions on it.
Posted 1 year ago #

RSS feed for this topic

Reply

You must log in to post.