Natural full outer join?

Question

There are three relations (t1, t2, t3):

t1
-----------
 a  | b

 1  | 2
----------- 

t2
------------
  b  |  c

  2  | 3
------------

t3
-------------
  a  |  c

  4  |  5
-------------

The query is:

select * from t1 natural full outer join (t2 natural full outer join t3);

The result of select * from t2 natural full outer join t3 is:

-----------------
  a  |  b  |  c

     | 2   | 3

  4  |     | 5
------------------

then I tried:

select * from t1 natural full outer join (result)

    t1             result
-----------      -----------------
 a  | b           a    |  b  | c

 1  | 2                | 2   | 3

                    4  |     | 5
                 ------------------

Shouldn't this be:

--------------------------
   a    |  b  |  c

   1    |  2  | 3

  4     |     | 5

But I don't know why the sql query give:

 a | b | c 

 4 |   | 5
   | 2 | 3
 1 | 2 |

Full outer join doesn't exist in mysql. If you want to emulate it, check out this post: http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql — Grumpy, Jan 26 '14 at 13:12

score 1 · Answer 1 · answered Jan 26 '14 at 13:09

Your query:

select *
from t1 natural full outer join
     result

Is equivalent to:

select *
from t1 full outer join
     result
     on t1.a = result.a and t1.b = result.b;

The natural join looks at all the fields in common, not just one. There are no rows that match, which is why you are getting three rows of results.

You seem to want:

select *
from t1 full outer join
     result
     on t1.b = result.b;

In general, it is better to avoid natural join's, because they "hide" information about what the query is doing and can readily lead to mistakes/unexpected results as you experienced.

score 0 · Answer 2 · answered Jan 26 '14 at 13:13

I don't know if you knows how FULL OUTER JOIN works.

The FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause.

So, if you want this result:

--------------------------
   a    |  b  |  c

   1    |  2  | 3

   4    |     | 5

With these tables:

    t1             result
-----------      -----------------
 a  | b           a    |  b  | c

 1  | 2                | 2   | 3

                    4  |     | 5

You should do this query:

SELECT * FROM t1 natural RIGHT JOIN (result) res ON t1.b = res.b

because you must specify with which column have to be joined.

Natural full outer join?

2 Answers2