Home » SQL & PL/SQL » SQL & PL/SQL » SQL Natural Join
icon5.gif  SQL Natural Join [message #242718] Mon, 04 June 2007 15:59 Go to next message
hegge
Messages: 5
Registered: June 2007
Junior Member
Hi there,

I'm wondering why the results with natural join differs depending on the select statement.

For example:
select name from db1 natural join db2 natural join db3 where type='Nice';

is completely different to

select * from db1 natural join db2 natural join db3 where type='Nice';

I've been using MySQL until now, which gives me the same result on both statement.

thnx for help

(I'm using oracle 10.2.0.10)
Re: SQL Natural Join [message #242738 is a reply to message #242718] Mon, 04 June 2007 23:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Welcome to this forum (and to the world of Oracle of course!)

If you could provide some sample data, we might see what's going on.
So, please provide create table statements and some insert-statements, just enough to reproduce your problem.
If you use [code] and [/code] tags around your statements, it will all be nice and readable. (this is described in the two stickies of the forum).
Re: SQL Natural Join [message #242892 is a reply to message #242718] Tue, 05 June 2007 07:42 Go to previous messageGo to next message
hegge
Messages: 5
Registered: June 2007
Junior Member
Thanks for your reply! Here's some (nice formatted) data from my tables:

create table Studenten(
  MatrNr int primary key,
  Name varchar(40),
  Semester int,
  Fachrichtung int);

create table Vorlesungen(
  VorlNr int primary key,
  Titel varchar(90),
  SWS int,
  gelesenVon int);

create table hear(
  MatrNr int,
  VorlNr int);


Content:
Studenten:

insert into Studenten values(24002,'Xenokrates',18, 103);
insert into Studenten values(25403,'Jonas',12, 103);
insert into Studenten values(26120,'Fichte',10, 100);
..

hear:

insert into hear values(26120,5001);
insert into hear values(27550,5001);
insert into hear values(27550,4052);
insert into hear values(28106,5041);
insert into hear values(29555,5022);
insert into hear values(25403,5022);
..

Vorlesungen:

insert into Vorlesungen values(5001,'Grundzuege', 4, 2137);
insert into Vorlesungen values(5041,'Ethik', 4, 2125);
insert into Vorlesungen values(5043,'Erkenntnistheorie', 3, 2126);
insert into Vorlesungen values(5022,'Glaube und Wissen', 2, 2134);


I'm trying to get those students, who visit the lecture 'Glaube und Wissen'.

select * 
from studenten natural join hear natural join vorlesungen 
where titel = 'Glaube und Wisen' 

outputs

VORLNR	MATRNR	NAME	SEMESTER	FACHRICHTUNG	TITEL	SWS	GELESENVON
5022	29555	Feuerbach	2	100	Glaube und Wissen	2	2134
5022	25403	Jonas	12	103	Glaube und Wissen	2	2134

but 

select name
from studenten natural join hear natural join vorlesungen 
where titel = 'Glaube und Wisen' 

outputs

NAME
Fichte
Schopenhauer
Schopenhauer
Carnap
Carnap
...


And I just wanna know why.

Tanks for any further replys.
Re: SQL Natural Join [message #242908 is a reply to message #242892] Tue, 05 June 2007 08:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With the data you posted I got:
SQL> select * 
  2  from studenten natural join hear natural join vorlesungen 
  3  where titel = 'Glaube und Wisen' ;

no rows selected

SQL> select name
  2  from studenten natural join hear natural join vorlesungen 
  3  where titel = 'Glaube und Wisen' 
  4  ;

no rows selected

Please post data that show what you want to show.

Regards
Michel
Re: SQL Natural Join [message #242910 is a reply to message #242908] Tue, 05 June 2007 08:43 Go to previous messageGo to next message
hegge
Messages: 5
Registered: June 2007
Junior Member
Ok sorry.

I mistyped the select statement:

select * 
from studenten natural join hear natural join vorlesungen 
where titel = 'Glaube und Wissen' 

select name
from studenten natural join hear natural join vorlesungen 
where titel = 'Glaube und Wissen' 

And here is the complete data:

insert into Studenten values(24002,'Xenokrates',18, 103);
insert into Studenten values(25403,'Jonas',12, 103);
insert into Studenten values(26120,'Fichte',10, 100);
insert into Studenten values(26830,'Aristoxenos',8, 102);
insert into Studenten values(27550,'Schopenhauer',6, 102);
insert into Studenten values(28106,'Carnap',3, 101);
insert into Studenten values(29120,'Theophrastos',2, 101);
insert into Studenten values(29555,'Feuerbach',2, 100);

insert into Vorlesungen values(5001,'Grundzuege', 4, 2137);
insert into Vorlesungen values(5041,'Ethik', 4, 2125);
insert into Vorlesungen values(5043,'Erkenntnistheorie', 3, 2126);
insert into Vorlesungen values(5049,'Maeeutik', 2, 2125);
insert into Vorlesungen values(4052,'Logik', 4, 2125);
insert into Vorlesungen values(5052,'Wissenschaftstheorie', 3, 2126);
insert into Vorlesungen values(5216,'Bioethik', 2, 2126);
insert into Vorlesungen values(5259,'Der Wiener Kreis', 2, 2133);
insert into Vorlesungen values(5022,'Glaube und Wissen', 2, 2134);
insert into Vorlesungen values(4630,'Die 3 Kritiken', 4, 2137);


insert into hear values(26120,5001);
insert into hear values(27550,5001);
insert into hear values(27550,4052);
insert into hear values(28106,5041);
insert into hear values(28106,5216);
insert into hear values(28106,5259);
insert into hear values(29120,5001);
insert into hear values(29120,5049);
insert into hear values(29555,5022);
insert into hear values(25403,5022);
insert into hear values(29555,5001);

Re: SQL Natural Join [message #242912 is a reply to message #242910] Tue, 05 June 2007 08:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It seems to be a bug.
If you look at the explain plan:
SQL> @xpl3p

  2  select * 
  3  from studenten natural join hear natural join vorlesungen 
  4  where titel = 'Glaube und Wissen' ;

Explained.

SQL> @xpl3
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 899968111

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     2 |   346 |     9  (12)| 00:00:01 |
|   1 |  NESTED LOOPS                |             |     2 |   346 |     9  (12)| 00:00:01 |
|*  2 |   HASH JOIN                  |             |     2 |   224 |     7  (15)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | VORLESUNGEN |     1 |    86 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL         | HEAR        |    11 |   286 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| STUDENTEN   |     1 |    61 |     1   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | SYS_C009655 |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("HEAR"."VORLNR"="VORLESUNGEN"."VORLNR")
   3 - filter("VORLESUNGEN"."TITEL"='Glaube und Wissen')
   6 - access("STUDENTEN"."MATRNR"="HEAR"."MATRNR")

Note
-----
   - dynamic sampling used for this statement

SQL> @xpl3p

  2  select name
  3  from studenten natural join hear natural join vorlesungen 
  4  where titel = 'Glaube und Wissen' ;

Explained.

SQL> @xpl3
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 344679811

-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |    11 |  1045 |    10  (10)| 00:00:01 |
|*  1 |  HASH JOIN            |             |    11 |  1045 |    10  (10)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|             |     8 |   656 |     6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL  | VORLESUNGEN |     1 |    47 |     3   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |             |     8 |   280 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | STUDENTEN   |     8 |   280 |     3   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL   | HEAR        |    11 |   143 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("STUDENTEN"."MATRNR"="HEAR"."MATRNR")
   3 - filter("VORLESUNGEN"."TITEL"='Glaube und Wissen')

Note
-----
   - dynamic sampling used for this statement

You see the following access path is missing:
2 - access("HEAR"."VORLNR"="VORLESUNGEN"."VORLNR")

That is, Oracle misses a join.

You have a clear test case. I think you should open a SR.
Let us know when you'll get an answer from Oracle.
(Btw, there are many bugs with "natural joins" you should avoid them.)

Regards
Michel
Re: SQL Natural Join [message #242921 is a reply to message #242912] Tue, 05 June 2007 09:14 Go to previous messageGo to next message
hegge
Messages: 5
Registered: June 2007
Junior Member
Thanks very much. I thought I would have made a common mistake.

SR means Service Request? Where can I post this? I looked through the oracle page and couldn't find it.

I've been using oracle for only one week now, so excuse my ignorance Smile

Regards

Christian
Re: SQL Natural Join [message #242924 is a reply to message #242921] Tue, 05 June 2007 09:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes Service Request on Metalink.
You need a CSI (Client Support Id) to be able to raise a SR.

Regards
Michel
Re: SQL Natural Join [message #242933 is a reply to message #242924] Tue, 05 June 2007 09:44 Go to previous messageGo to next message
hegge
Messages: 5
Registered: June 2007
Junior Member
I'm working on a database service provided by my university. This means I don't have a support identifier and can't register.

I'll send an email to the relevant assistant with this information.

Thanks for the support Smile
Re: SQL Natural Join [message #242935 is a reply to message #242912] Tue, 05 June 2007 09:45 Go to previous message
gintsp
Messages: 118
Registered: February 2007
Senior Member
Michel Cadot wrote on Tue, 05 June 2007 16:59
(Btw, there are many bugs with "natural joins" you should avoid them.)



To my mind natural join actually is the most UNNATURAL join I've ever seen. It is absolutely un-transparent and exposed to possible bugs without any notice in case the table's structure changes.

Of course natural join would never work with my naming conventions but anyway even for other systems I've ever supported and touched natural join would be absolutely one of the last things I'd like to add.

Gints Plivna
http://www.gplivna.eu
Previous Topic: Host String,Username,Password
Next Topic: START WITH CONNECT BY
Goto Forum:
  


Current Time: Sun Dec 04 15:03:29 CST 2016

Total time taken to generate the page: 0.09608 seconds