Z dokumentacji technet ( https://msdn.microsoft.com/en-us/library/ms188048(v=sql.105).aspx )

Specifies ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values.

Witajcie. Dzisiaj omówimy wyjątkowo perfidny przypadek, który może łatwo doprowadzić do katastrofy ;), jeśli nie pamięta się o tym jak w świecie zbiorów danych traktowana jest „nieznana” wartość czyli NULL  😉

Gdzieś kiedyś ktoś zdefiniował w standardzie SQL jak ma być ona traktowana (sugerując się ogólną definicją Edgara Franka Codda). Skoro coś nie jest znane to nie może być traktowane jako wartość a więc jest niestosowalne, a co za tym idzie, nieporównywalne. (jeśli ktoś jest ciekawski polecam poczytać o logice trójwartościowej ) Jak zatem wygląda to w SQL SERVER?

Sprawa jest prosta:

• domyślnie NULL traktowany jest zgodnie ze standardem, a więc jest niestosowalną i nieporównywalną „nicością”,ale…
• …żeby móc jej używać 🙂 trzeba stosować specjalne metody, odrębne od klasycznych dostępnych dla wszystkich istniejących wartości. Zatem w odróżnieniu od operatorów równości i nierówności, które można używać na wartościach tutaj trzeba posłużyć się IS NULL, IS NOT NULL a wszędzie tam gdzie chcemy użyć metod naturalnych dla wartości tj. sumy, różnicy, iloczynu, konkatenacji etc, NULLa musimy podmienic jakąś wartością domyślną np przy pomocy ISNULL lub COALESCE
• powyższa logika dotyczy zarówno warunków filtrowania wierszy (WHERE) jak i warunków złączenia (JOIN na wartościach NULL). Wartości null są też pomijane przy agregacji (SUM,AVG etc)
• istnieje sposób na wyłączenie tego i traktowanie NULLA jak „wartosci” do porównań explicite kolumna = NULL (SET ANSI_NULLS OFF), jednak nie będzie możliwe jego wykorzystywanie w przyszłych edycjach SQL SERVER (polecenie oznaczone jest statusem deprecated, w przyszlosci wywołanie tego polecenia może zwrócić błąd!) zatem lepiej go nie stosować w ogóle!
• TO OZNACZA, że jeśli wydacie polecenie WHERE moja_kolumna <> 'jakas wartosc, ktorej nawet nie ma w danych’, to wynikiem nie będzie cała tabela, a jedynie rekordy, które w polu moja_kolumna nie maja danej wartości ale również nie są NULLami!!!!! Niezależnie od tego czy ktoś wyłączył ANSI_NULLS czy nie.

Przykład:

Tworzymy tabelę, wstawiamy dwie wartości:  'wartosc’  i  NULL

CREATE TABLE #T
    (
      id INT IDENTITY
             PRIMARY KEY ,
      kolumna VARCHAR(50)
    )

INSERT  INTO #T
VALUES  ( 'wartosc' ),
        ( NULL )

SELECT  *
FROM    #T

 

Zobaczmy czy możemy znaleźć dane explicite używając = NULL:

SELECT  *
FROM    #T
WHERE   kolumna = NULL

 

Nie ma, zatem zmieńmy ansi_nulls i sprawdźmy czy teraz da się to zrobić:

SET ANSI_NULLS OFF

SELECT  *
FROM    #T
WHERE   kolumna = NULL

 

czy teraz nawet przy ansi_nulls off mozemy zwrocic takie kolumny, których kolumna <> 'nieistniejąca wartość’ ?:

SELECT  *
FROM    #T
WHERE   kolumna <> 'nieistniejąca wartość'

 

A co z JOINem? Nie ma różnicy!

SET ANSI_NULLS ON
Podejrzyj
SELECT  *
FROM    #T t1
        JOIN #T t2 ON t1.kolumna = t2.kolumna


SET ANSI_NULLS OFF
SELECT  *
FROM    #T t1
        JOIN #T t2 ON t1.kolumna = t2.kolumna

 

Jak to obejsc? Na JOINie musimy podmienic w warunku NULLa na wartosci domyslne np napis 'domyślny’:

SET ANSI_NULLS ON
SELECT  *
FROM    #T t1
        JOIN #T t2 ON ISNULL(t1.kolumna,'domyślny') = ISNULL(t2.kolumna,'domyślny')

Zatem rada na przyszłość… jesli definicja kolumny, z której korzystacie, nie ma NOT NULL… zastanówcie się dwa razy zanim wykonacie na danych warunki porównania.. W przeciwnym wypadku istnieje duże ryzyko zgubienia wierszy!

 

POST TEN ZOSTAŁ PIERWOTNIE OPUBLIKOWANY JAKO TSQL NA DZIŚ #22