[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

Which is better and why?

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
vbemt
Asked:
vbemt
  • 6
  • 6
  • 2
  • +1
1 Solution
 
sdstuberCommented:
neither is better than the other.  They are identical in functionality and intent.

It's simply a matter of cosmetic style.
0
 
sdstuberCommented:
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
 
vbemtAuthor Commented:
Okay, I was just wondering if the inner join was more efficient then putting it in the "Where" clause.  
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
silemoneCommented:
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
 
vbemtAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
vbemtAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
vbemtAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
vbemtAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if that's not it, please post the table structures in your oracle db...
0
 
vbemtAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 6
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now