samedi 26 décembre 2015

A table left outer joining with itself: How does this work?

I'm reading the book SQL Antipatterns and trying to understand its example of building a "tree" with a self-referential table such as

                         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.

So the book says

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

I'm trying to understand how this works. The way I've always understood left outer joins between a table t1 and t2 is that you take all the rows from t1 and for the rows where the ON clause is not satisfied you fill in NULL for the columns from the second table. In this case there is only one table, but I can imagine that the query is taking place with two tables where the second one is a copy of the first. Still, I don't understand how that query returns

two levels of the tree.

What exactly is the resulting table and can you walk me through the logic of how it is resulted?

Aucun commentaire:

Enregistrer un commentaire