Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I came across 1 query which is

Select * from R Natural Outer Join S

Where R=(A,B) has tuples {(1,2),(1,2),(3,4)} and S=(B,C) has tuples {(2,5),(2,5),(4,6),(7,10)}.

To implement this I created 2 relations named R and S.

create table R
(
A number(5),
B number(5)
)

create table S
(
B number(5),
C number(5)
)

And I inserted the provided tuples in it.

Now while implementing this I came to know that "Natural Outer Join" is not supported by the Database tool that I am using (Oracle) so I used the following query

select *
from R
natural full outer join S

output

2 1 5
2 1 5
2 1 5
2 1 5
4 3 6
7   10

Now coming to my question

  1. Is "Natural outer join" same as "Natural full outer join"?
  2. How the matching of records are being done here?

Because there is no primary key defined in any of the tables I think it should do cross join and display 16 records which is not the case.

It would be very helpful if anyone can explain this behavior to me.

share|improve this question
3  
Slightly strange example because, despite the notation, neither R nor S is a relation! – onedaywhen Oct 18 '11 at 13:26

A quick check on Wikipedia doesn't mentioned if an "outer join" implies left, right or full when this important bit is omitted.

Practically,

  • "outer join" by iself isn't supported. You normally require LEFT, RIGHT or FULL
  • "natural" means "join on column with the same names"

This means

  • "Natural outer join" won't be recognised
  • "Natural full outer join" is "full outer join" with "natural" matching

Indexs/keys don't matter in this case and make no difference.

The result you get is correct for the standard

select *
from 
   R
   full outer join 
   S ON R.B = S.B

or

select *
from 
   R
   full outer join 
   S USING (B)

Note: not all RDBMS support all syntax:

  • SQL Server doesn't support NATURAL (a good thing)
  • MySQL doesn't support FULL OUTER JOIN (can be worked around)

Natural joins are dangerous anyway (SO links)

share|improve this answer
3  
+1 for first part of the answer, -1 for appending the usual "natural join considered harmful" nonsense. What should my response be? Issue the usual "natural join is fine and dandy" counter-arguments in comments? Post my own answer, copying the first part of your answer and append the usual "natural join is fine and dandy" counter-arguments? – onedaywhen Oct 18 '11 at 11:44
2  
@onedaywhen: he he he. I knew it'd irritate you :) The real world isn't tolerant of NATURAL JOINs especially when OO developers want to use "ID" columns in every table Have fun reading comments and answers at programmers.stackexchange.com/q/114728/5905 Especially from "jim". And my middle link above quotes Bill Karwin – gbn Oct 18 '11 at 12:01
1  
Not using NATURAL JOIN could be considered defensive programming too – gbn Oct 18 '11 at 12:04
    
Thought it was worth a try but let us proceed with the usual game of wiff-waff, then ;) – onedaywhen Oct 18 '11 at 13:06
    
On closer inspection, your first example (using ON syntax) is not equivalent to the OP's natural join. Hint: count the columns in the resultset. – onedaywhen Oct 18 '11 at 13:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.