1

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 |
--------------------------------------------------
  • It might be helpful if you showed the output from SHOW CREATE TABLE table_a\G (and the others) and the extra columns you wish to include. As for figuring out how much harder it would be, why not use your test system? Or don't you have one? You can run an EXPLAIN EXTENDED on MySQL – Vérace Oct 9 '17 at 17:48
  • Thanks for the comment! I'll update the question now with the output you suggest. I'll look into running EXPLAIN EXTENDED on our existing query to see if we can get a benchmark. – jnthnclrk Oct 9 '17 at 18:37
  • Unfortunately, EXPLAIN EXTENDED gives away too much of our internal app's structure. I can't really post that here. – jnthnclrk Oct 9 '17 at 18:43
  • Do you want one of the 3 tables (say a) to be on the left side and the other 2 on the right side? Or 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? – ypercubeᵀᴹ Oct 9 '17 at 19:55
2

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
 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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