Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Which is better and why?

Posted on 2008-10-01
15
Medium Priority
?
292 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 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
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.​
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

877 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