Right Full Outer Join Query

database-theoryjoin;

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.

Best Answer

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)

Related Question