为什么这个左外部联接包含行两次

Question

在以下情况下：

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

退货(在中执行http://sqlfiddle.com/#！9/cae8023/5(：

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值的左侧行
所以如果这是正确的，为什么在小提琴输出中我们有1 34 Jill 1 42 Shawn鲍勃和吉尔的争吵又发生了？

Answer 1

连接行的条件是groupId等于o.age < b.age。

鲍勃的年龄是32岁。这还不到吉尔34岁的年龄。这也低于肖恩42岁的年龄。因此，在两对相连的行中满足该条件。

联接的行具有引用为o的行中的所有列和引用为b的行中所有列。

请注意，您已经为Shawn输入了两行。Bob的行实际上与Jill的行相匹配，Shawn的行都。所以你有三排给鲍勃。

---

当我在本地MySQL实例(8.0.31(上测试您的查询时，我按以下顺序得到结果，这与您的sqlfiddle结果不同：

+---------+------+--------+---------+------+--------+
| groupId | age  | Person | groupId | age  | Person |
+---------+------+--------+---------+------+--------+
|       1 |   32 | Bob    |       1 |   42 | Shawn  |
|       1 |   32 | Bob    |       1 |   42 | Shawn  |
|       1 |   32 | Bob    |       1 |   34 | Jill   |
|       1 |   34 | Jill   |       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 |   39 | Laura  |
|       2 |   29 | Jake   |       2 |   36 | Paul   |
|       2 |   36 | Paul   |       2 |   39 | Laura  |
|       2 |   39 | Laura  |    NULL | NULL | NULL   |
+---------+------+--------+---------+------+--------+

如果没有显式ORDERBY子句，InnoDB的默认行为是按从索引中读取行的顺序返回行。在这种情况下，它对两个表都使用主键顺序，因为没有其他索引来优化联接。您可以看到左表中列的顺序与主键顺序相匹配。

我不知道如何解释为什么Bob Shawn行在Bob Jill行之前，因为这不是联接表的主键顺序。可能是在执行未编制索引的联接时，联接缓冲区中的顺序被打乱了。

sqlfiddle可能正在客户端中执行一些重新排序行的操作。

Answer 2

您插入了两次(Shawn(的记录。您的查询应该是：

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('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   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)