NULL w SQLite: IS NULL, COALESCE i IFNULL

NULL oznacza "nieznane"

Każda inna wartość w SQLite oznacza coś konkretnego: liczbę, tekst, blob. NULL jest inny. To znacznik wartości, której brakuje albo której nie znamy. Ta jedna myśl wyjaśnia wszystkie dziwne rzeczy, które NULL wyprawia w zapytaniach.

Przygotuj małą tabelę do eksperymentów:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT * FROM users;

Dwie kolumny dopuszczają NULL. Boris nie ma e-maila. Cleo nie ma wieku. Dan nie ma ani jednego, ani drugiego. Reszta strony pokazuje, jak odpytywać takie wiersze i nie dać się oszukać.

= i <> nie działają z NULL

Pierwszy odruch to napisać WHERE email = NULL. Wygląda sensownie. Nie zwraca nic:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email FROM users WHERE email = NULL;

Zero wierszy, choć Boris i Dan wyraźnie mają e-mail równy NULL. Powód: porównanie czegokolwiek z NULL daje NULL, a nie prawdę ani fałsz. Klauzula WHERE w SQLite zostawia tylko wiersze, w których warunek jest prawdziwy, a NULL prawdą nie jest. Dlatego wiersz zostaje odfiltrowany.

Ta sama pułapka z <>:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email FROM users WHERE email <> 'ada@example.com';

Można by oczekiwać, że zwróci wszystkich poza Adą. Zwraca tylko Cleo. Boris i Dan, których e-maile są NULL, wypadają, bo NULL <> 'ada@example.com' to też NULL, a nie prawda.

To najczęstsza pułapka w SQL. Kiedy zapytanie "gubi wiersze", których się nie spodziewasz, podejrzewaj kolumnę z NULL.

Używaj IS NULL i IS NOT NULL

Właściwy sposób sprawdzania NULL to operator IS. W przeciwieństwie do = rozumie NULL i zwraca prawdę albo fałsz, nigdy NULL:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name FROM users WHERE email IS NULL;

SELECT name FROM users WHERE email IS NOT NULL;

Pierwsze zapytanie zwraca Borisa i Dana. Drugie zwraca Adę i Cleo. IS NULL i IS NOT NULL to dwa operatory stworzone specjalnie po to, by zapytać "czy tej wartości brakuje?". Używaj ich wszędzie tam, gdzie kusi cię napisanie = NULL lub <> NULL.

Jeśli chcesz dostać "wszystkich poza Adą, łącznie z nieznanymi", połącz warunki jawnie:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email
FROM users
WHERE email <> 'ada@example.com' OR email IS NULL;

Teraz pojawiają się Boris, Cleo i Dan.

NULL przechodzi przez arytmetykę i łączenie tekstów

Zasada "nieznanego" nie dotyczy tylko porównań. Każda operacja, która dotyka NULL, daje NULL:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    age,
    age + 1       AS next_year,
    age * 2       AS doubled,
    'Cześć ' || name AS greeting,
    'Wiek: ' || age AS labelled_age
FROM users;

next_year i doubled są NULL dla Cleo i Dana. labelled_age też jest dla nich NULL: połączenie tekstu z NULL daje NULL, a nie 'Wiek: '. Jeśli kolumna może być NULL, a na wyjściu potrzebujesz użytecznej wartości, musisz to obsłużyć. Do tego służą dwie kolejne funkcje.

IFNULL: wartość zastępcza z dwoma argumentami

IFNULL(a, b) zwraca a, chyba że jest NULL, a wtedy zwraca b. To najprostszy sposób, by zamienić NULL na wartość domyślną:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    IFNULL(email, '(brak e-maila)') AS email,
    IFNULL(age, 0)              AS age
FROM users;

Boris i Dan dostają (brak e-maila). Cleo i Dan dostają 0. Dane w tabeli się nie zmieniają, IFNULL przepisuje tylko wynik.

IFNULL zawsze przyjmuje dokładnie dwa argumenty. Jeśli potrzebujesz więcej wartości zastępczych, sięgnij po COALESCE.

