I came across 1 query which is
Select * from R Natural Outer Join S
R=(A,B) has tuples
S=(B,C) has tuples
To implement this I created 2 relations named
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
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.
- “outer join” by iself isn’t supported. You normally require LEFT, RIGHT or FULL
- “natural” means “join on column with the same names”
- “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
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)