fbpx
devstyle.pl - Blog dla każdego programisty
devstyle.pl - Blog dla każdego programisty
10 minut

Null nullowi nierówny – czyli „nic” w SQL Server


14.05.2019

Cześć! Dawno się nie widzieliśmy. Wciąż żyję, wciąż dłubię w SQL-u i wciąż mam o tym coś do powiedzenia. Dziś chciałbym opowiedzieć Wam trochę o… niczym. Okazuje się, że nie jest to tak prosty temat, jak by się mogło wydawać. Jeśli programujesz w innym języku, to z pewnością zdarzyło Ci się z nullem w tej lub innej formie zetknąć. A jak sytuacja wygląda w naszych ukochanych bazkach? O tym za chwilę.

Najpierw jednak muszę się usprawiedliwić, bo jakiś czas mnie tu nie było. Pierwszy kwartał był dla mnie okresem, w którym musiałem się odsunąć od pozapracowych aktywności. Nowy rok, nowa praca, nowe mieszkanie, kilka nowych wyzwań na dalsze miesiące. Słowem – nowy ja. Mam nadzieję, że to rozumiesz i że brakowało Ci mnie chociaż trochę.

Null jaki jest, każdy widzi

O niczym, czyli o nullu, można wbrew pozorom powiedzieć całkiem sporo. Wielu osobom spędza on sen z powiek. Był przyczyną mnóstwa przedziwnych i niespodziewanych wyjątków. Tony Hoare, który ponad 50 lat temu zaimplementował to cudo po raz pierwszy, wręcz przepraszał za swój pomysł.

Zacznijmy więc od JavaScript. Aby sprawdzić, jak to działa, wystarczy wdusić F12 na klawiaturze i wkleić w konsolę poniższy kawałek kodu.


var x = null;
var y = null;
console.log(x == y);
console.log(x === y);

Oba porównania zwracają true. Nawet biorąc pod uwagę słynne już dziwaczne wyniki porównań w JS, było to do przewidzenia. A jak sytuacja będzie wyglądała w C#? Weźmy na warsztat taki kawałek kodu w prostej konsolówce.


string x = null;
string y = null;
Nullable<int> z = null;

Console.WriteLine(x == y);
Console.WriteLine(z.Equals(x));
Console.WriteLine(ReferenceEquals(z, x));

C# jest silnie typowanym językiem, więc pokusiłem się o sprawdzenie równości na nieco więcej sposobów, również porównując dwie zmienne zupełnie różnych od siebie typów.

I znowu: wszystko jest sobie równe. Nie jest to zaskakujące, bo przecież jedno nic i drugie nic są dokładnie tym samym, prawda? Nie będę wchodzić w szczegóły, jak to działa w innych językach, ale jeśli chcesz się dowiedzieć o tym trochę więcej w kontekście C#, to polecam prezentację Mariusza Dobrowolskiego – tutaj. A my przejdźmy do tabelek.

NULL w SQL-u

Jak zawsze musimy zacząć od przygotowania bazy, na której będziemy operować. Stwórzmy zatem tabelę przechowującą dane jakichś osób, przy czym drugie imię i adres mailowy określmy jako nullowalne, bo – w odróżnieniu od imienia i nazwiska – ktoś może ich nie mieć.


CREATE DATABASE [Sample]
GO

USE [Sample]
GO

CREATE TABLE [dbo].[People]
(
[Id] int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[FirstName] nvarchar(max) NOT NULL,
[LastName] nvarchar(max) NOT NULL,
[MiddleName] nvarchar(max) NULL,
[Email] nvarchar(max) NULL
)
GO

INSERT INTO [dbo].[People]
([FirstName], [LastName], [MiddleName], [Email])
VALUES
(N'Rafał', 'Hryniewski', NULL, 'rafal@hryniewski.net'),
('Maciej', 'Aniserowicz', NULL, NULL),
('Jan', 'Kowalski', 'Andrzej', 'test@test.test'),
('Anna', 'Nowak', NULL, NULL)
GO

Struktura jest dosyć prosta. Wyobraźmy sobie, że z jakiegoś powodu musimy wybrać wszystkich użytkowników, którzy nie mają adresu e-mail, oraz tych, którzy mają jakikolwiek adres. Myślę, że wiesz, jak to zrobić, ale zacznijmy od sposobu niepoprawnego.


SELECT *
FROM [dbo].[People]
WHERE [Email] = NULL

SELECT *
FROM [dbo].[People]
WHERE [Email] <> NULL

I niespodzianka. Oba wyniki są puste mimo tego, że posiadamy przecież rekordy zawierające zarówno NULL, jak i jakąś wartość w kolumnie Email. Pójdźmy dalej i powtórzmy poprzednie eksperymenty z innych języków. Niestety typ boolean w MS SQL nie może być przypisany do czegokolwiek, więc składnia jest nieco brzydka, bo musimy skorzystać z konstrukcji CASE WHEN.


DECLARE @x int = 1;
DECLARE @y int = 1;
DECLARE @z int = NULL;

DECLARE @result1 bit;
DECLARE @result2 bit;
DECLARE @result3 bit;
DECLARE @result4 bit;

SET @result1 = CASE WHEN @x = @y THEN 1 ELSE 0 END;
SET @result2 = CASE WHEN NULL = @x THEN 1 ELSE 0 END;
SET @result3 = CASE WHEN @z = @z THEN 1 ELSE 0 END;
SET @result4 = CASE WHEN @z <> @z THEN 1 ELSE 0 END;

SELECT @result1, @result2, @result3, @result4;

Jeśli nie chce Ci się wklejać tego kodu do Management Studio, to mogę zdradzić, że tylko @result1 przyjmie wartość 1. Zwróć jednak baczną uwagę na przypisanie wartości zmiennej @result3, porównujemy tu ze sobą dwie zmienne @z, które uprzednio ustawiliśmy jako NULL. W przykładach w innych językach sytuacja byłaby odwrotna.

Jeszcze ciekawiej wygląda na tym tle przypisanie wartości zmiennej @result4. Tutaj również kończymy z zerem, a porównujemy przecież, czy @z jest różne od @z. Jaki z tego wniosek? Wychodzi na to, że @z z wartością ustawione na NULL nie jest sobie równe. Nie jest też od siebie różne.

WTF?

Wbrew pozorom jest to bardzo logiczne zachowanie, ale zanim wyjaśnię, dlaczego tak jest, pozwólmy sobie na jeszcze jeden mały eksperyment przy wykorzystaniu utworzonej uprzednio tabeli.

Załóżmy, że dla każdego rekordu w tabeli będziemy chcieli skleić ze sobą pierwsze i kolejne imiona z nazwiskami. Po co? Powiedzmy, że chcemy zaadresować koperty, przesyłki lub zrobić cokolwiek, co ma mniej lub więcej sensu, ale wymaga takiej operacji. Na razie odpuśćmy sobie dbanie o odpowiednią liczbę spacji między wyrazami, jeśli brakuje nam wartości w którejś z kolumn.

SELECT
    [Id],
    [FirstName] + ' ' + [MiddleName] + ' ' + [LastName] AS FullName
FROM [People]

Jakie wyniki dostaniemy? Mimo że patrząc na dane przykładowe, z pewnością jesteś w stanie stwierdzić, że powinniśmy móc wygenerować takie dane, to okazuje się, że 3 z 4 rekordów mają wartość NULL. Oto wyniki:


NULL

NULL

Jan Andrzej Kowalski

NULL

Co powinniśmy zrobić, aby wszystko zostało zwrócone w sposób poprawny? (Wciąż olewamy liczbę spacji).


SELECT 
   [Id],
   [FirstName] + ' ' + COALESCE([MiddleName], '') + ' ' + [LastName] AS FullName
FROM [People]

Różnica polega na zastosowaniu funkcji COALESCE. O tym, co ona robi, już za chwilę. Wyniki, jakie dostaniemy, są już nieco bardziej satysfakcjonujące.


Rafał  Hryniewski

Maciej  Aniserowicz

Jan Andrzej Kowalski

Anna  Nowak

Zostaje jeszcze problem podwójnych spacji. W sumie nie jest to tematem tego posta, ale jeśli ktoś jest ciekawy, jak się z tym uporać, to odpowiedź znajdzie poniżej. Wyniki z tego fragmentu kodu sprawdź sobie samodzielnie i jego analizę potraktuj jak pracę domową. Podpowiem tylko, że jeśli rozbijesz wszystko na pojedyncze wywołania funkcji, to stanie się to naprawdę oczywiste.

SELECT
[Id],
CONCAT([FirstName], ' ', COALESCE([MiddleName] + ' ', ''), [LastName])AS FullName2
FROM [People]

Wróćmy jednak do samej operacji łączenia stringów. Pozwól, że w końcu wytłumaczę, co się dzieje i dlaczego NULL zachowuje się w taki sposób. To już przedostatni przykład w tym poście i zaraz wszystko powinno Ci się rozjaśnić. Jak myślisz, co zwróci takie zapytanie wykonane przez MS SQL?


SELECT 2 + NULL

Pewnie już się domyślasz, że wynikiem jest NULL. Spróbujmy przełożyć całe zapytanie na język polski (nawiasem mówiąc, uwielbiam SQL między innymi za to, że da się to zrobić w tak prosty sposób). Pierwsze, co przychodzi do głowy w zetknięciu z nullem, to:


WYBIERZ WYNIK Z

2

PLUS

NIC

2 + nic daje nam… dalej 2. Ale muszę Ci powiedzieć, że tłumaczenie nie jest poprawne. Właściwe powinno brzmieć:


WYBIERZ WYNIK Z

2

PLUS

NIE WIEM

Jeśli więc do 2 dodamy nieznaną wartość, to otrzymamy… nieznaną wartość.

Wszystko nabiera sensu?

Przypomnij sobie teraz każdy z przykładów, które przytoczyłem, i zamiast traktować NULL jako nic, pomyśl o nim jako o nieznanej wartości, jako „nie wiem”.

W SQL-u mamy do czynienia z trójwartościową logiką (three-valued logic). Oznacza to ni mniej, ni więcej tyle, że oprócz standardowych wartości operacji prawda i fałsz istnieją jeszcze wartości nieznane. Takie założenie sprawia również, że jeśli zrobimy z wartością nieznaną cokolwiek bez wskazania, jak się zachować w przypadku napotkania nulla, to w większości przypadków nie czeka nas żaden NullReferenceException czy inna niespodzianka. Wynik będzie najzwyczajniej w świecie nieznany.

„Ile cebuli on kupił?”

„Kto?”

„Nie wiem!”

„To skąd mam wiedzieć, ile czegokolwiek kupił?!”

Wróćmy jeszcze na chwilę do jednego z przykładów, konkretnie tego, w którym sklejaliśmy poszczególne kolumny w pełne imię użytkownika.


SELECT 
   [Id],
   [FirstName] + ' ' + COALESCE([MiddleName], '') + ' ' + [LastName] AS FullName
FROM [People]

Co robi COALESCE? Mówi nam, jak potraktować daną wartość, gdy nie znamy jej wartości. Po raz kolejny przetłumaczmy całość na język polski:


WYBIERZ

Id,

FirstName + spację + MiddleName lub pusty string, jeśli nie wiemy, jaki jest MiddleName + spację + LastName jako FullName

Z TABELI [People]

Prawda, że wszystko nabrało sensu?

Dlaczego tak jest? NULL odzwierciedla brak danych na jakiś temat. To, jak go potraktujesz, zależy już tylko od Ciebie. Jeśli założysz, że wszystkie Twoje dane z dowodu osobistego znajdują się w jednej tabeli, to brak, na przykład, peselu będzie najpewniej brakującą informacją. Powiedziałbym więc, że NULL jest jak najbardziej na miejscu. W przypadku gdy ktoś nie ma drugiego imienia, prawidłową wartością byłby raczej pusty nvarchar, bo żadne dane w rzeczywistości nie zostały pominięte.

W praktyce warto jednak pamiętać, że NULL w kolumnach może oznaczać zarówno brakujące dane, jak i ich faktyczny „pusty” stan.

Mam nadzieję, że było fajnie i zrozumiale. Postaram się też, aby było nieco częściej. Do przeczytania!

Nie przegap kolejnych postów!

Dołącz do ponad 9000 programistów w devstyle newsletter!

Tym samym wyrażasz zgodę na otrzymanie informacji marketingowych z devstyle.pl (doh...). Powered by ConvertKit
Notify of
Szymon

Kiedyś null w SQL Server dość dużo mi namieszał w jednym projekcie (na szczęście w porę zauważyłem problem). Warto rozszerzyć artykuł o informację, iż można w obrębie sesji ustawić właściwość:

SET ANSI_NULLS OFF

i wtedy zapytanie typu:

declare @var1 int = null;
declare @var2 int = null;

if @var1 = @var2
select ‘test’;

zwróci nam ‘test’ i null będzie traktowany w sposób, w którym “nic będzie czymś” :)

Jednakże jest to bardziej ciekawostka. W zapytaniach po prostu stosuję “is null / is not null / isnull / coalesce”
Bardzo przyjemnie się czytało ten artykuł.

norbitek
norbitek

Z mojego doświadczenia pamiętam, że największy wpływ na wynik ma w SQL-ach typu SELECT * FROM tabela WHERE kolumna IN (SELECT kolumna_2 FROM tabela_2) w sytuacji gdy podzapytanie zwraca choć jeden wiersz z NULLem. Wtedy całe zapytanie nic nie zwraca. W NOT IN tego problemu nie ma. Dlatego pewniej jest stosować NOT EXISTS.

michal
michal

tak lub w podzapytaniu gdzie ma IN odfiltrowac NULLe. Tak czy owak celna uwaga.

Andrzej
Andrzej

Nie bardzo rozumiem sens tego tekstu w sytuacji, kiedy w języku SQL istnieje klauzula IS NULL oraz IS NOT NULL? Dla mnie artykuł o niczym.

Bartek

“Powszechny błąd popełniany przy projektowaniu baz danych polega na
próbie uwzględnienia dużej liczby możliwych cech w ramach jednej relacji,
co skutkuje dużą liczbą kolumn. Niektóre dziedziny nauki mogą wymagać
dużej szczegółowości cech analizowanych zjawisk, a co się z tym wiąże
dużej liczby opisywanych atrybutów obiektów. W przypadku zastosowań
biznesowych bywa tak jednak bardzo rzadko. Niechybnym sygnałem,
że tabela „padła ofiarą” tego typu nadmiaru, jest sytuacja, gdy większość
atrybutów wiersza jest NULL….”

Temat ciekawy który został mocno rozwinięty i chyba wyczerpany ;) w książce z której pochodzi powyższy cytat: The Art of SQL – Stephane Faroult, Peter Robson.
Polskie wydanie: https://helion.pl/ksiazki/sql-sztuka-programowania-stephane-faroult-peter-robson,sqlszp.htm#format/e

K
K

Java to nie to samo co JavaScript… Xd

Maciej Aniserowicz

To żart, ale jak widać – nie wszedł :). Zmienione.

Marcin
Marcin

Java to nie javascript

Adam

Mssql isnotnull
Oraz isnull

Moja książka

Facebook

Zobacz również