SQL全外连接：左表字段为NULL时如何取右表对应值？

一、修改原SQL语句（直接解决NULL问题） #

你遇到的问题是全外连接后，Xbox独占游戏在左表（PS4）无对应记录，导致p.Game显示NULL。使用COALESCE()函数即可解决——它会返回参数列表里第一个非NULL的值。同时Genre和Publisher字段也存在相同的NULL问题，建议一并处理。另外原SQL最后一个字段后多了个逗号，需要删除以避免语法错误：

SELECT
  COALESCE(p.Game, x.Game) AS Game,
  COALESCE(p.Genre, x.Genre) AS Genre,
  COALESCE(p.Publisher, x.Publisher) AS Publisher,
  p.North_America AS ps_na,
  p.Europe AS ps_eur,
  p.Japan AS ps_japan,
  p.Rest_of_World AS ps_row,
  x.North_America AS xbox_na,
  x.Europe AS xbox_eur,
  x.Japan AS xbox_japan,
  x.Rest_of_World AS xbox_row
FROM 
  games.ps4 AS p
  FULL JOIN 
    games.xboxone AS x
    USING(Game)

二、更简便的表合并思路 #

如果你的目标是整合所有游戏的跨平台销售数据，也可以先将两个表统一结构后纵向合并，再通过分组聚合实现横向展示（适合后续扩展更多平台的场景）：

WITH combined_sales AS (
  SELECT
    Game,
    Genre,
    Publisher,
    North_America AS na_sales,
    Europe AS eur_sales,
    Japan AS japan_sales,
    Rest_of_World AS row_sales,
    'PS4' AS platform
  FROM games.ps4
  UNION ALL
  SELECT
    Game,
    Genre,
    Publisher,
    North_America,
    Europe,
    Japan,
    Rest_of_World,
    'XboxOne' AS platform
  FROM games.xboxone
)
SELECT
  Game,
  MAX(Genre) AS Genre,
  MAX(Publisher) AS Publisher,
  MAX(CASE WHEN platform = 'PS4' THEN na_sales END) AS ps_na,
  MAX(CASE WHEN platform = 'PS4' THEN eur_sales END) AS ps_eur,
  MAX(CASE WHEN platform = 'PS4' THEN japan_sales END) AS ps_japan,
  MAX(CASE WHEN platform = 'PS4' THEN row_sales END) AS ps_row,
  MAX(CASE WHEN platform = 'XboxOne' THEN na_sales END) AS xbox_na,
  MAX(CASE WHEN platform = 'XboxOne' THEN eur_sales END) AS xbox_eur,
  MAX(CASE WHEN platform = 'XboxOne' THEN japan_sales END) AS xbox_japan,
  MAX(CASE WHEN platform = 'XboxOne' THEN row_sales END) AS xbox_row
FROM combined_sales
GROUP BY Game

这种方法的优势是后续新增其他平台（比如Switch）时，只需在UNION ALL中添加对应表即可，无需修改JOIN逻辑。

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