Solved

Which is better and why?

Posted on 2008-10-01
15
248 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 73

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 73

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
 
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 142

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 142

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 142

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 142

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 142

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 142

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

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 …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
A short film showing how OnPage and Connectwise integration works.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

930 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now