W relacyjnych systemach baz danych, elementy (wiersze) przechowywane w tabelach, mogą być opisywane przez atrybuty (kolumny) określone jak i nieokreślone, czyli poprostu zawierające wartości bądź nie.

Atrybut nieokreślony to taki, który nie ma wartości. „Wartość” taką, oznaczamy jako NULL (w teorii baz danych NULL oznaczany jest małą grecką literą – omega ω ). Zazwyczaj będzie to atrybut opcjonalny, nieobowiązkowy lub po prostu nie określony.

Ten sposób postrzegania rzeczywistości i modelowania jej w bazach, od lat dostarcza wielu dyskusji z uwagi na potencjalne nieścisłości, które z tego tytułu się pojawiają. Artykuł ten, nie ma na celu odnoszenia się do dyskutowania problemów relacyjnych. Przedstawiam tutaj wszystkie fakty istnienia NULL w SQL Server od strony praktycznej, związanej z pisaniem zapytań.

Logika Trójwartościowa

Z uwagi na to, że nie wszystkie kolumny muszą posiadać określone wartości (dane), wprowadzono pojęcie logiki trójwartościowe. Zgodnie z nią, rezultat porównania dwóch wartości, może przyjmować jeden z 3 stanów :

• TRUE – gdy wartości są równe
• FALSE – gdy się różnią
• UNKNOWN – wynik porównania nie jest znany

Dwóch pierwszych nie ma co omawiać, bo z pewnością były dokładnie wytłumaczone w pierwszych latach szkoły podstawowej. Ciekawszym stanem jest wynik UNKNOWN – który jest związany właśnie z wartością NULL – czyli wartością nieznaną / nieokreśloną.

Wartość NULL definiuje atrybut lub logiczny wynik porównania jako nieokreślony, niezdefiniowany. Może występować w rekordach (elementach zbioru), w kolumnach, którym nie zostały określone wartości.

NULL nie jest równy 0, ani też nie jest to wartość pusta w sensie pustego stringu ”. Lepszym porównaniem byłaby tu próżnia – coś co nic w sobie nie zawiera.

Praca z NULL

Warunki logiczne

Wartości nieokreślonej nie możemy sensownie porównać z czymkolwiek gdyż wynikiem takiego porównania będzie również wartość nieokreślona czyli UNKNOWN. Trudno odnieść się do czegoś czego się nie zna lub czego nie ma – więc jak to porównać. A co w takim razie da porównanie dwóch wartości NULL ?

-- deklaracja zmiennych, bez przypisania wartości, 
-- każdy z nich przechowuje wartość nieznaną NULL
declare @wartosc_a int,@wartosc_b int

select CASE WHEN @wartosc_a = @wartosc_b THEN 'EQUAL' 
	    WHEN NOT (@wartosc_a = @wartosc_b ) then 'NOT EQUAL'
	    WHEN @wartosc_a != @wartosc_b then 'VARIOUS'
	    ELSE 'UNKNOWN' END as NULLsComaprisionResult

Wynik porównania dwóch wartości NULL to także wartość nieznana. Nie jest ani większa, ani równa. Podobnie, jeśli byśmy chcieli porównać wartość znaną, z czymś czego nie znamy – wynik zawszę będzie UNKNOWN.

Znajomość NULL skutkuje w wielu miejscach związanych z pisaniem zapytań.
W warunkach filtracji, obojętnie czy będzie to filtracja w WHERE, we FROM – jako warunek złączenia czy w HAVING, zwracane są tylko te wiersze, dla których wynik porównania jest spełniony – czyli zwraca wartość TRUE.

Ponieważ porównanie czegokolwiek z NULL, zwraca wartość UNKNOWN, dlatego w interpretacji filtrowania rekordów, wiersze dla których taki wynik zostanie wyznaczony – będą odrzucone.

Jeśli spróbowalibyśmy odnieść się do wartości NULL w ten sposób :

