如何在MySQL JOIN中处理NULL值？关联t1与t2获取population字段

表结构与数据 #

表t1 #

表t2 #

问题原因 #

原SQL语句JOIN ON t1.nation=t2.nation AND t1.state=t2.state AND t1.region=t2.region失效，是因为SQL中NULL值无法用=判定相等，NULL = NULL的结果为未知，导致包含空值的行无法匹配。

可行解决方案 #

1. 用COALESCE统一空值映射 #

把NULL替换成一个不会和真实数据冲突的特殊值（比如空字符串），让=能正常匹配：

SELECT t1.*, t2.population
FROM t1
JOIN t2 
  ON COALESCE(t1.nation, '') = COALESCE(t2.nation, '')
  AND COALESCE(t1.state, '') = COALESCE(t2.state, '')
  AND COALESCE(t1.region, '') = COALESCE(t2.region, '')

如果业务中字段本身会用到空字符串，就换成其他标记（比如'__NULL__'）即可。

2. 使用IS NOT DISTINCT FROM（兼容部分数据库） #

PostgreSQL、SQL Server 2022+、Oracle 12cR1+等数据库支持该运算符，它直接将NULL视为相等：

SELECT t1.*, t2.population
FROM t1
JOIN t2 
  ON t1.nation IS NOT DISTINCT FROM t2.nation
  AND t1.state IS NOT DISTINCT FROM t2.state
  AND t1.region IS NOT DISTINCT FROM t2.region

3. 手动判断NULL相等（全兼容写法） #

如果数据库不支持上面的运算符，用OR逐个字段处理NULL匹配，兼容性拉满：

SELECT t1.*, t2.population
FROM t1
JOIN t2 
  ON (t1.nation = t2.nation OR (t1.nation IS NULL AND t2.nation IS NULL))
  AND (t1.state = t2.state OR (t1.state IS NULL AND t2.state IS NULL))
  AND (t1.region = t2.region OR (t1.region IS NULL AND t2.region IS NULL))

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