Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
Slightly strange example because, despite the notation, neither R nor S is a relation! –  onedaywhen Oct 18 '11 at 13:26

2 Answers 2

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)

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

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);
share|improve this answer
2  
-1 "Nulls are a bad thing and are dangerous. Avoid." Whatever nulls are they are far better than using special values like '0' –  Jack Douglas Oct 18 '11 at 18:39
2  
So, the conclusion is that Nulls are better and dangerous. I like that, it sounds like Beautiful and Wild. –  ypercube Oct 18 '11 at 20:42
    
@JackDouglas: the null value is an extra special value e.g. three-valued logic is "one better" than conventional logic, right? ;) BTW I copied+pasted the term "null value" from the SQL-92 spec, so I know it is a value. Some advice, please: should I now downvote gbn's answer because is says "Natural joins are dangerous" and I don't agree? Do I downvote all your answers that fail to renounce the use of nulls? –  onedaywhen Oct 19 '11 at 7:37
1  
@JackDouglas: it would only result in the same: one up and one down :) p.s. I trust you realised I had my tongue firmly embedded in cheek. I am aware of your stance on nulls and it is a healthy one IMO. –  onedaywhen Oct 19 '11 at 7:52
2  
The case against nulls and SQL's undocumented and inconsistent three-valued logic is well documented. In a nutshell, they are unintuitive to start with, traditional logic cannot be applied and SQL's inconsistencies mean that all the cases must be learned and explicitly handled. Failure to adhere to this approach results in frequent errors in SQL code and applications based on them. I think anyone who has worked with SQL-based applications will agree that bugs attributable to nulls are common. I think we only disagree on whether nulls are a necessary evil or just plain evil ;) –  onedaywhen Oct 20 '11 at 8:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.