SQL Server: почему сравнение null = value возвращает true для NOT IN?

Почему сравнение значения с null возвращает false, за исключением случая использования NOT IN , где оно возвращает true?

] Дан запрос на поиск всех пользователей stackoverflow, которые имеют сообщение:

SELECT * FROM Users
WHERE UserID IN (SELECT UserID FROM Posts)

Это работает, как ожидалось; я получаю список всех пользователей, у которых есть сообщения.

Теперь запросите обратное; найти всех пользователей stackoverflow, которые не имеют сообщения:

SELECT * FROM Users
WHERE UserID NOT IN (SELECT UserID FROM Posts)

Это не возвращает записей, что неверно.

Учитывая гипотетические данные ¹

Users              Posts
================   ===============================
UserID  Username   PostID   UserID  Subject
------  --------   -------  ------  ----------------
1       atkins     1        1       Welcome to stack ov...
2       joels      2        2       Welcome all!
...     ...        ...      ...
399573  gt6989b    ...      ...
...     ...        ...      ...
                   10592    null    (deleted by nsl&fbi...
                   ...      ... 

И предположите правила NULL:

• NULL = NULL оценивается как неизвестное
• NULL <> NULL оценивается как неизвестное
• значение = NULL оценивается как неизвестное

Если мы посмотрим на второй запрос, мы заинтересованы в поиске всех строк, где находится Users. Идентификатор пользователя не найден в столбце Posts.UserID. логически я поступил бы следующим образом:

Проверить идентификатор пользователя 1

• 1 = 1 возвращает истину. Таким образом, мы заключаем, что у этого пользователя есть некоторые сообщения, и не включаем их в список вывода

Теперь проверьте UserID 2:

• 2 = 1 возвращает false, поэтому мы продолжаем искать
• 2 = 2 возвращает true, поэтому мы заключаем, что у этого пользователя есть некоторые сообщения, и не включаем их в список вывода

Теперь проверьте UserID 399573

• 399573 = 1 возвращает false, поэтому мы продолжаем поиск
• 399573 = 2 возвращает false, поэтому мы продолжаем поиск
• ...
• 399573 = null возвращает unknown, поэтому мы продолжаем искать
• ...

Мы не нашли сообщений по UserID 399573, поэтому мы должны включить его в список вывода.

За исключением того, что SQL Server этого не делает. Если у вас есть NULL в вашем списке в , то он внезапно находит совпадение. Он внезапно находит совпадение. Внезапно 399573 = null оценивается как истина.

Почему сравнение значения с null возвращает значение «неизвестно», кроме случаев, когда оно возвращает истину?

Изменить : я знаю, что могу обойти это бессмысленное поведение, специально исключив нули:

SELECT * FROM Users
WHERE UserID NOT IN (
   SELECT UserID FROM Posts
   WHERE UserID IS NOT NULL)

Но я не должен этого делать, насколько я могу судить, с булевой логикой все должно быть хорошо и без этого - отсюда и мой вопрос.

Footnotes

• ¹ гипотетические данные; если вам это не нравится: восполните свой запас.
• celko теперь имеет свой собственный тег