Mysql – How to three way inner join to three way outer join in MySQL

join;MySQLsumtableunique-constraint

We are currently joining three MySQL tables based on the summation of a common key within each table:

select a.common_key, a.val, b.val, c.val from
  (select common_key, sum(val) val from table_a group by common_key) as a,
  (select common_key, sum(val) val from table_b group by common_key) as b,
  (select common_key, sum(val) val from table_c group by common_key) as c
where a.common_key=b.common_key and a.common_key=c.common_key;

This works fine, but now we want to include in our results values from a, b or c for which there is no matching values in the other tables.

In other words, we want to go from a three way inner join to a three way outer join, but we are having a hard time figuring out both the syntax and
also how much harder a processing task it would be for our database.

As requested, here is what we get from SHOW CREATE TABLE table_a\G:

+---------+-----------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                      |
+---------+-----------------------------------------------------------------------------------------------------------------------------------+
| table_a | CREATE TABLE `table_a` (
  `common_key` int(11) DEFAULT NULL,
  `val` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
--------------------------------------------------

Best Answer

If you want to include in the results values from any of a, b or c for which there is no matching values in the other tables, you can first find all distinct values for the three tables and then LEFT JOIN to the derived tables you already have:

select 
    d.common_key, 
    a.val, b.val, c.val 
from
    ( select common_key from table_a
      union distinct
      select common_key from table_b
      union distinct
      select common_key from table_c
    ) as d
  left join
      (select common_key, sum(val) val from table_a group by common_key) as a
    on d.common_key = a.common_key
  left join
      (select common_key, sum(val) val from table_b group by common_key) as b
    on d.common_key = b.common_key
  left join
      (select common_key, sum(val) val from table_c group by common_key) as c
    on d.common_key = c.common_key
 ;
Related Question