Значения NULL внутри предложения NOT IN

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

Итак, при сравнении 3 с NULL (запрос A) он возвращает undefined.

Т.е. ВЫБЕРИТЕ 'true', где 3 в (1,2, null) и ВЫБЕРИТЕ 'true', где 3 не в (1,2, null)

даст тот же результат, поскольку НЕ (НЕОПРЕДЕЛЕННО) все еще не определено, но не ИСТИНА

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

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

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

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

Запрос 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 имеет значение UNKNOWN, поэтому предикат оценивается как UNKNOWN, и вы не получаете никаких строк.

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

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

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

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

выберите 'true', где 3 не в (ноль)

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

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

Когда вы используете 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

НЕИЗВЕСТНО - это не то же самое, что ЛОЖЬ. вы можете легко проверить это, позвонив:

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

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

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

Есть очень хороший статья на эту тему на 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

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

SQL NOT IN constraint and NULL values

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

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

When the predicate evaluates to UNKNOWN you don't get any rows.

Хотя это так и для 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) оценивается как TRUE, а второе ограничение (с использованием NOT IN) оценивается как UNKNOWN. тем не мение, вставка выполнена успешно! Следовательно, в этом случае не совсем правильно говорить «у вас нет строк», потому что в результате мы действительно вставили строку.

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

7.6 where clause

The result of the is a table of those rows of T for which the result of the search condition is true.

4.10 Integrity constraints

A table check constraint is satisfied if and only if the specified search condition is not false for any row of a table.

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

В SQL DML строки удаляются из результата, когда WHERE оценивается как UNKNOWN, потому что не удовлетворяет условию «истинно».

В SQL DDL (т.е. ограничениях) строки не удаляются из результата, когда они оцениваются как UNKNOWN, потому что делает удовлетворяет условию «is not false».

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

p.s. если вам так же сложно следовать такой логике, как «неизвестное не может удовлетворить ограничение», как я пишу это, то подумайте, что вы можете обойтись без всего этого, просто избегая столбцов, допускающих значение NULL, в SQL DDL и всего в SQL DML. который производит нули (например, внешние соединения)!

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

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

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

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

Задача состоит в том, чтобы найти поставщиков, которым известен номер детали поставки «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;

ЕСЛИ вы хотите фильтровать с помощью NOT IN для подзапроса, содержащего NULL, просто проверьте, не является ли значение null

SELECT blah FROM t WHERE blah NOT IN
        (SELECT someotherBlah FROM t2 WHERE someotherBlah IS NOT NULL )

SQL использует трехзначную логику для значений истинности. Запрос IN дает ожидаемый результат:

SELECT * FROM (VALUES (1), (2)) AS tbl(col) WHERE col IN (NULL, 1)
-- returns first row

Но добавление NOT не меняет результатов:

SELECT * FROM (VALUES (1), (2)) AS tbl(col) WHERE NOT col IN (NULL, 1)
-- returns zero rows

Это потому, что приведенный выше запрос эквивалентен следующему:

SELECT * FROM (VALUES (1), (2)) AS tbl(col) WHERE NOT (col = NULL OR col = 1)

Вот как оценивается предложение where:

| col | col = NULL⁽¹⁾  | col = 1 | col = NULL OR col = 1 | NOT (col = NULL OR col = 1) |
|-----|----------------|---------|-----------------------|-----------------------------|
| 1   | UNKNOWN        | TRUE    | TRUE                  | FALSE                       |
| 2   | UNKNOWN        | FALSE   | UNKNOWN⁽²⁾            | UNKNOWN⁽³⁾                  |

Заметить, что:

1. Сравнение с NULL дает UNKNOWN.
2. Выражение OR, где ни один из операндов не является TRUE и хотя бы один операнд является UNKNOWN, дает UNKNOWN (ссылка)
3. NOT из UNKNOWN дает UNKNOWN (ссылка)

Вы можете расширить приведенный выше пример до более чем двух значений (например, NULL, 1 и 2), но результат будет таким же: если одно из значений - NULL, то ни одна строка не будет соответствовать.