Wartości NULL wewnątrz klauzuli NOT IN

Ilekroć używasz NULL, masz do czynienia z logiką o trzech wartościach.

Twoje pierwsze zapytanie zwraca wyniki, ponieważ klauzula WHERE ocenia się na:

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

Drugi:

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

The UNKNOWN is not the same as FALSE możesz go łatwo przetestować, dzwoniąc:

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

Oba zapytania nie dadzą ci żadnych wyników

Jeśli UNKNOWN był taki sam jak FALSE, to zakładając, że pierwsze zapytanie da FALSE, drugie musiałoby ocenić na TRUE, tak jak były takie same jak NOT (FALSE).
Tak nie jest.

Jest bardzo dobry artykuł na ten temat na SqlServerCentral.

Cała kwestia null i logiki trójwartościowej może być na początku nieco myląca, ale ważne jest, aby zrozumieć, aby pisać poprawne zapytania w TSQL

Innym artykułem, który polecam jest SQL Agregate Functions I NULL.

NOT IN zwraca 0 rekordów w porównaniu z nieznaną wartością

Ponieważ NULL jest nieznaną, zapytanie NOT IN zawierające NULL lub NULLs na liście możliwych wartości zawsze zwróci rekordy 0, ponieważ nie ma możliwości upewnienia się, że wartość NULL nie jest testowaną wartością.

Compare to null jest niezdefiniowane, chyba że używasz IS NULL.

Tak więc, porównując 3 do NULL (zapytanie A), zwraca undefined.

Tzn. wybierz 'true' gdzie 3 in (1,2, null) oraz SELECT 'true' where 3 not in (1,2, null)

Da ten sam wynik, ponieważ NOT (UNDEFINED) jest nadal niezdefiniowane, ale nie jest prawdziwe

Tytuł tego pytania w momencie pisania to

SQL Nie w wartościach constraint i NULL

Z tekstu pytania wynika, że problem występował w zapytaniu SQL DML SELECT, a nie w zapytaniu SQL DDL CONSTRAINT.

Jednak, szczególnie biorąc pod uwagę brzmienie tytułu, chcę zwrócić uwagę, że niektóre wypowiedzi tu zawarte są potencjalnie mylącymi stwierdzeniami, takimi jak (parafrazowanie)

Gdy orzeczenie ewaluuje do UNKNOWN nie dostajesz żadnych wierszy.

Chociaż tak jest w przypadku SQL DML, biorąc pod uwagę ograniczenia, efekt jest inny.

Rozważmy tę bardzo prostą tabelę z dwoma ograniczeniami zaczerpniętymi bezpośrednio z predykatów w pytaniu (i poruszonymi w doskonałej odpowiedzi przez @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;

Zgodnie z odpowiedzią @Brannon, pierwsze ograniczenie (używając IN) ocenia na TRUE, a drugie ograniczenie (używając NOT IN) ocenia na UNKNOWN. jednak wkładka się powiodła! Dlatego w tym przypadku nie jest ściśle poprawne powiedzenie "nie masz żadnych wierszy", ponieważ rzeczywiście mamy wiersz wstawiony w wyniku.

Powyższy efekt jest rzeczywiście poprawny w odniesieniu do standardu SQL-92. Porównanie i kontrast poniższej sekcji ze specyfikacji SQL-92

7.6 gdzie klauzula

Wynikiem jest tabela tych wierszy T dla której wynikiem warunku wyszukiwania jest prawda.

4.10 ograniczenia integralności

Ograniczenie sprawdzania tabeli jest spełnione wtedy i tylko wtedy, gdy określone warunek wyszukiwania nie jest false dla żadnego wiersza tabeli.

Innymi słowy:

W SQL DML wiersze są usuwane z wyniku, gdy WHERE ewaluuje do UNKNOWN, ponieważ nie spełnia warunku "is true".

W SQL DDL (tj. ograniczeniach) wiersze nie są usuwane z wyniku, gdy oceniają NA nieznany ponieważ spełnia warunek "nie jest fałszywe".

Chociaż efekty odpowiednio w SQL DML i SQL DDL mogą wydawać się sprzeczne, istnieje praktyczny powód dla nadania nieznanym rezultatom "korzyści wątpliwości" poprzez umożliwienie im spełnienia ograniczenia( bardziej poprawnie, pozwalając im nie zawieść spełnienia ograniczenia): bez tego zachowania wszystkie ograniczenia musiałyby wyraźnie obsługiwać null i byłoby to bardzo niezadowalające z konstrukcji języka perspective (nie wspominając, właściwy ból dla programistów!)

P. s. Jeśli uważasz, że jest to trudne do naśladowania logiki jak "unknown does not fail to satisfied a constraint", jak mam to napisać, następnie rozważyć można zrezygnować z tego wszystkiego po prostu unikając nullable kolumny w SQL DDL i wszystko w SQL DML, które produkuje null (np. zewnętrzne połączenia)!

W A, 3 jest testowane pod kątem równości wobec każdego członka zbioru, dając (FALSE, FALSE, TRUE, UNKNOWN). Ponieważ jeden z elementów jest prawdziwy, warunek jest prawdziwy. (Jest również możliwe, że jakieś zwarcie ma miejsce tutaj, więc faktycznie zatrzymuje się tak szybko, jak tylko trafi pierwszy TRUE i nigdy nie ocenia 3=NULL.)

W B, myślę, że ocenia warunek jako NOT (3 in (1,2, null)). Test 3 na równość względem zbiorów (FALSE, FALSE, UNKNOWN), które są sumowane do Nieznane. Nie (nieznany) daje nieznany. Tak więc ogólnie prawda o stanie jest nieznana, która na końcu jest zasadniczo traktowana jako fałszywa.

Można wywnioskować z odpowiedzi tutaj, że NOT IN (subquery) nie obsługuje poprawnie null i należy go unikać na rzecz NOT EXISTS. Jednak taki wniosek może być przedwczesny. W poniższym scenariuszu, przypisanym Chrisowi Date (Database Programming and Design, Vol 2 No 9, September 1989), to NOT IN poprawnie obsługuje null i zwraca poprawny wynik, a nie NOT EXISTS.

Rozważmy tabelę sp do reprezentowania dostawców (sno), którzy są znani z dostarczania części (pno) w ilości (qty). Tabela zawiera obecnie następujące wartości:

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

Należy pamiętać, że ilość jest zerowa, tzn. aby móc zarejestrować fakt, że dostawca jest znany z dostarczania części, nawet jeśli nie jest znany w jakiej ilości.

Zadaniem jest znalezienie dostawców, którzy mają znany numer części dostaw "P1", ale nie w ilościach 1000.

Następujące zastosowania NOT IN do prawidłowej identyfikacji dostawcy tylko "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'
                       );

