Porównanie wartości NULL w tabeli

Zawsze zastanawiam się nad jakimś tajemniczym zachowaniem t-sql, jak poniżej

-- Create table t and insert values.  
use tempdb
CREATE TABLE dbo.t (a INT NULL);  
-- insert 3 values
INSERT INTO dbo.t values (NULL),(0),(1);  
GO  
set ansi_nulls off -- purposely turn off, so we can allow NULL comparison, such as null = null
go
-- expect 3 rows returned but only 2 returned (without null value row)
select * from dbo.t where a = a 

Nie chodzi tu o to, jak pobrać wszystkie wiersze w tabeli, a także o unikanie użycia ANSI_NULLS.

Chcę tylko uzyskać pewne spostrzeżenia, dlaczego t-sql zachowuje się w ten sposób.

Jest to zaskakujące zachowanie, ale ze strony MSDN SET ANSI_NULLSmożemy przynajmniej wiedzieć, że jest to oczekiwane zachowanie. Jeszcze jeden powód, aby nigdy nie używać ANSI_NULLS OFF:

SET ANSI_NULLSwpływa na porównanie tylko wtedy, gdy jednym z operandów porównania jest albo zmienna, która jest, NULLalbo literał NULL. Jeśli obie strony porównania są kolumnami lub wyrażeniami złożonymi, ustawienie nie wpływa na porównanie.

Chociaż może to nie być krystalicznie jasne z dokumentacji msdn, wierzę, że znajdziesz następujące prawdziwe

„USTAW ANSI_NULLS WŁ. Wpływa na porównanie tylko wtedy, gdy jednym z operandów porównania jest zmienna o wartości NULL lub dosłowna wartość NULL. Jeśli obie strony porównania są kolumnami lub wyrażeniami złożonymi, ustawienie nie wpływa na porównanie.”

Zobacz /programming/2866714/how-does-ansi-nulls-work-in-tsql

Robert Sheldon w kolejnym poście z 2015 roku omawia zachowania NULL i dlaczego czasami (ale nie zawsze) zawodzi

https://www.simple-talk.com/sql/t-sql-programming/how-to-get-nulls-horribly-wrong-in-sql-server/

Opisuje 13 awarii NULL, które programista może łatwo potknąć.

Błąd nr 1: Nie wiem, co oznacza wartość NULL

Objaśnienie: NULL jest wartością nieistniejącą, nieistniejącą. To nie jest zero. To nie jest pusty ciąg. Wartość nie może być równa NULL. Żadne dwie wartości NULL nie są równe .

To jest podstawowy problem, ale pamiętaj o innych awariach.

Tak, wcześniejsze wersje (wierzę, że wcześniej niż SQL Server 7) zachowywały się inaczej, bardziej jak chcesz.

Jeśli jednak poszukasz problemu na stosie przepełnienia stosu i stosie wymiany, znajdziesz wiele długich wątków omawiających problemy.

Aby dodać do dyskusji, definicję NULL w standardzie SQL92 można interpretować niejednoznacznie. Oto dobre podsumowanie obsługi NULL i interpretacji z różnych DBMS dzięki uprzejmości sqlite.org.

UJAWNIENIE : Pamiętam, jak czytałem o „dwuznaczności” SQL92 ze starszej wersji (np. 6-8 lat temu) strony sqlite.org, do której prowadzi link powyżej, ale od tamtej pory ta strona została zaktualizowana.

Odpowiedź RLF jest powyżej ma dobrą ofertę, ale jeśli nie zgadzam się z Robertem Sheldon to tylko dlatego, że za „coś, co nie istnieje” (czyli NULL ) będzie filozoficznie i angielskojęzyczny-semantycznie równoważne „czegoś innego , który nie istnieje „. Jeśli mam zrozumieć logikę Sheldona, można by zadeklarować, że definicja NULL jest również NULL. (Jeśli nie istnieje, to jak możemy to zdefiniować? Przerażające, co?)

Widzę odmianę warzenia Paradoksu Russella ( i ból głowy). : - \

Ale znowu jest to dyskusja na temat semantyki języka angielskiego ( NIE SQL) i tutaj należy debata filozoficzna . :-)