Использование IS NULL или NOT NULL в условиях соединения - Теория вопроса

Теория вопроса здесь:

Почему указание table.field IS NULL или table.field IS NOT NULL не работает на условие соединения (например, влево или вправо), но только в условии where?

Не работает Пример:

-это должно возвращать все отправления с любыми возвращаемыми (не нулевыми значениями) фильтрами. Однако это возвращает все отправления независимо от того, что-либо встречает оператор [r.id is null].

SELECT
  *
FROM 
  shipments s
LEFT OUTER JOIN returns r  
  ON s.id = r.id
  AND r.id is null
WHERE
  s.day >= CURDATE() - INTERVAL 10 DAY

Рабочий пример:

-Это возвращает правильное количество строк, которое является полным количеством отправлений, меньше любых связанных с возвратом (не нулевые значения).

SELECT
  *
FROM 
  shipments s
LEFT OUTER JOIN returns r  
  ON s.id = r.id
WHERE
  s.day >= CURDATE() - INTERVAL 10 DAY
  AND r.id is null

Почему это так? Все остальные условия фильтрации между двумя соединяемыми таблицами работают очень хорошо, но по какой-то причине фильтры IS NULL и NOT NULL не работают, если только в инструкции where.

В чем причина этого?

Ответ 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   |
+------+--------+------+-------+

Ответ 2

Часть NULL вычисляется ПОСЛЕ фактического соединения, поэтому он должен быть в предложении where.

Ответ 3

На самом деле фильтр NULL не игнорируется. Дело в том, как работает соединение двух таблиц.

Я попытаюсь спуститься с шагами, выполняемыми сервером базы данных, чтобы понять. Например, когда вы выполняете запрос, который, как вы сказали, игнорирует условие NULL. ВЫБРАТЬ * ИЗ отгрузки LEFT OUTER JOIN возвращает r
ON s.id = r.id AND r.id - null ГДЕ s.day >= CURDATE() - INTERVAL 10 DAY

Первое, что произошло, это все строки из таблицы SHIPMENTS, которые выбраны

на сервере базы данных следующего шага начнется выбор по одной записи из таблицы 2 (RETURNS).

на третьем шаге запись из таблицы RETURNS будет квалифицирована против условий соединения, которые вы предоставили в запросе, который в этом случае (s.id = r.id и r.id равно NULL)

обратите внимание, что эта квалификация, применяемая на третьем шаге, решает, должен ли сервер принимать или отклонять текущую запись таблицы RETURNS для добавления с выбранной строкой таблицы SHIPMENT. Он никоим образом не может повлиять на выбор записи из таблицы SHIPMENT.

И как только сервер будет выполнен с объединением двух таблиц, содержащих все строки таблицы SHIPMENT и выбранных строк таблицы RETURNS, он применяет предложение where для промежуточного результата. поэтому, когда вы ставите (r.id is NULL) условие в том, где предложение, чем все записи из промежуточного результата с r.id = null, отфильтровывается.

Ответ 4

Предложение WHERE оценивается после обработки условий JOIN.

Ответ 5

Вы делаете LEFT OUTTER JOIN, который указывает, что вы хотите, чтобы каждый кортеж из таблицы на LEFT оператора независимо от того, что он имеет соответствующую запись в таблице RIGHT. В этом случае ваши результаты обрезаются из таблицы RIGHT, но вы получаете те же результаты, что и вы не включили AND вообще в предложение ON.

Выполнение условия AND в предложении WHERE приводит к тому, что чернослив происходит после завершения LEFT JOIN.

Ответ 6

Ваш план выполнения должен сделать это ясным; JOIN имеет приоритет, после чего результаты фильтруются.