Предложения NOT IN и значения NULL

Эта проблема возникла, когда я получил различное количество записей для идентичных запросов, один из которых используетnot in where ограничение, а другойleft join, Стол вnot in ограничение было одно нулевое значение (неверные данные), что заставило этот запрос вернуть количество записей 0. Я вроде понимаю почему, но я мог бы использовать некоторую помощь, чтобы полностью понять концепцию.

Проще говоря, почему запрос A возвращает результат, а B нет?

A: select 'true' where 3 in (1, 2, 3, null)
B: select 'true' where 3 not in (1, 2, null)

Это было на SQL Server 2005. Я также обнаружил, что вызовset ansi_nulls off заставляет B возвращать результат.

Ответы на вопрос(11)

NOT IN возвращает 0 записей при сравнении с неизвестным значением

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

 Govind Rai13 сент. 2016 г., 19:31
Это ответ в двух словах. Я обнаружил, что это легче понять, даже без какого-либо примера.

также это может быть полезно, чтобы узнать логическое различие между объединением, существует и вhttp://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

Нуль означает и отсутствие данных, то есть это неизвестно, а не значение данных ничего. Это'Людям, имеющим опыт программирования, очень легко перепутать это, потому что в языках типов C при использовании указателей null действительно ничего.

Следовательно, в первом случае 3 действительно находится во множестве (1,2,3, null), поэтому возвращается true

Во втором, однако, вы можете уменьшить его до

Выбрать 'правда' где 3 не в (ноль)

Так что ничего не возвращается, потому что парсер ничего не знает о наборе, с которым вы его сравниваете - он 'не пустой набор, а неизвестный набор. Использование (1, 2, нуль) нене помогает, потому что (1,2) множество, очевидно, ложно, но тогда выре и 'это против неизвестного, что неизвестно.

Из ответов здесь можно сделать вывод, чтоNOT IN (subquery) Безразлично»t обрабатывает нули правильно и его следует избегать в пользуNOT EXISTS, Однако такой вывод может быть преждевременным. В следующем сценарии, приписанном Крису Дейту (Программирование и дизайн баз данных, Том 2 № 9, сентябрь 1989 г.), этоNOT IN это обрабатывает нули правильно и возвращает правильный результат, а не.NOT EXISTS

Рассмотрим таблицуsp представлять поставщиков (sno) которые, как известно, поставляют запчасти (pno) в количестве (qty). В настоящее время таблица содержит следующие значения:

      VALUES ('S1', 'P1', NULL), 
             ('S2', 'P1', 200),
             ('S3', 'P1', 1000)

Обратите внимание, что количество можно обнулять, т. Е. Иметь возможность зафиксировать тот факт, что поставщик, как известно, поставляет детали, даже если неизвестно, в каком количестве.

Задача состоит в том, чтобы найти поставщиков, которым известен номер детали поставки »P1' но не в количестве 1000.

Следующие использованияNOT IN правильно определить поставщика »S2' только:

WITH sp AS 
     ( SELECT * 
         FROM ( VALUES ( 'S1', 'P1', NULL ), 
                       ( 'S2', 'P1', 200 ),
                       ( 'S3', 'P1', 1000 ) )
              AS T ( sno, pno, qty )
     )
SELECT DISTINCT spx.sno
  FROM sp spx
 WHERE spx.pno = 'P1'
       AND 1000 NOT IN (
                        SELECT spy.qty
                          FROM sp spy
                         WHERE spy.sno = spx.sno
                               AND spy.pno = 'P1'
                       );

Однако в приведенном ниже запросе используется та же общая структура, но сNOT EXISTS но неправильно включает поставщикаS1' в результате (то есть, для которого количество равно нулю):

WITH sp AS 
     ( SELECT * 
         FROM ( VALUES ( 'S1', 'P1', NULL ), 
                       ( 'S2', 'P1', 200 ),
                       ( 'S3', 'P1', 1000 ) )
              AS T ( sno, pno, qty )
     )
SELECT DISTINCT spx.sno
  FROM sp spx
 WHERE spx.pno = 'P1'
       AND NOT EXISTS (
                       SELECT *
                         FROM sp spy
                        WHERE spy.sno = spx.sno
                              AND spy.pno = 'P1'
                              AND spy.qty = 1000
                      );

