左连接未返回左表全部行？不用NULLIF的SQL查询问题求助

嘿，我来帮你理清楚这个问题～咱们先拆解两种写法的本质区别，你就能明白为啥左连接没得到想要的结果了。

1. 两种查询的核心逻辑差异 #

• 你最初的查询select a.city as source,nullif(b.city,a.city) as destination from TABLENAME a,TABLENAME b是交叉连接（笛卡尔积）：左表的每一行都会和右表的所有行配对，生成所有可能的组合，然后用nullif把b.city和a.city相同的情况转成NULL。所以结果里会包含所有a、b的组合，包括a.city = b.city的场景（此时destination为NULL）。
• 而你尝试的左连接select a.city as Source,b.city as Destination from testq a left join testq b on a.city<>b.city，左连接的逻辑是：先根据on条件匹配右表的行，只有当左表的某一行在右表中找不到任何满足a.city<>b.city的行时，才会保留左表行并填充右表列为NULL。如果左表的某个city在右表中存在其他不同的city，那左表的这行会和所有满足条件的右表行连接，不会出现NULL的情况；只有当左表的city在右表中完全没有不同的city（比如右表只有和左表相同的city），才会返回NULL。

2. 举个实际例子更直观 #

假设testq表的数据是：

• 交叉连接+nullif的结果是4行：

  destination
  NULL
  上海
  北京
  NULL

• 你的左连接查询结果是2行：

  Destination
  上海
  北京

这就很明显了：左连接过滤掉了a.city = b.city的组合，而你想要的是保留这些组合并把destination设为NULL，所以结果不符合预期。

3. 不用nullif的替代方案 #

如果不想用nullif函数，咱们可以用case语句替代交叉连接里的逻辑，效果和原来的查询完全一样：

select 
    a.city as source,
    case when b.city = a.city then null else b.city end as destination
from testq a, testq b

如果一定要用左连接的话，其实不太适合这个场景——因为左连接的核心是保留左表所有行，但只匹配满足on条件的右表行，而你需要的是所有右表行的组合，只是修改其中特定值。所以交叉连接+case是更直接的替代方案。

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