你可以得到你想要的结果 UNION 在所有表中，为每个表中不存在的值选择0，然后按 user_id :

SELECT user_id, SUM(gain_count) AS gain_count, 
    SUM(consume_count) AS consume_count, SUM(join_count) AS join_count
FROM (SELECT user_id, gain_count, 0 AS consume_count, 0 AS join_count FROM gain_table
      UNION ALL
      SELECT user_id, 0, consume_count, 0 FROM consume_table
      UNION ALL
      SELECT user_id, 0, 0, join_count FROM join_table) u
GROUP BY user_id

输出：

user_id     gain_count  consume_count   join_count
1           3           0               3
2           4           5               1
3           1           0               0
5           0           4               4
6           0           7               0

下面介绍如何使用ansi标准sql：

SELECT coalesce(g.user_id, c.user_id, j.user_id) user_id
    , coalesce(g.gain_count, 0) gain_count
    , coalesce(c.consume_count, 0) consume_count
    , coalesce(j.join_count,0) join_count
FROM gain_table g
FULL JOIN consume_table c on c.user_id = g.user_id
FULL JOIN join_table j on j.user_id = coalsece(c.user_id, g.user_id)

自92发布以来，这一直是ansi标准的一部分，除了mysql以外的所有主要数据库都可以做到这一点。mysql必须这样做：

SELECT ids.user_id
    , coalesce(g.gain_count, 0) gain_count
    , coalesce(c.consume_count, 0) consume_count
    , coalesce(j.join_count, 0) join_count
FROM (
    SELECT user_id FROM gain_table
    UNION
    SELECT user_id FROM consume_table
    UNION
    SELECT user_id FROM join_table
) ids
LEFT JOIN gain_table g on g.user_id = ids.user_id
LEFT JOIN consume_table c on c.user_id = ids.user_id
LEFT JOIN join_table j on j.user_id = ids.user_id