USE Northwind
GO

-- Pomimo istnienia rekordów dla których Region nie jest nullem, 
-- tak skonstruowane zapytanie nic nie zwróci
select * from dbo.Employees
where Region <> null

Zapytanie nie zwróci żadnego rekordu :

(0 row(s) affected)

Pomimo że istnieją rekordy, dla których wartość kolumny Region nie jest NULLem, ale żadne z takich porównań nie zwróci TRUE (chyba że mamy ustawiony brak zgodności ze standardem ANSI – opisuje to w dalszej części artykułu). Wynik jakichkolwiek porównań z NULL to zawsze wartość nieznana UNKNOWN, choć czasem mylnie interpretowany jako FALSE. Dlatego jeśli zależy nam na porównaniach odnoszących się do NULL musimy jawnie używać specjalnie do tego celu stworzonego zapisu czyli IS NULL lub IS NOT NULL :

-- poprawne porównywanie do NULL
select LastName, FirstName, Region from Employees
where Region IS NOT NULL

Z tego samego powodu, zapytanie w którym użyjemy w warunku WHERE, operatora IN – tłumaczonego na szereg warunków logicznych OR, nie zwróci rekordów dla których kolumna wartość zawiera NULL

select LastName, FirstName, Region 
from Employees
-- IN to równoważnik poniższego zapisu : Region = 'WA' or Region = NULL
where Region IN ('WA',NULL)

Wartość NULL w wyrażeniach

Wykonując jakiekolwiek działania na wartościach nieznanych, należy spodziewać się również, że i wynik będzie tak samo dokładny jak i wartości na których operujemy czyli UNKNOWN.
Zobaczmy do jakiego rezultatu doprowadzą, wszelkiego rodzaju działania z wartością nieokreśloną :

Select  100000 + NULL as wynik1,
	   (5000+300) * 2 - null as wynik2,
	   'Ala ma ' + 'kota' + null + '!' as wynik3

Wszystkie operacje z udziałem wartości nieznanej – dają zawsze w wyniku NULL. Niezależnie czy są wartości przechowywane jako zmienne, kolumny czy stałe – cokolwiek połączymy z NULL – da nam NULL.
Ponieważ wartości nieokreślone są często spotykane w bazach, musimy sobie jakoś z nimi radzić. Tylko w jaki sposób je traktować. Pawdopodobnie przy operacjach matematycznych, chcielibyśmy traktować NULL jak 0 natomiast w wyrażeniach łączącym stringi – interpretować jako string pusty ”. Jest to więc bardzo subiektywne, w zależności od konkretnego scenariusza. Rozpatrzmy następujący przypadek :

USE AdventureWorks2008
GO

SELECT FirstName + ' ' + MiddleName + ' ' + LastName  AS FullName,
 Firstname, MiddleName, LastName
FROM Person.Person

Kolumna MiddleName, zawiera wartości NULL, stąd wszędzie tam gdzie się pojawia wartość nieokreślona, wynik złączenia tekstu jest również nieokreślony.

Wpływ NULLi na określone działania możemy zniwelować za pomocą wyrażeń warunkowych CASE WHEN, podstawiając w tym przypadku wartość stringu pustego ” w miejsce NULL:

SELECT FirstName + ' ' + 
       CASE WHEN MiddleName is null THEN '' ELSE MiddleName END
       + ' ' + LastName  AS FullName,
       FirstName, MiddleName, LastName
FROM Person.Person

Sposób skuteczny, ale mało wygodny. Mamy na szczęście do dyspozycji kilka funkcji wbudowanych, które pomagają w radzeniu sobie z NULLami i upraszaczją kod SQL.

Funkcje wspierające przy pracy z NULL

ISNULL ( wartość , wartość_zastępcza ) jedna z dwóch najczęściej stosowanych i użytecznych do pracy z NULLami. Podstawia wartość wskazaną w drugim argumencie, jeśli wykryje w danej kolumnie NULL. Zastosujmy ją więc do poprzedniego przykładu, zamiast CASE WHEN :

SELECT FirstName + ' ' + ISNULL(MiddleName,'') + ' ' + LastName  AS FullName
FROM Person.Person

COALESCE ( wartosc_1 , wartosc_2 … wartosc_n ) – zwraca pierwszą nie-nullową wartość z listy. Startuje od wartości podanej jako pierwsza, jeśli nie jest NULLem to ją zwraca a jeśli jest to analizuje kolejną podaną w jej definicji. Równoważnik takiego zapisu np. dla 3 argumentów :

COALESCE ( wartosc_1 , wartosc_2 , wartosc_3)

-- równoważnik z wykorzystaniem IS NULL
ISNULL ( ISNULL( wartosc_1 , wartosc_2 ), wartosc_3 )
-- z wykorzystaniem CASE WHEN
CASE WHEN wartosc_1 is not null then wartosc_1
     WHEN wartosc_2 is not null then wartosc_2 
     WHEN wartosc_3
end

NULLIF (wartosc1, wartosc2) – porównuje dwie wartości i zwraca NULL jeśli są równe.
Jest logicznym odpowiednikiem zapisu warunkowego :

CASE WHEN wartosc1 = wartosc2 THEN NULL ELSE wartosc1 END

Logicznie uzasadnione wyjątki

Są wyjątki w implementacji logiki trójwartościowej (TRUE, FALSE, UNKNOWN), które po analizie możemy zakwalifikować do łamiących jej reguły.

Poniższe przypadki, mają na celu pokazanie kilku uzasadnionych odstępstw, aby bardziej świadomie patrzeć na obowiązujące zasady w bazach danych.

Miejsca, gdzie wynik porównania wartości NULL traktowany jest jako TRUE :

• ORDER BY – wszystkie wartości kolumny, które zawierają NULL i po której sortujemy – są traktowane jako równe – występują obok siebie.
• GROUP BY – wszystkie elementy, grupowane po atrybucie zawierającym NULL, tworzą jedną grupę, zatem również traktowane są jak równe.
• UNIQUE CONSTRAINT – w kolumnie unikalnej, gdy jest już w niej wartość NULL, przy próbie wpisania kolejnego, traktowane są jakby NULLe były sobie równe (jeden już jest, więcej nie można).
• budowanie indeksu na kolumnie zawierającej NULLe – traktuje wartości nieokreślone jak równe. Efektywność indeksu na kolumnie zawierającej nulle maleje.

Porównanie NULL z wartością znaną, czasem daje TRUE

• jeśli zdefiniujesz ograniczenie np. CHECK CONSTRAINT >0 na kolumnie przechowującej wartości liczbowe. Przy próbie wpisania wartości NULL – wynik porównania będzie spełniony tak jakby NULL faktycznie był większy od 0 (tylko liczby ujemne i 0 nie spełnią tego ograniczenia).

Standard ANSI

Sposób porówynania wartości null opisany do tej pory, jest zgodny ze standardem ANSI SQL:92. W SQL Server, dostępna jest możliwość wyłączenia tej zgodności np. na poziomie połączenia (sesji), poprzez ustawienie

SET ANSI_NULLS OFF

Szczerze mówiąc, nie spotkałem się z przypadkami w rzeczywistych systemach, gdzie korzystało by się z globalnego wyłączenia zgodności ze standardem ANSI w kontekście całej bazy. W sporadycznych sytuacjach, można łatwo zmienić sposób interpretacji porównania z null. Jeśli ustawisz ANSI_NULLS na OFF – wtedy NULL będzie traktowany jak zwykła wartość czyli wynik warunku np. NULL = NULL będzie TRUE.

--Wylaczenie zgodnosci z ansi (domyslnie jest włączone)
SET ANSI_NULLS OFF

select FirstName, LastName, Region
from Employees
where Region = NULL