Home » SQL & PL/SQL » SQL & PL/SQL » How to natural join 3 tables efficiently?
How to natural join 3 tables efficiently? [message #236930] Fri, 11 May 2007 06:21 Go to next message
dumdum
Messages: 25
Registered: April 2007
Junior Member
Suppose there are 3 tables:
r1 with attributes A,B,C and 1000 rows
r2 with attributes C,D,E and 1500 rows
r3 with attributes E,F and 750 rows
A,C,E are primary keys
if r1 natural join r2 natural join r3
How many rows will the resulting table have?
Is there any more efficient way to natural join these 3 tables? How?
Thx!
Re: How to natural join 3 tables efficiently? [message #236933 is a reply to message #236930] Fri, 11 May 2007 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What are the data?

Regards
Michel
Re: How to natural join 3 tables efficiently? [message #236938 is a reply to message #236933] Fri, 11 May 2007 06:49 Go to previous messageGo to next message
dumdum
Messages: 25
Registered: April 2007
Junior Member
Sorry, I don't know what you mean. The value of the attribute doesn't matter.
Re: How to natural join 3 tables efficiently? [message #236943 is a reply to message #236938] Fri, 11 May 2007 06:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course they are. They determine the cardinality of the joins.

Regards
Michel
Re: How to natural join 3 tables efficiently? [message #236944 is a reply to message #236943] Fri, 11 May 2007 06:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The resulting table will have somewhere between 0 and 750 rows.
Re: How to natural join 3 tables efficiently? [message #236945 is a reply to message #236943] Fri, 11 May 2007 06:59 Go to previous messageGo to next message
dumdum
Messages: 25
Registered: April 2007
Junior Member
Oops, I forget to mention the assumption. Every primary key is assumed to have a dense index already. Sorry.
Re: How to natural join 3 tables efficiently? [message #236947 is a reply to message #236944] Fri, 11 May 2007 07:01 Go to previous messageGo to next message
dumdum
Messages: 25
Registered: April 2007
Junior Member
Thank you, Frank but would you mind telling me the reason?
Re: How to natural join 3 tables efficiently? [message #236948 is a reply to message #236944] Fri, 11 May 2007 07:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Frank wrote on Fri, 11 May 2007 13:58
The resulting table will have somewhere between 0 and 750 rows.

On second thought: it will contain 750 rows.

As a side note: I never saw the use for a natural join. Always thought of it as a lazy way of coding.


[Edit: Argh, 0-750 is correct, unless r1.C and R2.E are not-null and have a FK to the respective PKs

[Updated on: Fri, 11 May 2007 07:04]

Report message to a moderator

Re: How to natural join 3 tables efficiently? [message #236952 is a reply to message #236930] Fri, 11 May 2007 07:11 Go to previous message
dumdum
Messages: 25
Registered: April 2007
Junior Member
Thx!
Previous Topic: Representing Commas
Next Topic: Join tables (merge)
Goto Forum:
  


Current Time: Sat Dec 03 14:13:11 CST 2016

Total time taken to generate the page: 0.10138 seconds