一张表与自身进行左外连接：这是如何工作的？

Question

我正在阅读书籍 SQL反模式 并试图理解其利用自引用表构建“树”的示例，如下所示：

                         Comments
-------------------------------------------------------------------
  comment_id | parent_id | author |      comment
-------------------------------------------------------------------
       1          NULL     Fran      What's the cause of this bug?
       2            1      Ollie     I think it's a null pointer.
       3            2      Fran      No, I checked for that.
       4            1      Kukla     We need to check for invalid input.
       5            4      Ollie     Yes, that's a bug.
       6            4      Fran      Yes, please add a check.
       7            6      Kukla     That fixed it.

所以这本书说：

You can retrive a comment and its immediate children using a relatively simple query:

SELECT c1.*, c2.*
FROM Comments c1 LEFT OUTER JOIN Comments c2
  ON c2.parent_id = c1.comment_id

我正在尝试理解它是如何工作的。我一直以来对于表格t1和t2之间的左外连接理解是，您需要从t1中取出所有行，并且对于不满足ON子句的行，您需要为第二个表中的列填充NULL。在这种情况下只有一个表格，但我可以想象查询是在两个表格中进行的，其中第二个表格是第一个表格的副本。即使如此，我仍然不明白该查询返回了什么。

树的两个级别。

请问结果表格是什么样的，您能否向我讲述产生它的逻辑？

- user5648283

Answer 1

生成的表格将如下所示：

                         Comments
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
  c1.comment_id | c1.parent_id | c1.author |  c1.comment                          | c2.comment_id | c2.parent_id | c2.author | c2.comment                         | 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
       1               NULL      Fran         What's the cause of this bug?             2               1         Ollie        I think it's a null pointer.
       1               NULL      Fran         What's the cause of this bug?             4               1         Kukla        We need to check for invalid input. 
       2               1         Ollie        I think it's a null pointer.              3               2         Fran         No, I checked for that.                                                      
       3               2         Fran         No, I checked for that.                   NULL            NULL      NULL         NULL                                
       4               1         Kukla        We need to check for invalid input.       5               4         Ollie        Yes, that's a bug.                 
       4               1         Kukla        We need to check for invalid input.       6               4         Fran         Yes, please add a check.           
       5               4         Ollie        Yes, that's a bug.                        NULL            NULL      NULL         NULL                                
       6               4         Fran         Yes, please add a check.                  7               6         Kukla        That fixed it.
       7               6         Kukla        That fixed it.                            NULL            NULL      NULL         NULL

在ON子句中，我们有c2.parent_id = c1.comment_id。这意味着“右”表（c2）的parent.id将与“左”表（c1）的comment_id连接。

表通过将每行与其子评论进行映射来分支出自身。右侧结果（c2）将是c1条目的所有子级，每个子级都会重复。由于我们在执行左连接，没有子级的行将在c2列上简单返回NULL。

- Carlos Miranda

Answer 2

您的理解是正确的- 是的，您应该将其视为与自身副本的左连接。它表示一个深度级别的原因是因为每一行代表一个评论，但它可能有一个指向父评论（parent_id）的链接，这意味着您可以将此表转换为树以帮助直观理解。因为您要进行左连接，所以您要将每个评论与所有子元素匹配。如果它没有任何子元素，则子列将为空（左连接的结果）。如果您绘制出树形图，您可以取每个节点并将其及其所有直接子节点圆圈起来，然后您将看到这是您的结果集。如果再次进行连接，您将深入2个级别，并获得子级的子级等。

编辑：添加了图片（抱歉艺术不佳），您应该注意到7个圆圈（原始表中每行1个），以及9行结果（额外的2行来自评论1和4各自具有2个子项）。

- Arin Taylor