你可以用一个 full join :

select coalesce(onl.col1, ofl.col1) as col1,
       coalesce(onl.col2, ofl.col2) as col2,
       onl.score, ofl.score
from (select onl.*
      from online onl
      where onl.col1 = 'a'
     ) onl full join
     (select ofl.*
      from offline ofl
      where ofl.col1 = 'a'
     ) ofl
     on onl.col1 = ofl.col1 and onl.col2 = ofl.col2;

过滤是很棘手的 full join ，这就是它使用子查询的原因。

使用下面的查询！

SELECT online.col1
    ,online.col2
    ,coalesce(online.score, 0) AS onlinescore
    ,coalesce(offlilne.score, 0) AS offlinescore
FROM online
INNER JOIN offline
    ON online.col1 = offline.col1
    AND online.col2 = offline.col2
UNION ALL
SELECT online.col1
    ,online.col2
    ,coalesce(online.score, 0) AS onlinescore
    ,'' AS offlinescore
FROM online
LEFT JOIN offline
    ON online.col1 = offline.col1
    AND online.col2 = offline.col2
WHERE offline.col1 IS NULL
UNION ALL
SELECT offline.col1
    ,offline.col2
    ,'' AS onlinescore
    ,coalesce(offline.score, 0) AS offlinescore
FROM offline
LEFT JOIN online
    ON online.col1 = offline.col1
    AND online.col2 = offline.col2
WHERE online.col1 IS NULL