你可以用 UNION ALL 然后在主查询中聚合为

SELECT id, name, SUM(count1) AS count1, SUM(count2) AS count2
  FROM
 (
  SELECT id, name, count1, 0 AS count2 
    FROM table1
   UNION ALL
  SELECT id, name, 0, count2  
    FROM table2
 ) q
GROUP BY id, name

你可以使用 full join :

SELECT COALESCE(t1.id, t2.id) as id, COALESCE(t1.name, t2.name) as name,
       COALESCE(t1.count1, 0) as count1, COALESCE(t2.count2, 0) as count2
table1.count1 as count1, table2.count2 as count2
FROM table1 t1 FULL JOIN
     table2 t2 
     ON t1.id = t2.id and t1.name = t2.name;

如果您的数据库不支持 full join ，那么你基本上有两个选择。一个只使用 LEFT JOIN :

SELECT ni.id, ni.name,
       COALESCE(t1.count1, 0) as count1, COALESCE(t2.count2, 0) as count2
table1.count1 as count1, table2.count2 as count2
FROM ((SELECT id, name FROM table1
      ) UNION  -- on purpose to remove duplicates
      (SELECT id, name FROM table2
      )
     ) ni LEFT JOIN
     table1 t1
     ON t1.id = ni.id AND ni.name = t1.name
     table2 t2 
     ON ni.id = t2.id and ni.name = t2.name;

第二种方法是使用 UNION ALL ，不是 UNION :

SELECT t1.id, t1.name, t1.count1, COALESCE(t2.count2, 0) as count2
FROM table1 t1 LEFT JOIN
     table2 t2 
     ON t1.id = t2.id and t1.name = t2.name
UNION ALL
SELECT t2.id, t2.name, 0, t2.count2
FROM table2 t2 LEFT JOIN
     table1 t1
     ON t1.id = t2.id and t1.name = t2.name
WHERE t1.id IS NULL;

我不知道“规范”方法在哪里使用 UNION 来自。我认为这是一个关于亲近的思考练习。但是，这是一个非常糟糕的解决方案，原因如下： UNION 增加了删除重复项的额外开销。而这不是 FULL JOIN 功能。
缺乏 WHERE 第二个查询上的子句确保如果存在任何匹配项，则存在重复项。
使用 UNION 删除每个子查询中的重复项。不管是否可取，这都不是问题所在 FULL JOIN 作品。