Solved

Which is better and why?

Posted on 2008-10-01
15
237 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 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

18 Experts available now in Live!

Get 1:1 Help Now