使用FULL OUTER JOIN合并两表时非匹配行主键缺失如何解决

问题原因 #

你当前查询仅选择了左表ta的AssetID字段，当出现仅右表tb存在的匹配行时，左表所有字段返回NULL，因此对应行的AssetID为空。

修正方案 #

使用标准SQL的COALESCE函数，优先取左表的AssetID，左表为空时自动取右表的AssetID，即可覆盖所有场景下的主键值。

修正后的查询语句如下：

SELECT 
  COALESCE(ta.AssetID, tb.AssetID) AS AssetID,
  ta.Valuable,
  tb.Protected
FROM (
    SELECT AssetID, Valuable
    FROM TableA
) ta    
FULL OUTER JOIN (
    SELECT AssetID, Protected
    FROM TableB   
) tb ON ta.AssetID = tb.AssetID

补充说明 #

COALESCE是SQL标准函数，几乎所有主流数据库（PostgreSQL、MySQL 8.0+、SQL Server、Oracle等）都支持。如果你使用的是不支持FULL OUTER JOIN的低版本MySQL，可以用UNION ALL拼接左连接和右连接去重的结果实现同等效果。

内容的提问来源于stack exchange，提问作者Maxcot