了解SQL自联接

Question

我正在尝试理解SQL自连接。我有下面的表'persons‘，其中有员工的名字、姓氏、地址和城市(来源W3Schools)：

mysql> select * from persons;
+------+-----------+-----------+--------------+-----------+
| P_id | LastName  | FirstName | Address      | City      |
+------+-----------+-----------+--------------+-----------+
|    1 | Hansen    | Ola       | Timoteivn 10 | Sandnes   | 
|    2 | Svendson  | Tove      | Borgvn 23    | Sandnes   |
|    3 | Pettersen | Kari      | Storgt 20    | Stavanger |
+------+-----------+-----------+--------------+-----------+

现在我想返回与“Hansen Ola”具有相同城市的员工的姓名。所以我写了一个自连接，它工作得很好：

mysql> select p1.Lastname, p1.firstname from persons p1, persons p2 where p1.city =    p2.city and p2.lastname = 'Hansen';
+----------+-----------+
| Lastname | firstname |
+----------+-----------+
| Hansen   | Ola       |
| Svendson | Tove      |
+----------+-----------+

但是，如果我将别名p2更改为p1，即将p2.lastname = 'Hansen‘更改为p1.lastname = 'Hansen'，则无法获得两个雇员的姓名。

mysql> select p1.Lastname, p1.firstname from persons p1, persons p2 where p1.city = p2.city and p1.lastname = 'Hansen';
+----------+-----------+
| Lastname | firstname |
+----------+-----------+
| Hansen   | Ola       |
| Hansen   | Ola       |
+----------+-----------+

有人能帮我理解一下为什么把别名从p2改成p1会改变结果吗？谢谢。

Answer 1

让我突出显示您的问题，希望它会更有意义：

select p1.lastname, ...
from persons p1, persons p2
where ... and p1.lastname = 'Hansen'

因此，您将结果限制为只返回第一个表p1中的记录。同时，您将第一个表限制为只包含lastname = 'Hansen‘的表。如果您想象一下不受限制的结果集，它可能会更有意义(我删除了WHERE子句的不重要部分，以显示完整的产品)：

select p1.Lastname as p1_lastname, p2.lastname as p2_lastname
from persons p1, persons p2
where p1.city = p2.city

P1_LASTNAME     P2_LASTNAME
Hansen  Hansen
Svendson    Hansen
Hansen  Svendson
Svendson    Svendson
Pettersen   Pettersen

如果你使用上面的结果集并添加条件p1.lastname = "Hansen"，难怪你只能从中得到'Hansen‘。

Answer 2

这个查询有点奇怪，我不确定它在现实中有什么基础。本质上，它将一个表与其自身联接起来，其中city列与之匹配，然后指定其中一个表的lastname，这限制了结果。如果执行SELECT *而不是仅选择有限的行，则可以看到表正在与自身交叉联接，并且返回由city和lastname要求过滤的行的叉积。实际结果是：

p1.firstname, p1.lastname, p2.firstname, p2.lastname
Hansen        Ola          Hansen        Ola
Hansen        Ola          Svendson      Toda

这在原始查询中是颠倒的，因为条件是颠倒的。

Answer 3

您的查询只返回那些包含“Hansen”的行。它不是跟随自联接。