ТакNOT EXISTS это не серебряная пуля, это, возможно, появилось!

Конечно, источником проблемы является наличие нулей, поэтомуреальный» Решение состоит в том, чтобы устранить эти нули.

Это может быть достигнуто (среди других возможных конструкций) с использованием двух таблиц:

sp поставщики, как известно, поставляют запчастиspq известные поставщики поставляют запчасти в известных количествах

отмечая, что должно быть ограничение внешнего ключа, гдеspq Рекомендации .sp

Результат может быть получен с помощьюминус' реляционный оператор (будучиEXCEPT ключевое слово в стандартном SQL), например

WITH sp AS 
     ( SELECT * 
         FROM ( VALUES ( 'S1', 'P1' ), 
                       ( 'S2', 'P1' ),
                       ( 'S3', 'P1' ) )
              AS T ( sno, pno )
     ),
     spq AS 
     ( SELECT * 
         FROM ( VALUES ( 'S2', 'P1', 200 ),
                       ( 'S3', 'P1', 1000 ) )
              AS T ( sno, pno, qty )
     )
SELECT sno
  FROM spq
 WHERE pno = 'P1'
EXCEPT 
SELECT sno
  FROM spq
 WHERE pno = 'P1'
       AND qty = 1000;
 Govind Rai13 сент. 2016 г., 19:24
О, мой бог. Спасибо, что на самом деле написали это .... это сводило меня с ума ..

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

Ваш первый запрос возвращает результаты, поскольку предложение WHERE оценивается как:

    3 = 1 or 3 = 2 or 3 = 3 or 3 = null
which is:
    FALSE or FALSE or TRUE or UNKNOWN
which evaluates to 
    TRUE

Второй:

    3  1 and 3  2 and 3  null
which evaluates to:
    TRUE and TRUE and UNKNOWN
which evaluates to:
    UNKNOWN

UNKNOWN - это не то же самое, что FALSE, вы можете легко проверить это, вызвав:

select 'true' where 3  null
select 'true' where not (3  null)

Оба запроса не дадут вам результатов

Если UNKNOWN был таким же, как FALSE, то при условии, что первый запрос даст вам FALSE, второй должен будет иметь значение TRUE, поскольку оно будет таким же, как NOT (FALSE).

Это не относится к делу.

Там очень хорошостатья на эту тему на SqlServerCentral.

Поначалу проблема NULL и трехзначной логики может немного сбивать с толку, но это важно понять, чтобы писать правильные запросы в TSQL.

Еще одна статья, которую я бы порекомендовалАгрегатные функции SQL и NULL.

это для мальчика

select party_code 
from abc as a
where party_code not in (select party_code 
                         from xyz 
                         where party_code = a.party_code);

это работает независимо от настроек ANSI

 Rostand Abear24 июн. 2009 г., 08:42
выберите party_code из abc как код party_code, в котором нет party_code (выберите код party_code из xyz, где код party_code не нулевой), но удачи, если вы забыли, что поле допускает нулевые значения, что часто бывает
 Rostand Abear23 июн. 2009 г., 13:20
по первоначальному вопросу: B: выберите 'правда' где 3 не в (1, 2, ноль) способ удаления нулей должен быть сделан, например. Выбрать 'правда' где 3 отсутствует в (1, 2, isnull (null, 0)), общая логика такова: если NULL является причиной, то найдите способ удалить значения NULL на каком-то этапе запроса.

Сравнение с нулем не определено, если вы не используете IS NULL.

Таким образом, при сравнении 3 с NULL (запрос A) возвращается неопределенное значение.

То есть ВЫБРАТЬ 'правда' где 3 in (1,2, null) и SELECT 'правда' где 3 не в (1,2, ноль)

будет выдавать тот же результат, так как NOT (UNDEFINED) все еще не определен, но не TRUE

 crokusek18 июл. 2012 г., 02:52
Отличный момент. выберите 1, где ноль в (ноль) не возвращает строки (ANSI).
Решение Вопроса

Запрос A такой же как:

select 'true' where 3 = 1 or 3 = 2 or 3 = 3 or 3 = null

поскольку3 = 3 верно, вы получите результат.

Запрос B такой же, как:

select 'true' where 3  1 and 3  2 and 3  null

