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.