MySQL UNION只包含第一个表

Question

我需要在两个表上执行一个完整的OUTER JOIN，我试图在MySQL中使用LEFT JOIN/RIGHT JOIN/UNION ALL技术实现它。

原表如下:

giving_totals:
+--------------+---------------+-------------+
| country_iso2 | total_given   | supersector |
+--------------+---------------+-------------+
| AE           | 1396986989.02 |           3 |
| AE           |  596757809.20 |           4 |
| AE           |  551810209.87 |           5 |
| AE           |   25898255.77 |           7 |
| AE           |      32817.63 |           9 |
...
+--------------+---------------+-------------+
receiving_totals:
 +--------------+----------------+-------------+
| country_iso2 | total_received | supersector |
+--------------+----------------+-------------+
| AE           |    34759000.00 |           3 |
| AE           |      148793.82 |           7 |
| AE           |         734.30 |           9 |
| AF           |  6594479965.85 |           1 |
| AF           |  2559712971.26 |           2 |
+--------------+----------------+-------------+

我希望结果表对每个超级部门代码的每个国家都有一个条目，即使它没有为该部门提供或接收资金(这来自AidData项目数据集，以防有人熟悉)。我想通过左JOIN(获取所有给出的条目)和右JOIN(获取所有接收条目)的UNION来实现这一点。下面是我尝试的查询:

SELECT g.country_iso2 AS country_iso2, g.total_given AS `total_given`,R.total_received AS `total_received`,g.supersector AS `supersector` 
FROM (`giving_totals` `g` 
LEFT JOIN `receiving_totals` `r` 
ON(((g.country_iso2 = r.country_iso2) 
AND (g.supersector = r.supersector)))) 
UNION ALL
SELECT g.country_iso2 AS country_iso2, g.total_given AS `total_given`,R.total_received    AS `total_received`,g.supersector AS `supersector` 
FROM (`giving_totals` `g` 
RIGHT JOIN `receiving_totals` `r` 
ON(((g.country_iso2 = r.country_iso2) 
AND (g.supersector = r.supersector)))) 

但是这只返回第一个连接，不管我是把右连接还是左连接放在第一位。我想我可能误解了UNION操作，因为每个个体连接返回的都是我所期望的。

Answer 1

这是另一种执行full outer join的方法:

SELECT driver.country_iso2 AS country_iso2,
       g.total_given AS `total_given`,
       R.total_received AS `total_received`,
       driver.supersector AS `supersector` 
from ((select distinct country_iso2, supersector
       from giving_totals
      ) union
      (select distinct country_iso2, supersector
       from receiving_totals
      )
     ) driver left outer join
     giving_totals gt
     on gt.country_iso2 = driver.country_iso2 and
        gt.supersector = driver.country_iso2 left outer join
     receiving_totals rt
     on rt.country_iso2 = driver.country_iso2 and
        rt.supersector = driver.country_iso2

也就是说，将联合作为子查询来获得您感兴趣的所有组合。然后您可以对该表执行left outer join操作。

问题的原因是第二个查询中的别名。你可以试试这个:

SELECT r.country_iso2 AS country_iso2, g.total_given AS `total_given`,R.total_received    AS `total_received`,r.supersector AS `supersector` 
FROM (`giving_totals` `g` 
RIGHT JOIN `receiving_totals` `r` 
ON(((g.country_iso2 = r.country_iso2) 
AND (g.supersector = r.supersector)))) 

原始表单将为这些值设置null