Right Full Outer Join Query

Question

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.

Answer 1

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)

• http://stackoverflow.com/questions/4826613/natural-join-in-sql-server/4826659#4826659
• http://stackoverflow.com/questions/3277580/sql-server-lack-of-natural-join-x-join-y-usingfield
• http://stackoverflow.com/questions/3063107/is-natural-join-any-better-than-select-from-where-in-terms-of-performance

Answer 2

To answer your second question, the heading of R natural join S is the union of headings of R and S i.e. { A , B , C } -- the curly braces indicate the list of attribute names is unordered -- because the attribute B appears on both relations. As for the tuples...

If @gbn's answer is anything to go by, dogmatic answers are to be encouraged, so here's mine :)

Your tables R and S are not relations because they contain duplicate rows. Your result is not a relation because it contains nulls and duplicate rows.

Nulls are a bad thing and are dangerous. Avoid. Also avoid outer joins in SQL because they are expressly designed to generate nulls.

Instead, use an appropriate default as specified by the DBA or application designer as appropriate. Here I am using zero:

SELECT A, B, C
  FROM R NATURAL JOIN S
UNION
SELECT A, B, 0 AS C
  FROM R
 WHERE B NOT IN (SELECT B FROM S)
UNION
SELECT 0 AS A, B, C
  FROM S
 WHERE B NOT IN (SELECT B FROM R);