# 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

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.

• Slightly strange example because, despite the notation, neither `R` nor `S` is a relation! Oct 18, 2011 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)

• +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? Oct 18, 2011 at 11:44
• @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, 2011 at 12:01
• Not using NATURAL JOIN could be considered defensive programming too
– gbn
Oct 18, 2011 at 12:04
• Thought it was worth a try but let us proceed with the usual game of wiff-waff, then ;) Oct 18, 2011 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. Oct 18, 2011 at 13:30