Solved

Which is better and why?

Posted on 2008-10-01
15
269 Views
Last Modified: 2010-03-20
I'm trying to find out the differences between the following sql statements, and which is better to use.
a)
Select a.field1, a.field2, b.field3
From a inner join b on a.field1 = b.field1

b)
Select a.field1, a.field2, b.field3
From a, b
Where a.field1 = b.field3

I know that on an inner join you can only join on columns that have the same name.  However I have tables that have common columns with different names but are the same thing (i.e  CityCode and TeamCity).  They contain the same exact type of information, the code of the city, but they are named differently.
0
Comment
Question by:vbemt
  • 6
  • 6
  • 2
  • +1
15 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 22614075
neither is better than the other.  They are identical in functionality and intent.

It's simply a matter of cosmetic style.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22614095
I suppose in an abstract sense,  the first "might" be considered better as it explicitly joins
whereas the second could be interpreted as a full join with a filter applied afterward.

Except that's not how it really works in any db I know of
0
 

Author Comment

by:vbemt
ID: 22614104
Okay, I was just wondering if the inner join was more efficient then putting it in the "Where" clause.  
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 21

Expert Comment

by:silemone
ID: 22614117
Actually in this instance, they are EQUIVALENT...it there were union All, etc...the first would be...you could test this theory by actually looking at the properties if this MSSQL and seeing how long it takes to execute.
0
 

Author Comment

by:vbemt
ID: 22614165
This all came about because I'm in the process of trying to convert tables from MS SQL Server to an existing Oracle database and the other developer already created tables that have different name.  Doing the 2nd method allows me to not have to change alot of tables and hopefully less time to do it :)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22614189
>I know that on an inner join you can only join on columns that have the same name.  

just to emphasize that that information is WRONG.
don't know where you got that from...



0
 

Author Comment

by:vbemt
ID: 22614257
Where this came from is this:
I was trying to create a view in Oracle.
I was getting an error on the line with my join.
I added a column in the first table and gave it the same name as the second column.
It worked.
When I removed it and put it back to the orginal code it didn't.  The only difference was the name.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22614302
>I added a column in the first table and gave it the same name as the second column.
which "second" column?

what was the view code?
0
 

Author Comment

by:vbemt
ID: 22614356
CREATE view [vwEOB
as
select e.eobid,e.eobname, createdby, createdon, modifiedby, modifiedon,n.elnotid, n.elnot, n.name24[Name]
from eob e
inner join eobelnot l on e.eobid = l.eobid
inner join elnot n on l.elnotid = n.elnotid
Where e.isactive = 1

This was the orginal SQL Server Script.
In my Oracle table I don't have an elnotID it's called elID and when I tried to the join it wouldn't work until I added a name so that they were the same.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22614398
you mean this did not work:
CREATE view vwEOB
as
select e.eobid,e.eobname, createdby, createdon, modifiedby, modifiedon,n.elID , n.elnot, n.name24 "Name"
from eob e
inner join eobelnot l on e.eobid = l.eobid
inner join elnot n on l.elID  = n.elID 
Where e.isactive = 1

Open in new window

0
 

Author Comment

by:vbemt
ID: 22614451
No, that's not what it is.  Table a is elnot, and table b is elID.  They are the same data but different column names.  When I do the join it doesn't work.  I have alot of tables that would have to be changed to make them all the same name.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22614550
you mean:
CREATE view vwEOB
as
select e.eobid,e.eobname, createdby, createdon, modifiedby, modifiedon, n.elnotid elID , n.elnotid , n.name24 "Name"
from eob e
inner join eobelnot l on e.eobid = l.eobid
inner join elnot n on l.elID  = n.elnotid 
Where e.isactive = 1

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22614558
if that's not it, please post the table structures in your oracle db...
0
 

Author Comment

by:vbemt
ID: 22614601
correct that is how it is, but I get an error from Oracle when I execute it.  And it comes out to being an invalid column name.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22614940
that error is simply & plain indicating that some column name is indeed wrongly specified in the query, in regards to which column names exist in the tables.
using JOIN or WHERE will not change anything in that regards.
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question