How perform natural full outer join in table having multiple attributes common?

Question

mysql> select * from r;

+------+------+------+
| A    | B    | C    |
| 1    | 2    | 3    |
| 1    | 2    | 4    |
| 2    | 1    | 3    |
| 3    | 1    | 3    |
+------+------+------+

mysql> select * from s;

+------+------+------+
| A    | B    | D    |
| 1    | 2    | 1    |
| 2    | 1    | 5    |
| 4    | 2    | 1    |
| 3    | 2    | 1    |
+------+------+------+

Now I want to perform natural full outer join. I have tried left outer join and right outer join and full outer join but they are performing while have taken only 1 element in common.

But how will it be done here.

The question is asking for number of rows contain null entries in table r natural full outer join s.

Answer 1

You can "chain" left joins to implement a very good approximation to a full join:

select *
from (select a, b from r
      union -- on purpose to remove duplicates
      select a, b from s
     ) rs left join
     r
     using (a, b) left join
     s
     using (a, b);

This becomes trickier if a/b could have NULL values in the r or s. If that is the case, ask a new question with appropriate sample data and desired results.