为何左外连接（LEFT OUTER JOIN）会返回重复行？

表结构与数据插入 #

首先创建Persons表并插入数据：

CREATE TABLE Persons (
    groupId int,
    age int,
    Person varchar(255)
);

insert into Persons (Person, groupId, age) values('Bob'  , 1     , 32);
insert into Persons (Person, groupId, age) values('Jill'  , 1     , 34);
insert into Persons (Person, groupId, age) values('Shawn'  , 1     , 42);
insert into Persons (Person, groupId, age) values('Shawn'  , 1     , 42);
insert into Persons (Person, groupId, age) values('Jake'  , 2     , 29);
insert into Persons (Person, groupId, age) values('Paul'  , 2     , 36);
insert into Persons (Person, groupId, age) values('Laura'  , 2     , 39);

执行的查询语句 #

SELECT *
FROM `Persons` o                    
  LEFT JOIN `Persons` b             
      ON o.groupId = b.groupId AND o.age < b.age

实际返回结果 #

1   32  Bob 1   34  Jill
1   32  Bob 1   42  Shawn
1   34  Jill    1   42  Shawn
1   32  Bob 1   42  Shawn
1   34  Jill    1   42  Shawn
1   42  Shawn   (null)  (null)  (null)
1   42  Shawn   (null)  (null)  (null)
2   29  Jake    2   36  Paul
2   29  Jake    2   39  Laura
2   36  Paul    2   39  Laura
2   39  Laura   (null)  (null)  (null)

预期结果 #

1   32  Bob 1   34  Jill
1   32  Bob 1   42  Shawn
1   34  Jill    1   42  Shawn
1   42  Shawn   (null)  (null)  (null)
2   29  Jake    2   36  Paul
2   29  Jake    2   39  Laura
2   39  Laura   (null)  (null)  (null)

疑问 #

我理解左连接是取左表的每一行，尝试与右表的每一行匹配，匹配成功则添加该行，匹配失败则保留左表行并将右表列设为null。如果这个理解正确，为什么查询结果中Bob和Jill的行出现了重复？

问题根源是右表（作为连接对象的Persons表）里存在两条完全重复的Shawn记录（groupId=1，age=42）。

你对左连接的逻辑理解没错，但漏了一个关键细节：左表的每一行会和右表中所有满足连接条件的行一一匹配，而不是只匹配一次符合条件的行。

具体拆解：

• 左表的Bob（age=32，groupId=1）：右表里符合groupId=1且年龄大于32的行有3条——1条Jill、2条Shawn。所以Bob会和这3条分别匹配，生成3条结果，其中两条是和不同的Shawn行匹配的，看起来就像Bob的行重复了。
• 左表的Jill（age=34，groupId=1）：右表里符合条件的是两条Shawn记录，因此会生成2条匹配结果，也就出现了重复的Jill行。
• 左表的两条Shawn记录：右表里没有同组且年龄更大的记录，所以各自返回一条带null的行。

如果要得到你预期的去重结果，有两种常见解决方法：

1. 直接对查询结果去重，使用DISTINCT关键字：

SELECT DISTINCT *
FROM `Persons` o                    
  LEFT JOIN `Persons` b             
      ON o.groupId = b.groupId AND o.age < b.age

2. 先对右表的重复数据去重，再执行连接：

SELECT o.*, b.*
FROM `Persons` o                    
LEFT JOIN (
    SELECT DISTINCT groupId, age, Person 
    FROM `Persons`
) b ON o.groupId = b.groupId AND o.age < b.age

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