когдаansi_nulls включен,3 null НЕИЗВЕСТНО, поэтому предикат оценивается как НЕИЗВЕСТНО, и вы нене получить никаких строк.

когдаansi_nulls выключен,3 null Значение true, поэтому предикат оценивается как true, и вы получите строку.

 binki11 нояб. 2017 г., 22:54
@IstiaqueAhmed I ’Я уверен, что sqlcourse просто сломан:imgur.com/a/zXJle , Так что'Нет причин для моего кода работать с ним, если он не способен работать с T-SQL.
 binki10 нояб. 2017 г., 19:47
@IstiaqueAhmed Вы уверены, что sqlcourse.com ’Что-то работает для любого запроса? Я могу'не найти то, что он принимает ...
 Hau07 сент. 2018 г., 06:58
Одна потенциальная путаница здесь заключается в том, чтоcase cast(null to boolean) then 'yes' else 'no' end результаты вno, С другой стороны,case cast(null to boolean) = false then 'yes' else 'no' end также приводит кno...case cast(null to boolean) is null then 'yes' else 'no' end приведет к.yes
 Istiaque Ahmed10 нояб. 2017 г., 21:14
@binki, было бы полезно подробнее рассказать о том, что ты сказал
 binki23 авг. 2016 г., 18:30
Я думаю, это объясняет, почемуSELECT 1 WHERE NULL NOT IN (SELECT 1 WHERE 1=0); выдает строку вместо пустого набора результатов, который я ожидал.
 OzrenTkalcecKrznaric15 сент. 2016 г., 11:47
Это очень плохое поведение SQL-сервера, потому что, если он ожидает NULL-сравнения с использованием "НУЛЕВОЙ"затем он должен расширить предложение IN до того же поведения и не тупо применять неверную семантику к себе.
 Istiaque Ahmed10 нояб. 2017 г., 14:57
@binki, ваш запрос выполняется, если запустить здесьrextester.com/l/sql_server_online_compiler но не работает, если запустить здесьsqlcourse.com/cgi-bin/interpreter.cgi.
 Ian Boyd13 окт. 2010 г., 16:47
Кто-нибудь когда-либо указывал, что преобразованиеNOT IN к серии and изменяет семантическое поведениене в этом наборе к чему-то еще?
 Ryan Olson27 дек. 2010 г., 20:21
@Ian - Похоже "НЕ ВХОД ('X', 'Y') " на самом деле это псевдоним для A <> 'ИКС' И А <> 'Y' в SQL. (Я вижу, что вы обнаружили это вstackoverflow.com/questions/3924694/..., но хотел убедиться, что ваше возражение было учтено в этом вопросе.)
 Hau07 сент. 2018 г., 06:42
@ Ян Не совсем. Если вы интерпретируете значениеnull быть "какое-то неизвестное значение, тогда семантика является последовательной:3 in (1, 2, 3, unknown) это правда, потому что тызнать что 3 в группе.3 not in (1, 2, unknown) не является ни правдой, ни ложью, потому что этоможет быть в группе. "Учитывая 1, 2, и что-то мы неНе знаете фактическое значение, не 3 в этом наборе? " Я'Я не уверен.

