用不同的ON从句理解自我连接

Question

这是我的桌子结构：

// mytable
+----+---------+----------+
| id | related | subject  |
+----+---------+----------+
| 1  | NULL    | subject1 |
| 2  | 1       |          |
+----+---------+----------+

在我看来，有两个查询看起来是相同的，但是在测试中不同的结果：

SELECT a.id, IFNULL(b.subject, a.subject)
FROM mytable a
LEFT JOIN mytable b ON a.id = b.related

    +----+----------+
    | 1  | subject1 |
    | 2  |          |
    +----+----------+

SELECT a.id, IFNULL(b.subject, a.subject)
FROM mytable a
LEFT JOIN mytable b ON b.id = a.related

    +----+----------+
    | 1  | subject1 |
    | 2  | subject1 |
    +----+----------+

看，这是self-join。那么为什么ON a.id = b.related和ON b.id = a.related的结果是不同的呢？

Answer 1

这两个查询都从a获取所有行。

这两个查询都是对b进行外部连接。

不同的是用于从b中找到“匹配”的条件。

(查询可能看起来是相同的，但事实是它们有很大的不同。)

作为演示，运行如下所示的查询：

SELECT a.id             AS `a_id`
     , a.related        AS `a_related`
     , a.subject        AS `a_subject`
     , b.id             AS `b_id`
     , b.related        AS `b_related`
     , b.subject        AS `b_subject`
  FROM mytable a
  LEFT
  JOIN mytable b
    ON b.related = a.id

然后更改ON子句

    ON b.id = a.related

您还可能希望重复这两个查询，删除LEFT关键字(使其成为内部连接而不是外部连接)。

一种看待外部连接的方法..。当找不到来自b的匹配行时，就会发明来自b的虚拟行。该虚拟行完全由空值组成，虚拟行被连接到a，就好像它是一个匹配的行。(这并不一定是数据库引擎实际做的事情，但通过这种方式，我们可以了解外部联接返回的结果。)

仔细查看查询的结果，您将能够了解查询的结果为何不同。

a和b引用同一个表是一个特例。如果是两个不同的表格，我们就会看到同样的结果。这真的不重要..。对于查询，这是两个不同的源，它们恰好引用同一个表。不要让a和b引用同一个表这一事实造成任何混淆。

Answer 2

使用SELECT *运行查询，以揭开一些谜团：

您的第一个查询：

SELECT *
FROM mytable a
LEFT JOIN mytable b ON a.id = b.related;

产生以下情况：

+----+---------+----------+--------+----------+----------+
| id | related | subject  |  id1   | related1 | subject1 |
+----+---------+----------+--------+----------+----------+
|  2 | 1       | <null>   | <null> | <null>   | <null>   |
|  1 | <null>  | subject1 | 2      | 1        | <null>   |
+----+---------+----------+--------+----------+----------+

您的第二个查询：

SELECT *
FROM mytable a
LEFT JOIN mytable b ON b.id = a.related;

产生这种情况：

+----+---------+----------+--------+----------+----------+
| id | related | subject  |  id1   | related1 | subject1 |
+----+---------+----------+--------+----------+----------+
|  2 | 1       | <null>   | 1      | <null>   | subject1 |
|  1 | <null>  | subject1 | <null> | <null>   | <null>   |
+----+---------+----------+--------+----------+----------+

第一个查询是将id 2连接到相关的2。没有相关的2，而且由于id 2没有主题，所以您的ifnull()中没有subject。

第二个查询是将相关的1加入到id 1 for a.id 2。这从b.id 1中提取了一个主题，结果得到一个id 2的主题。

您确实需要在这里思考LEFT JOIN是如何工作的，以及它是如何受到ON子句的影响的。因此，这里有两个非常不同的查询。