Каковы различия в этих примерах таблицы замыкания SQL?

Question

У меня возникли некоторые трудности, связанные с обработкой таблиц закрытия SQL, и я хотел бы получить некоторую помощь в понимании некоторых примеров, которые я нашел.

Допустим, у меня есть стол под названием sample_items со следующими иерархическими данными:

id   name                   parent_id
1    'Root level item #1'   0
2    'Child of ID 1'        1
3    'Child of ID 2'        2
4    'Root level item #2'   0

Древовидная структура должна быть такой:

id
| - 1 
|   | - 2
|       | - 3
| - 4

Для простоты запросов к деревьям (например, для нахождения всех потомков определенного идентификатора) у меня есть таблица с именем sample_items_closure используя метод, описанный Биллом Карвином в этом превосходном посте SO. Я также использую дополнительный path_length столбец для запроса ближайшего ребенка или родителя, когда это необходимо. Если я правильно понимаю этот метод, мои данные таблицы закрытия будут выглядеть так:

ancestor_id   descendant_id   path_length
1             1               0
2             2               0
1             2               1
3             3               0
2             3               1
1             3               2
4             4               0

Каждый ряд в sample_items теперь есть запись в sample_items_closure стол как для себя, так и для всех его предков. Пока все имеет смысл.

Однако, изучая другие примеры таблиц замыканий, я натолкнулся на один, который добавляет дополнительного предка для каждой строки, которая связана с корневым уровнем (ancestor_id 0) и имеет длину пути 0. Используя те же данные, которые были у меня выше, это то, что таблица закрытия будет выглядеть так:

ancestor_id   descendant_id   path_length
1             1               0
0             1               0
2             2               0
1             2               1
0             2               0
3             3               0
2             3               1
1             3               2
0             3               0
4             4               0
0             4               0

Чтобы лучше понять контекст, вот запрос на выборку, используемый на этом сайте, измененный в соответствии с моими примерами:

SELECT `id`,`parent_id` FROM `sample_items` `items`
  JOIN `sample_items_closure` `closure`
  ON `items`.`id` = `closure`.`descendant_id`
  WHERE `closure`.`ancestor_id` = 2

У меня есть два вопроса, связанных с этим методом:

Вопрос 1:

Зачем нужна дополнительная строка, связывающая каждого потомка с корневым уровнем (id 0)?

Вопрос 2:

Почему path_length для этих записей будет 0, а не path_length предыдущего предка +1? Например:

ancestor_id   descendant_id   path_length
1             1               0
0             1               1
2             2               0
1             2               1
0             2               2
3             3               0
2             3               1
1             3               2
0             3               3
4             4               0
0             4               1

Бонусный вопрос: почему некоторые примеры все еще включают в себя список смежности (parent_id колонка для sample_items в моем примере) когда полная структура дерева уже выражена в таблице замыканий?

Answer 1

Вы могли бы использовать CTE. Они созданы именно для этих вариантов использования и имеют много замечательных примеров, которые близки к вашему случаю.