В А 3 проверяется на равенство по отношению к каждому члену множества, уступая (ЛОЖЬ, ЛОЖЬ, ИСТИНА, НЕИЗВЕСТНО). Поскольку один из элементов имеет значение ИСТИНА, условие ИСТИНА. (Это'Также возможно, что здесь имеет место некоторое короткое замыкание, поэтому оно фактически останавливается, как только достигает первого ИСТИНА, и никогда не оценивает 3 = NULL.)

В B, я думаю, это оценивает условие как НЕ (3 в (1,2, ноль)). Тестирование 3 на равенство с заданным выходом (FALSE, FALSE, UNKNOWN), которое агрегируется в UNKNOWN. НЕ (НЕИЗВЕСТНО) приводит к НЕИЗВЕСТНО. Таким образом, в целом истинность условия неизвестна, что в конце концов рассматривается как ЛОЖЬ.

Если вы хотите отфильтровать с NOT IN для подзапроса, связанного с NULL, просто отметьте not not null

SELECT blah FROM t WHERE blah NOT IN
        (SELECT someotherBlah FROM t2 WHERE someotherBlah IS NOT NULL )
 QMaster23 февр. 2018 г., 23:00
У меня была проблема с запросом внешнего соединения, который не возвращал никаких записей в особых ситуациях, поэтому проверил это решение как для сценария с нулевыми, так и для существующих записей, и это сработало для меня. Если возникли другие проблемы, я 'упомяну здесь, спасибо большое.

Название этого вопроса на момент написания

Ограничение SQL NOT IN и значения NULL

Из текста вопроса видно, что проблема возникла в SQL DMLSELECT запрос, а не SQL DDL.CONSTRAINT

Однако, особенно учитывая формулировку заголовка, я хочу отметить, что некоторые высказанные здесь заявления могут вводить в заблуждение, такие как (перефразируя)

Когда предикат оценивается как НЕИЗВЕСТНЫЙ, вы нене получить никаких строк.

Хотя это относится к SQL DML, при рассмотрении ограничений эффект будет другим.

Рассмотрим эту очень простую таблицу с двумя ограничениями, взятыми непосредственно из предикатов в вопросе (и адресованных в превосходном ответе @Brannon):

DECLARE @T TABLE 
(
 true CHAR(4) DEFAULT 'true' NOT NULL, 
 CHECK ( 3 IN (1, 2, 3, NULL )), 
 CHECK ( 3 NOT IN (1, 2, NULL ))
);

INSERT INTO @T VALUES ('true');

SELECT COUNT(*) AS tally FROM @T;

Согласно @Brannon 'ответ, первое ограничение (используяIN) оценивается как ИСТИНА и второе ограничение (используяNOT IN) оценивает к НЕИЗВЕСТНО.тем не мениеВставка удалась! Поэтому в данном случае не совсем правильно говорить: «ты неникаких строк " потому что мы действительно вставили строку в результате.

Вышеуказанный эффект действительно правильный в отношении стандарта SQL-92. Сравните и сопоставьте следующий раздел из спецификации SQL-92

7.6, где пункт

Результатом является таблица тех строк T, для которых результат условия поиска является истинным.

4.10 Ограничения целостности

Проверочное ограничение таблицы выполняется тогда и только тогда, когда указанное условие поиска не является ложным для какой-либо строки таблицы.

Другими словами:

В SQL DML строки удаляются из результата, когдаWHERE оценивает НЕИЗВЕСТНО, потому что этоне удовлетворить условие "правда".

В SQL DDL (т.е. в ограничениях) строки не удаляются из результата, когда они оцениваются как UNKNOWN, потому что этоделает удовлетворить условие "не ложь ".

Хотя эффекты в SQL DML и SQL DDL соответственно могут показаться противоречивыми, существует практическая причина для получения НЕИЗВЕСТНЫХ результатов:Преимущество сомнения' позволяя им удовлетворять ограничению (точнее, позволяя им не отказывать в удовлетворении ограничения): без этого поведения все ограничения должны были бы явно обрабатывать пустые значения, и это было бы очень неудовлетворительно с точки зрения разработки языка (не говоря уже о том, что правильная боль для кодеров!)

постскриптум если вам сложно следовать такой логике, как "Неизвестный не может не выполнить ограничение " как я пишу, подумайте, что вы можете обойтись без всего этого, просто избегая пустых столбцов в SQL DDL и всего, что в SQL DML создает нули (например, внешние объединения)!

 onedaywhen26 сент. 2011 г., 09:18
@ Джейми Иде: На самом деле у меня есть другой ответ на эту тему: потому чтоNOT IN (subquery) включение нулей может дать неожиданные результаты, этозаманчиво избежатьIN (subquery) полностью и всегда использоватьNOT EXISTS (subquery) (как я когда-то сделал!), потому что кажется, что он всегда обрабатывает нули правильно. Однако есть случаи, когдаNOT IN (subquery) дает ожидаемый результат, тогда какNOT EXISTS (subquery) дает неожиданные результаты! Я могу найти время, чтобы написать это, если смогу найти свои заметки на эту тему (нужны заметки, потому что этоне интуитивно понятно!) Вывод тот же: избегайте нулей!
 Jamie Ide24 сент. 2011 г., 21:54
Я, честно говоря, не думал, что по этому вопросу осталось что-то сказать. Интересно.

Ваш ответ на вопрос