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

Question

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

Эта проблема возникла, когда я получил различное количество записей для идентичных запросов, один из которых not inwhere ограничение, а другой 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 возвращать результат.

323

sql sql-server t-sql null notin

Источник

user12752 24 сен '08 в 18:51

11 ответов

Решение

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

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

77

Источник

user3821 24 сен '08 в 19:01

Всякий раз, когда вы используете 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.

66

Источник

user3241 25 сен '08 в 09:54

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

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

Т.е. ВЫБРАТЬ "истина", где 3 в (1,2, ноль) и ВЫБРАТЬ "истина", где 3 не в (1,2, ноль)

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

19

Источник

user8220 24 сен '08 в 18:53

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 (1) | col = 1 | col = NULL OR col = 1 | NOT (col = NULL OR col = 1) |
|-----|----------------|---------|-----------------------|-----------------------------|
| 1   | UNKNOWN        | TRUE    | TRUE                  | FALSE                       |
| 2   | UNKNOWN        | FALSE   | UNKNOWN (2)           | UNKNOWN (3)                 |

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

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

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

11

Источник

user87015 28 ноя '19 в 11:44

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

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

10

Источник

user1745672 31 окт '14 в 22:06

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

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

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

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

Когда предикат оценивается как UNKNOWN, вы не получаете никаких строк.

Хотя это относится к 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 соответственно могут показаться противоречивыми, существует практическая причина для того, чтобы дать НЕИЗВЕСТНЫМ результатам "преимущество сомнения", позволяя им удовлетворять ограничению (более правильно, позволяя им не отказывать в удовлетворении ограничения) без этого поведения все ограничения должны были бы явно обрабатывать нули, и это было бы очень неудовлетворительно с точки зрения языкового дизайна (не говоря уже о том, что кодерам было бы больно!)

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

9

Источник

user15354 23 сен '11 в 08:39

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

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

8

Источник

user6568 24 сен '08 в 18:58

Из ответов здесь можно сделать вывод, что NOT IN (subquery) неправильно обрабатывает нули и его следует избегать в пользу 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;

7

Источник

user15354 27 сен '11 в 15:00

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

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

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

выберите "истина", где 3 не в (ноль)

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

7

Источник

user7315 24 сен '08 в 19:08

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

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

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

1

Источник

23 июн '09 в 10:02

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

0

Источник

user21404 24 сен '08 в 22:47

Другие вопросы по тегам sql sql-server t-sql null notin

user5745 24 сен '08 в 19:01 2008-09-24 19:01 · Accepted Answer · 2008-09-24 19:01

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

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

11 ответов

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

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