Solved

Which is better and why?

Posted on 2008-10-01
15
265 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query still returning duplicates 5 41
Sum particular field in database 2 16
Using SQL*PLUS issue with where statement with trunc function 3 29
Select question from MySQL 1 13
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…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

837 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