Ответ 1
Пример с таблицами A и B:
A (parent) B (child)
============ =============
id | name pid | name
------------ -------------
1 | Alex 1 | Kate
2 | Bill 1 | Lia
3 | Cath 3 | Mary
4 | Dale NULL | Pan
5 | Evan
Если вы хотите найти родителей и их детей, вы выполните INNER JOIN:
SELECT id, parent.name AS parent
, pid, child.name AS child
FROM
parent INNER JOIN child
ON parent.id = child.pid
Результат состоит в том, что каждое соответствие из parent id из левой таблицы и child pid из второй таблицы будет отображаться в виде строки в результате:
+----+--------+------+-------+
| id | parent | pid | child |
+----+--------+------+-------+
| 1 | Alex | 1 | Kate |
| 1 | Alex | 1 | Lia |
| 3 | Cath | 3 | Mary |
+----+--------+------+-------+
Теперь вышесказанное не показывает родителей без детей (потому что их идентификаторы не имеют соответствия в дочерних идентификаторах, так что вы делаете? Вместо этого вы выполняете внешнее соединение. Существуют три типа внешних соединений: слева, правое и полное внешнее объединение. Нам нужен левый, поскольку мы хотим, чтобы "лишние" строки из левой таблицы (родительские):
SELECT id, parent.name AS parent
, pid, child.name AS child
FROM
parent LEFT JOIN child
ON parent.id = child.pid
Результат заключается в том, что помимо предыдущих совпадений также показаны все родители, у которых нет соответствия (read: not have a kid):
+----+--------+------+-------+
| id | parent | pid | child |
+----+--------+------+-------+
| 1 | Alex | 1 | Kate |
| 1 | Alex | 1 | Lia |
| 3 | Cath | 3 | Mary |
| 2 | Bill | NULL | NULL |
| 4 | Dale | NULL | NULL |
| 5 | Evan | NULL | NULL |
+----+--------+------+-------+
Откуда взялись все эти NULL? Ну, MySQL (или любая другая СУБД, которую вы можете использовать) не будет знать, что туда помещать, поскольку у этих родителей нет соответствия (ребенок), поэтому нет pid и child.name, чтобы соответствовать этим родителям. Таким образом, он помещает эту специальную нецензуруемую форму под названием NULL.
Моя точка зрения заключается в том, что эти NULLs создаются (в наборе результатов) во время LEFT OUTER JOIN.
Итак, если мы хотим показать только родителей, у которых нет ребенка, мы можем добавить WHERE child.pid IS NULL в LEFT JOIN выше. Предложение WHERE оценивается (проверяется) после выполнения JOIN. Итак, из приведенного выше результата видно, что будут показаны только последние три строки, где pid равно NULL:
SELECT id, parent.name AS parent
, pid, child.name AS child
FROM
parent LEFT JOIN child
ON parent.id = child.pid
WHERE child.pid IS NULL
Результат:
+----+--------+------+-------+
| id | parent | pid | child |
+----+--------+------+-------+
| 2 | Bill | NULL | NULL |
| 4 | Dale | NULL | NULL |
| 5 | Evan | NULL | NULL |
+----+--------+------+-------+
Теперь, что произойдет, если мы переместим эту IS NULL проверку с WHERE на предложение соединения ON?
SELECT id, parent.name AS parent
, pid, child.name AS child
FROM
parent LEFT JOIN child
ON parent.id = child.pid
AND child.pid IS NULL
В этом случае база данных пытается найти строки из двух таблиц, соответствующих этим условиям. То есть строки, где parent.id = child.pid И child.pid IN NULL. Но он может найти нет такого соответствия, потому что no child.pid может быть чем-то равным (1, 2, 3, 4 или 5) и одновременно быть NULL!
Итак, условие:
ON parent.id = child.pid
AND child.pid IS NULL
эквивалентно:
ON 1 = 0
который всегда False.
Итак, почему он возвращает ВСЕ строки из левой таблицы? Потому что это ЛЕВЫЙ ПРИСОЕДИНЯЙТЕСЬ!. Оставленные объединения возвращают строки, которые соответствуют (в этом случае не равны), а также строки из левой таблицы, которые не соответствуют > проверка (все в этом случае):
+----+--------+------+-------+
| id | parent | pid | child |
+----+--------+------+-------+
| 1 | Alex | NULL | NULL |
| 2 | Bill | NULL | NULL |
| 3 | Cath | NULL | NULL |
| 4 | Dale | NULL | NULL |
| 5 | Evan | NULL | NULL |
+----+--------+------+-------+
Я надеюсь, что приведенное выше объяснение ясно.
Sidenote (не связанный напрямую с вашим вопросом): Почему на самом деле Pan не отображается ни в одном из наших JOIN? Поскольку его pid - NULL, а NULL в (не общей) логике SQL не равно никому, поэтому он не может соответствовать ни одному из родительских идентификаторов (которые составляют 1,2,3,4 и 5), Даже если там был NULL, он все равно не будет соответствовать, потому что NULL не имеет ничего, даже самого NULL (это очень странная логика!). Поэтому мы используем специальную проверку IS NULL, а не проверку = NULL.
Итак, появится Pan, если мы сделаем RIGHT JOIN? Да, это будет! Поскольку RIGHT JOIN покажет все результаты, которые соответствуют (первый INNER JOIN, который мы сделали), плюс все строки из таблицы RIGHT, которые не совпадают (что в нашем случае равно единице, строка (NULL, 'Pan').
SELECT id, parent.name AS parent
, pid, child.name AS child
FROM
parent RIGHT JOIN child
ON parent.id = child.pid
Результат:
+------+--------+------+-------+
| id | parent | pid | child |
+---------------+------+-------+
| 1 | Alex | 1 | Kate |
| 1 | Alex | 1 | Lia |
| 3 | Cath | 3 | Mary |
| NULL | NULL | NULL | Pan |
+------+--------+------+-------+
К сожалению, у MySQL нет FULL JOIN. Вы можете попробовать его в других СУБД, и он покажет:
+------+--------+------+-------+
| id | parent | pid | child |
+------+--------+------+-------+
| 1 | Alex | 1 | Kate |
| 1 | Alex | 1 | Lia |
| 3 | Cath | 3 | Mary |
| 2 | Bill | NULL | NULL |
| 4 | Dale | NULL | NULL |
| 5 | Evan | NULL | NULL |
| NULL | NULL | NULL | Pan |
+------+--------+------+-------+