NULL и NOT IN()

Перевод NULLs vs. NOT IN() с select * from depesz;

Как-то раз мой друг, как ему показалось, нашел ошибку в PG.

Очень простой запрос

select * from table where id not in (select field from other_table)

не возвращал ни одной строки, несмотря на то, что точно существовали такие id, которых не было в other_table.field. Как это?

Для начала смоделируем ситуацию:

CREATE TABLE objects (id INT4 PRIMARY KEY);
CREATE TABLE secondary (object_id INT4);
INSERT INTO objects (id) VALUES (1), (2), (3);
INSERT INTO secondary (object_id) VALUES (NULL), (2), (4);

Данные:

# select * from objects ;
 id
----
  1
  2
  3
(3 rows)

# select * from secondary ;
 object_id
-----------
    [null]
         2
         4
(3 rows)

Ок. Первым делом проверим:

# SELECT * FROM objects WHERE id IN (SELECT object_id FROM secondary);
 id
----
  2
(1 row)

Таким образом, опираясь на то, что у нас есть 3-и id объектов (1, 2, 3) и IN работает только для 2-ки, NOT IN должен вернуть 1 и 3. Правильно? Не правильно:

# SELECT * FROM objects WHERE id NOT IN (SELECT object_id FROM secondary);
 id
----
(0 rows)

Чё за... Почему?

Проверим результат id NOT IN для каждого id:

# SELECT id, id NOT IN (SELECT object_id FROM secondary) FROM objects;
 id | ?column?
----+----------
  1 | [null]
  2 | f
  3 | [null]
(3 rows)

Хм... но почему?

Разберём случай с id = 2.

Сравнение с 2 во второй таблице - id (2) равен 2, т.ч. можем утверждать, что NOT IN вернёт false. Дальнейшая проверка не требуется.
Сравнение с 4 во второй таблице - id (2) не равен 4, т.ч. пока NOT IN выполняется, но надо проверять далше.
Сравнение с NULL во второй таблице - сравнение id и NULL даст NULL, т.к. NULL рассматривается как "неизвестно". Может равно, а может не равно. И всё ещё требуется дальнейшая проверка.

Получается, что для id = 2 у нас будет false, потому что во второй таблице есть строка с object_id = 2.

Но что будет при сравнении с id = 3?

Сравнение с 2 во второй таблице - id (3) не равен 2, т.ч. пока NOT IN выполняется, но надо проверять далше.
Сравнение с 4 во второй таблице - id (3) не равен 4, т.ч. пока NOT IN выполняется, но надо проверять далше.
Сравнение с NULL во второй таблице - сравнение id и NULL даст NULL, т.к. NULL рассматривается как "неизвестно". Может равно, а может не равно. И всё ещё требуется дальнейшая проверка.

Для двух строк NOT IN сработало, но для одной - мы не уверены. NULL - "неизвестно", т.ч. оно может оказаться и тройкой (3). Мы не знаем. И по этому NOT IN должен вернуть NULL.

Если посмотреть ближе, то можно увидеть, что технически не возможно получить true в данной ситуации, пока во второй таблице есть значения NULL!

Что делать?

Есть очень простое решение:

# SELECT id, id NOT IN (SELECT object_id FROM secondary WHERE object_id IS NOT NULL) FROM objects;
 id | ?column?
----+----------
  1 | t
  2 | f
  3 | t
(3 rows)

Которое возвращает строки с id = 1 и id = 3.