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