Home » SQL & PL/SQL » SQL & PL/SQL » sql query join (Oracle 9.2.0.5)
sql query join [message #435305] Tue, 15 December 2009 08:25 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member

   create table test_109 ( R NUMBER, ID NUMBER, C VARCHAR2(3));
   INSERT INTO TEST_109 VALUES( 1,30, 'X');
   INSERT INTO TEST_109 VALUES( 1,31, 'Y');
   INSERT INTO TEST_109 VALUES( 2,34, 'Z');


   create table test_110 ( ID1 NUMBER, ID2 NUMBER, C VARCHAR2(3));
   INSERT INTO TEST_110 VALUES(4,1,'X');
   INSERT INTO TEST_110 VALUES(4,2,'X');
   INSERT INTO TEST_110 VALUES(4,3,'X');
   INSERT INTO TEST_110 VALUES(5,1,'X');
   INSERT INTO TEST_110 VALUES(5,2,'X');
   INSERT INTO TEST_110 VALUES(6,1,'Y');
   INSERT INTO TEST_110 VALUES(7,1,'Z');



Expected result: based on common column.
   R	   ID   ID1 ID2 
   1   	   30    4  1
   1       30    4  2 
   1       30    4  3
   1       30    5  1
   1       30    5  2
   1       31    6  1
   2       34    7  1   

Thanks
Re: sql query join [message #435307 is a reply to message #435305] Tue, 15 December 2009 08:30 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Based on what logic?
Re: sql query join [message #435308 is a reply to message #435305] Tue, 15 December 2009 08:33 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Do a equi join with column C, you should get the result..
Re: sql query join [message #435315 is a reply to message #435305] Tue, 15 December 2009 09:45 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
Just do a natural join
Select r, id, id1, id2 
  from test_109 
       Natural Join test_110
/
Re: sql query join [message #435318 is a reply to message #435315] Tue, 15 December 2009 09:55 Go to previous messageGo to next message
cookiemonster
Messages: 12412
Registered: September 2008
Location: Rainy Manchester
Senior Member
mnitu wrote on Tue, 15 December 2009 15:45
Just do a natural join
Select r, id, id1, id2 
  from test_109 
       Natural Join test_110
/


Or better yet, don't. It's not a good feature and I would never recommend it. Here's one reason why: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:13430766143199
Re: sql query join [message #435323 is a reply to message #435318] Tue, 15 December 2009 10:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the most general reason is "never rely on implicit or default behaviour".

Regards
Michel
Re: sql query join [message #435339 is a reply to message #435318] Tue, 15 December 2009 12:09 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
@cookiemonster
As with all features you have just to know what you are doing.

@Michel
There is no "implicit behaviour" in the Natural Join. Simply that's the way it works!

Re: sql query join [message #435341 is a reply to message #435339] Tue, 15 December 2009 12:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
There is no "implicit behaviour" in the Natural Join. Simply that's the way it works!

Yes, there is, it relies on the fact that the tables have a column of the same name and these columns are ALWAYS the same ones.
If these are not implicit (or rather underlying, non explicit) rules what are they?

Regards
Michel
Re: sql query join [message #435342 is a reply to message #435339] Tue, 15 December 2009 12:21 Go to previous messageGo to next message
BlackSwan
Messages: 25044
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/wiki/Natural_join
"So, it is best to avoid natural joins as far as possible."
above is advice that should be followed.
Re: sql query join [message #435344 is a reply to message #435339] Tue, 15 December 2009 12:48 Go to previous messageGo to next message
cookiemonster
Messages: 12412
Registered: September 2008
Location: Rainy Manchester
Senior Member
mnitu wrote on Tue, 15 December 2009 18:09
@cookiemonster
As with all features you have just to know what you are doing.

Not really. If you have a an application that uses natural joins all over the place all it will take to completely break it is the addition of a single column to a single table. The best way to fix that would be to rewrite the natural joins as non natural joins, and that being the case why would you use them in the first place?

mnitu wrote on Tue, 15 December 2009 18:09

@Michel
There is no "implicit behaviour" in the Natural Join. Simply that's the way it works!

The fact that it's documented to work that way doesn't mean it's not implicit behaviour.
Re: sql query join [message #435360 is a reply to message #435339] Tue, 15 December 2009 15:07 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
There is nothing natural in natural joins. Actually they are just plain time bombs in your application as I (and of course you can find similar topics around the web more) wrote here
http://gplivna.blogspot.com/2007/10/natural-joins-are-evil-motto-if-you.html

Re: sql query join [message #435400 is a reply to message #435305] Wed, 16 December 2009 02:35 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
Quote:
Expected result: based on common column.


Natural Join
Quote:
The result of the natural join is the set of all combinations of tuples in R and S that are equal on their common attribute names.


So I think that the question is asking for a Natural Join. And I don't believe that this is a real business problem, do you? So there is nothing wrong in using a Natural Join in this case.

And speaking about "implicit and default behavior", what about this query?
SQL> Select e.first_name, d.department_name
  2    From hr.employees e
  3         Join hr.departments d
  4      On (e.employee_id = d.department_id)
  5  /
 
FIRST_NAME           DEPARTMENT_NAME
-------------------- ------------------------------
Steven               Finance
John                 Accounting
Matthew              Treasury
Mozhe                Corporate Tax
Joshua               Control And Credit
Peter                Shareholder Services
Louise               Benefits
Tayler               Manufacturing
Winston              Construction
Timothy              Contracting
Jennifer             Operations

Maybe there is an "implicit and default behavior" too. How can I avoid it?
Re: sql query join [message #435402 is a reply to message #435400] Wed, 16 December 2009 02:40 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Maybe there is an "implicit and default behavior" too. How can I avoid it?

You presumably have something in mind here. care to elaborate.
FWIW I totally disagree with you on the use of natural joins. As with most others in this thread, I agree, they are simply a problem waiting to happen. It is of course, entirely up to you if you want to use them in your production code, but I sincerely hope that it is you that has to resolve the issues that occur due to this practice rather than the problem getting dumped in someone else's lap. Further, if you were working on the same project as me, I would insist that you did not follow the practice.
Re: sql query join [message #435504 is a reply to message #435402] Wed, 16 December 2009 08:18 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
Where was I talking about "using them in a production database" ?

Quote:
...And I don't believe that this is a real business problem, do you? ...



Re: sql query join [message #435511 is a reply to message #435400] Wed, 16 December 2009 08:43 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Expected result: based on common column.

Is NOT the same as
Quote:
The result of the natural join is the set of all combinations of tuples in R and S that are equal on their common attribute names.
i.e. common columns are not the same as common attribute names;
What are the common columns in the following two tables?
CREATE TABLE foo (id NUMBER, dname VARCHAR2(10));
CREATE TABLE bar (id NUMBER, fname VARCHAR2(30), dept_id NUMBER 

Do you think it is ID? Wrong!
CREATE TABLE foo (id NUMBER PRIMARY KEY, dname VARCHAR2(10))

CREATE TABLE succeeded.
CREATE TABLE bar (id NUMBER PRIMARY KEY, fname VARCHAR2(30), dept_id NUMBER REFERENCES foo(id))

CREATE TABLE succeeded.
INSERT INTO foo VALUES (10, 'SALES')
1 rows inserted
INSERT INTO foo VALUES (20, 'IT')
1 rows inserted
INSERT INTO foo VALUES (30, 'FINANCE')
1 rows inserted
INSERT INTO BAR VALUES (1, 'Rod',10)
1 rows inserted
INSERT INTO BAR VALUES (2, 'Jane',10)
1 rows inserted
INSERT INTO BAR VALUES (3, 'Freddy',20)
1 rows inserted
INSERT INTO BAR VALUES (4, 'Bungle',20)
1 rows inserted
INSERT INTO BAR VALUES (5, 'George',10)
1 rows inserted
INSERT INTO BAR VALUES (6, 'Zippy',30)
1 rows inserted
SELECT bar.fname, foo.dname
FROM foo
NATURAL JOIN bar

FNAME                          DNAME      
------------------------------ ---------- 

0 rows selected

SELECT bar.fname, foo.dname
FROM foo
JOIN bar
ON foo.id = bar.dept_id

FNAME                          DNAME      
------------------------------ ---------- 
Rod                            SALES      
Jane                           SALES      
Freddy                         IT         
Bungle                         IT         
George                         SALES      
Zippy                          FINANCE    

6 rows selected

Quote:
Where was I talking about "using them in a production database" ?

Quote:
...And I don't believe that this is a real business problem, do you? ...

So what? we are still under an (informal and unofficial)obligation to provide the best answer to the best of our knowledge and abilities
Are you seriously espousing the use of different techniques eing implemented depending on whether one is on a production system or not? Surely not!
Re: sql query join [message #435514 is a reply to message #435511] Wed, 16 December 2009 09:00 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
pablolee wrote on Wed, 16 December 2009 08:43

...i.e. common columns are not the same as common attribute names;

I don't get your point, do you?

pablolee wrote on Wed, 16 December 2009 08:43

...
So what? we are still under an (informal and unofficial)obligation to provide the best answer to the best of our knowledge and abilities

That was the case.
Re: sql query join [message #435515 is a reply to message #435341] Wed, 16 December 2009 09:03 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
Michel Cadot wrote on Tue, 15 December 2009 12:19
...
Yes, there is, it relies on the fact that the tables have a column of the same name and these columns are ALWAYS the same ones.
...

Oh, I see. Maybe you will agree then, that ordinary joins relays on the fact that columns data types NEVER change in a table.
Re: sql query join [message #435516 is a reply to message #435514] Wed, 16 December 2009 09:04 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
I don't get your point
Why am i not surprised?
Quote:
That was the case.
Oh dear. Good luck to those who have to work with you then.
Re: sql query join [message #435518 is a reply to message #435515] Wed, 16 December 2009 09:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
mnitu wrote on Wed, 16 December 2009 16:03
Michel Cadot wrote on Tue, 15 December 2009 12:19
...
Yes, there is, it relies on the fact that the tables have a column of the same name and these columns are ALWAYS the same ones.
...

Oh, I see. Maybe you will agree then, that ordinary joins relays on the fact that columns data types NEVER change in a table.

So you think that the chance of changing the datatype of a given column (ouch, bad thing to do!) is anywhere near the chance of a column being added to a table?

You already stated that your option, although valid SQL is not viable for a production environment. Why do you insist on defending such a potentially dangerous and unusable structure?
Probably the only reason Oracle implemented this was to comply to ANSI-standards.

[Updated on: Wed, 16 December 2009 09:16]

Report message to a moderator

Re: sql query join [message #435526 is a reply to message #435518] Wed, 16 December 2009 09:41 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
No, I don't!

And I'm not trying to convince anybody that they should use only Natural Join in their production database! I'm simply defending my answer that I find to be the best seeing how the question was formulated. But when I see all that people crying, « OH NO, NATURAL JOIN that's lèse-majesté crime » I can't stop me trying to remind that SQL kingdom is not the paradise, and that subtle problems can arrive even when you're following what it is called bests practices.

By the way, Natural Join is also part of the (relational) theory and I believe that you already know that.
Re: sql query join [message #435528 is a reply to message #435526] Wed, 16 December 2009 09:59 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
And I'm not trying to convince anybody that they should use only Natural Join in their production database
Nobody said that you were saying that.
Quote:
I'm simply defending my answer that I find to be the best seeing how the question was formulated.

And we are simply disagreeing with you as we believe that it is a poor connstruct to use no matter how the question is formulated.
Quote:
But when I see all that people crying, « OH NO, NATURAL JOIN that's lèse-majesté crime »

Where? Where was that cried? Please quote it.
Quote:
I can't stop me trying to remind that SQL kingdom is not the paradise, and that subtle problems can arrive even when you're following what it is called bests practices.
And so you use this to justify using a construct that is inherently problematic?
I believe that you simply object to being questioned and disagreed with.
Re: sql query join [message #435530 is a reply to message #435528] Wed, 16 December 2009 10:03 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
@pablolee
I agree that we disagree!
Re: sql query join [message #435531 is a reply to message #435526] Wed, 16 December 2009 10:05 Go to previous messageGo to next message
cookiemonster
Messages: 12412
Registered: September 2008
Location: Rainy Manchester
Senior Member
mnitu wrote on Wed, 16 December 2009 15:41
I can't stop me trying to remind that SQL kingdom is not the paradise, and that subtle problems can arrive even when you're following what it is called bests practices.

Of course they can, but I don't see that as any justification for ever using a construct that can break as easily as natural join when there is a simple alternative.

mnitu wrote on Wed, 16 December 2009 15:41

By the way, Natural Join is also part of the (relational) theory and I believe that you already know that.

Of course - that's why it's been added to oracle, still doesn't make it a good idea.
Re: sql query join [message #435532 is a reply to message #435531] Wed, 16 December 2009 10:15 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
cookiemonster wrote on Wed, 16 December 2009 10:05

...
Of course they can, but I don't see that as any justification for ever using a construct that can break as easily as natural join when there is a simple alternative.

Maybe for passing an Oracle Certification as an Database SQL Expert? Embarassed
Ask Oracle why it is there in their sample ?

cookiemonster wrote on Wed, 16 December 2009 10:05


...
Of course - that's why it's been added to oracle, still doesn't make it a good idea.

Oh, you are remebering me Tom Kyte saying "Never say never".
Re: sql query join [message #435533 is a reply to message #435530] Wed, 16 December 2009 10:20 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please ./fa/3400/0/

Regards
Michel
Previous Topic: Creating phone number format
Next Topic: Timestamp difference in minutes
Goto Forum:
  


Current Time: Wed Dec 07 08:40:48 CST 2016

Total time taken to generate the page: 0.07905 seconds