Исключение нулевого значения возвращает 0 строк в подзапросе

Это ожидаемое поведение подзапроса NOT IN. Когда подзапрос возвращает одно null значение, NOT IN не будет соответствовать ни одной строке.

Если вы не хотите выполнять только null проверку, тогда вам следует использовать NOT EXISTS:

select * 
from tbl_A A
where not exists (select distinct ID 
                  from tbl_B b
                  where a.id = b.id)

Что касается того, почему NOT IN вызывает проблемы, вот несколько сообщений, в которых это обсуждается:

НЕ В против НЕ СУЩЕСТВУЕТ против ЛЕВОГО СОЕДИНЕНИЯ / ЕСТЬ NULL

НЕ СУЩЕСТВУЕТ, а НЕ В

Что такое разница между NOT EXISTS, NOT IN и LEFT JOIN WHERE IS NULL?

Сопоставление NULL с помощью equals (=) вернет NULL или UNKNOWN в отличие от true / false с логической точки зрения. Например. см. http://msdn.microsoft.com/en-us/library/aa196339(v=sql.80).aspx для обсуждения.

Если вы хотите включить поиск значений NULL в таблице A, где нет NULL в таблице B (если B является «родительским», а A является «дочерним элементом» в желаемом отношении «внешнего ключа»), вам потребуется второй заявление, что-то вроде следующего. Также я бы рекомендовал указать поле идентификатора префиксом или псевдонимом таблицы, поскольку имена полей в обеих таблицах совпадают. Наконец, я бы не рекомендовал использовать в качестве ключа значения NULL. Но в любом случае:

select * from tbl_A as A where (A.ID not in ( select distinct B.ID from tbl_B as B ))
  or (A.ID is NULL and not exists(select * from tbl_B as B where B.ID is null))

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

Теперь, конечно, все это при условии, что у вас есть ANSI_NULLS ON (что по умолчанию). Если вы отключите это, тогда NULLS внезапно станут сопоставимыми, и это даст вам результаты, и, возможно, результаты, которые вы ожидаете.

Если идентификаторы никогда не бывают отрицательными, вы можете подумать о следующем:

select *
from tbl_A
where coalesce(ID, -1) not in ( select distinct coalesce(ID, -1) from tbl_B )

(Или, если id - строка, используйте что-нибудь line coalesce(id, '<null>')).

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

Вероятно, у вас отключены значения ANSI NULL. Это сравнивает нулевые значения, поэтому null = null вернет истину.

Приставьте к первому запросу префикс

SET ANSI_NULLS ON
GO