Понимание этого примера SQL LEFT JOIN (с IS NULL)

Question

В базе данных есть таблица с именем category:

CREATE TABLE category(
        category_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        parent INT DEFAULT NULL
);

Он был создан для создания дерева моделей смежных списков. Вот что сейчас находится внутри таблицы:

В этом примере (вы можете найти ссылку на источник внизу), следующий SQL-код был использован для получения элементов "leaf" в таблице, где элементы "leaf" - это строки в таблице, которые не имели других строка использует их "category_id" в столбце "parent". Следующий код был запущен:

SELECT t1.name FROM
category AS t1 LEFT JOIN category as t2
ON t1.category_id = t2.parent
WHERE t2.category_id IS NULL;

Результат предыдущего кода SQL дает такой результат:

Например, в таблице нет строки, которая имеет значение 3 внутри родительского столбца, поэтому TUBE (с category_id == 3) является "листовым" элементом.

ВОПРОС: Почему этот SQL-код логически дал такой результат? Я счастлив, что это происходит, потому что это то, что мне было нужно, но я не могу обернуть голову вокруг причин этого.

---

Источник примера: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Answer 1

Всякий раз, когда вы видите этот шаблон:

SELECT    ...
FROM      TableA
LEFT JOIN TableB ON TableA.column = TableB.column
WHERE     TableB.id is null

Подумайте: "найдите в таблице A строки, которых нет в TableB`.

Ваш запрос означает "найти категории, которые не имеют детей". Это легче понять, если удалить WHERE и добавьте еще несколько столбцов к запросу:

SELECT      t1.category_id,
            t1.name,
            t2.name  AS ChildName
FROM        category AS t1
LEFT JOIN   category as t2 ON t1.category_id = t2.parent

Вот что происходит в вашем запросе:

1. Начните с category таблица, псевдоним это как t1 а также t2, Я буду ссылаться на псевдонимы отныне.
2. Для каждого ряда в t1найти все записи в t2 это указывает на то, что t1 грести как родитель
3. Если t1 В ряду нет детей, t2.category_id будет нулевым
4. Мы хотим фильтровать только для t1 строки, которые не имеют детей

Answer 2

Я думаю, что было бы легче увидеть, что происходит, если вы переименуете псевдоним таблицы:

    SELECT tparent.name 
      FROM category AS tparent 
      LEFT JOIN category as tchild
        ON tparent.category_id = tchild.parent
     WHERE tchild.category_id IS NULL;

Теперь может быть проще увидеть, что вы запрашиваете список всех имен родительских категорий, у которых нет дочерних элементов [tchild.category_id IS NULL]