COALESCE: wygrywa pierwsza wartość różna od NULL

COALESCE(a, b, c, ...) przechodzi przez argumenty od lewej do prawej i zwraca pierwszy, który nie jest NULL. Uogólnia IFNULL na dowolną liczbę wartości zastępczych:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    COALESCE(email, name || '@nieznany.local', 'anonim') AS contact
FROM users;

Dla Ady i Cleo używany jest e-mail. U Borisa i Dana e-mail to NULL, więc SQLite próbuje drugiego argumentu: adresu zbudowanego z imienia. Gdyby on też był NULL, zostałby użyty 'anonim'.

COALESCE to wybór przenośny: każda popularna baza SQL obsługuje go tak samo. IFNULL to wygodny skrót z SQLite i MySQL dla przypadku z dwoma argumentami. Domyślnie wybieraj COALESCE, a po IFNULL sięgaj tylko wtedy, gdy naprawdę masz dwa argumenty i chcesz krótszej nazwy.

NULL to nie pusty ciąg znaków

Częste nieporozumienie: ludzie traktują NULL i '' jako wymienne. Tak nie jest.

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    '' IS NULL          AS empty_is_null,
    NULL IS NULL        AS null_is_null,
    length('')          AS empty_length,
    length(NULL)        AS null_length,
    '' = ''             AS empty_equals_empty,
    NULL = NULL         AS null_equals_null;

'' to prawdziwy tekst, który akurat ma zero znaków. NULL to brak wartości. length('') wynosi 0, a length(NULL) samo jest NULL. Do tego NULL = NULL daje NULL, a nie 1, i właśnie dlatego istnieje IS NULL.

Jeśli kolumna może zawierać zarówno '', jak i NULL, zdecyduj, które z nich oznacza "brak", i trzymaj się tego. Mieszanie obu zmusza każde zapytanie do obsługi dwóch przypadków, a o jednym na pewno kiedyś zapomnisz.

NULL w IN, NOT IN i DISTINCT

Jeszcze kilka miejsc, w których NULL potrafi zaskoczyć.

IN z listą zawierającą NULL może dać dziwne wyniki, zwłaszcza w połączeniu z NOT IN:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, age FROM users WHERE age NOT IN (25, NULL);

Można by oczekiwać wszystkich, których wiek nie wynosi 25. Nie dostajesz nic. SQLite rozwija NOT IN (25, NULL) mniej więcej do age <> 25 AND age <> NULL, a age <> NULL to zawsze NULL, więc cały warunek nigdy nie jest prawdziwy. Rozwiązanie: usuń NULL z listy (albo z kolumny) przed porównaniem.

Z kolei DISTINCT przy usuwaniu duplikatów traktuje wartości NULL jako równe sobie:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT DISTINCT email FROM users;

Dostajesz trzy wiersze: e-mail Ady, e-mail Cleo i jeden NULL (złożony z Borisa i Dana). Tak samo działają GROUP BY i UNION: traktują NULL jako jedną grupę, czyli odwrotnie niż =. SQL nie zawsze jest w tym konsekwentny, więc warto wiedzieć, po której stronie stoi każdy operator.

Krótka ściągawka

• Brakujące wartości sprawdzaj przez IS NULL / IS NOT NULL. Nigdy przez = NULL.
• Każda arytmetyka, łączenie tekstów czy porównanie z udziałem NULL zwraca NULL.
• Używaj COALESCE(a, b, c, ...), by zastąpić NULL wartością zastępczą. IFNULL(a, b) to skrót dla dwóch argumentów.
• Pusty ciąg '' to nie to samo co NULL. W każdej kolumnie wybierz jedno z nich jako "brak".
• NOT IN (..., NULL) to prawie zawsze błąd. Najpierw usuń NULL z listy.

Dalej: sortowanie wyników

Gdy już umiesz poprawnie filtrować wiersze, także te z NULL, kolejnym krokiem jest ułożenie ich w sensownej kolejności. Następna strona to ORDER BY, a ta klauzula ma własne zdanie o tym, gdzie w posortowanym wyniku lądują wartości NULL.