Jednak poniższe zapytanie wykorzystuje tę samą ogólną strukturę ale z NOT EXISTS ale niepoprawnie zawiera dostawcę "S1" w wyniku (tzn. dla którego ilość jest zerowa):

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
                      );

Więc NOT EXISTS to nie jest srebrna kula, która mogła się pojawić!

Oczywiście źródłem problemu jest obecność null, dlatego "prawdziwym" rozwiązaniem jest wyeliminowanie tych null.

Można to osiągnąć (między innymi) za pomocą dwóch tabel:

• sp dostawcy znani z dostaw części
• spq dostawcy znani z części dostaw w znanych ilościach

Zauważając, że prawdopodobnie powinno istnieć ograniczenie klucza obcego, gdzie spq odniesienia sp.

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;

Null oznacza i brak danych, czyli jest nieznany, a nie wartość danych niczego. Jest to bardzo łatwe dla osób z zaplecza programistycznego, aby pomylić to, ponieważ w językach Typu C, gdy używasz wskaźników null jest naprawdę niczym.

Stąd w pierwszym przypadku 3 jest rzeczywiście w zbiorze (1,2,3, null), więc zwracana jest true

W drugim jednak można go zredukować do

select 'true' where 3 not in (null)

Więc nic nie jest zwracane, ponieważ parser nie wie nic o zestawie, do którego go porównujesz - nie jest to pusty zestaw, ale nieznany zestaw. Użycie (1, 2, null) nie pomaga, ponieważ zestaw (1,2) jest oczywiście fałszywy, ale wtedy jesteś i ' ing to przeciwko unknown, który jest nieznany.

Jeśli chcesz filtrować Z NOT in dla zapytania podrzędnego containg null justcheck for not NULL

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

SQL używa logiki trzech wartości dla wartości prawdy. Zapytanie IN daje oczekiwany wynik:

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

ale dodanie NOT nie odwraca wyników:

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

Wynika to z tego, że powyższe zapytanie jest równoważne z następującym:

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

Oto jak oceniana jest klauzula 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⁽³⁾                  |

Zauważ, że:

1. porównanie z NULL daje UNKNOWN
2. wyrażenie OR gdzie żaden z operandów nie jest TRUE i przynajmniej jeden operand to UNKNOWN daje UNKNOWN (ref )
3. NOT z UNKNOWN daje UNKNOWN (ref )

Można rozszerzyć powyższy przykład na więcej niż dwie wartości (np. NULL, 1 i 2), ale wynik będzie taki sam: jeśli jedna z wartości to NULL, to żaden wiersz nie będzie pasował.

To dla chłopca:

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

To działa niezależnie od ustawień ansi

Również to może być przydatne do poznania logicznej różnicy między join, istnieje i w http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx