Right Full Outer Join Query

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

• Is "Natural outer join" same as "Natural full outer join"?

• 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.

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)

• https://stackoverflow.com/questions/4826613/natural-join-in-sql-server/4826659#4826659

• https://stackoverflow.com/questions/3277580/sql-server-lack-of-natural-join-x-join-y-usingfield

• https://stackoverflow.com/questions/3063107/is-natural-join-any-better-than-select-from-where-in-terms-of-performance

StackExchange Database Administrators Q#6908, answer score: 6