NULL значения в NOT IN

NOT IN - опасная штука. Нужно гарантировать, что в результате подзапроса не встретится значение NULL.

Запрос

SELECT *
  FROM store
 WHERE store_id NOT IN (100, 300, 500)
 ORDER BY store_id

вернет все магазины, кроме магазинов с идентификаторами 100, 300 и 500.

# store_id name site_url
1 200 Марс mars.ru
2 201 Сатурн saturn.ru
3 301 Адалин-family adalin-ultra.ru
4 302 Адалин-ultra
5 400 Европа evropa.ru
6 600 Umi
7 800 Универсам
8 900 Big big.ru

Запрос

SELECT *
  FROM store
 WHERE store_id NOT IN (100, 300, 500, NULL)
 ORDER BY store_id

не вернет ничего.

Почему так? store_id NOT IN (100, 300, 500, NULL) принимает значение TRUE, когда store_id не равно ни одному из значений в скобках. Это условие эквивалентно

 WHERE store_id != 100
   AND store_id != 300
   AND store_id != 500
   AND store_id != NULL

Выражение store_id != NULL принимает значение NULL, что означает - неизвестно. Так как неизвестно равно ли store_id одному из значений, то строка отбрасывается.

В список NOT IN значение NULL обычно не пишут. На этот подводный камень часто натыкаются при использовании подзапросов.

Получим список сотрудников, которые не сделали ни одной продажи:

SELECT e.employee_id,
       e.last_name,
       e.first_name
  FROM employee e
 WHERE e.employee_id NOT IN (SELECT p.employee_id
                               FROM purchase p)
0 строк

Исключим из подзапроса NULL значения:

SELECT e.employee_id,
       e.last_name,
       e.first_name
  FROM employee e
 WHERE e.employee_id NOT IN (SELECT p.employee_id
                               FROM purchase p
                              WHERE p.employee_id IS NOT NULL)
# employee_id last_name first_name
1 1 Иванов Алексей
2 2 Маккормик Кенни
3 3 Матвеева Анна
4 4 Шмидт Роман
... ... ... ...

P.S. В SQL значение NULL может означать, что значение либо неизвестно, либо значения не существует.

Если NULL используется для роста человека, это значит, что мы пока не знаем, какой у этого человека рост.

Если NULL используется для обозначения объема бензобака автомобиля, то для электрокаров это означает, что значения не существует.

В некоторых языках программирования помимо NULL есть значение UNDEFINED.

6.6 Отсутствие в списке значений

6.8 Проверка существования строки