sql - 表左外连接自身 : How does this work?

Question

我正在阅读 SQL Antipatterns 这本书，并试图理解其中使用自引用表构建“树”的示例，例如

                         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。在这种情况下，只有一个表，但我可以想象查询是在两个表中进行的，其中第二个是第一个的副本。不过，我还是不明白该查询是如何返回的

two levels of the tree.

结果表到底是什么？你能告诉我它是如何产生的逻辑吗？

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) 连接s comment_id.

该表通过将每一行与其子评论映射来分支到自身。右侧的结果 (c2) 将是 c1 条目的所有子项，对每个子项重复。由于我们进行的是左连接，没有子行的行将简单地在 c2 列上返回 NULL。