Zapytanie działa bez błędów i zwraca rozsądnie wyglądającą liczbę, ale brakuje wierszy, a średnia jest błędna. Bardzo często przyczyną są Wartości SQL NULL. NULL nie zachowuje się jak jakakolwiek normalna wartość, co zwraca uwagę zarówno początkujących, jak i doświadczonych programistów.

Ten przewodnik pokazuje dokładnie, jak zachowuje się NULL. Uruchomiliśmy każde zapytanie w SQLite 3.50 i pokazane wyniki pochodzą z tego uruchomienia. Większość tych zachowań to standardowy SQL i zauważamy, gdzie bazy danych się różnią, na przykład porządek sortowania NULL i sposób traktowania pustych ciągów przez Oracle.

Co to jest NULL?

NULL jest znacznikiem oznaczającym brak wartości lub jest ona nieznana. Nie jest to wartość w zwykłym sensie, więc są to różne rzeczy:

Różnica ma znaczenie szczególnie w przypadku kwot. Bonus 0 oznacza, że ​​dana osoba nie otrzymała nic. Bonus NULL oznacza, że ​​nikt jeszcze nie wie.

Przykładowa tabela

CREATE TABLE employees (
  id INT, name TEXT, phone TEXT, bonus INT, manager_id INT
);

INSERT INTO employees VALUES
 (1,'Meera','555-0101',500,NULL),
 (2,'Arjun',NULL,300,1),
 (3,'Sara','',NULL,1),
 (4,'Ravi',NULL,NULL,2);

Zauważ, że Sara ma pusty ciąg znaków dla telefonu, podczas gdy Arjun i Ravi mają NULL. Obydwoje są traktowani zupełnie inaczej.

Testowanie dla wartości NULL

Oto błąd, który na początku popełnia prawie każdy:

SELECT name FROM employees WHERE phone = NULL;
-- (no rows)

SELECT name FROM employees WHERE phone IS NULL;
-- Arjun
-- Ravi

SELECT name FROM employees WHERE phone IS NOT NULL;
-- Meera
-- Sara

Pierwsze zapytanie jest prawidłowym kodem SQL i nie powoduje błędu. Po prostu nic nie zwraca, ponieważ porównywanie czegokolwiek z wartością NULL nigdy nie jest prawdą. Sara pojawia się pod IS NOT NULL, ponieważ pusty ciąg znaków jest wartością rzeczywistą. Jednak w Oracle pusty ciąg znaków jest traktowany jako NULL, co jest godnym uwagi wyjątkiem.

Logika trójwartościowa

W większości języków programowania warunek jest prawdziwy lub fałszywy. SQL dodaje trzeci wynik: nieznany. Jeśli nie znasz wartości, nie możesz stwierdzić, czy jest ona równa 5. Nie wiadomo też, czy dwie nieznane wartości są równe, dlatego NULL = NULL nie jest prawdą. W naszym teście wszystkie zwróciły NULL:

SELECT NULL = NULL, NULL <> 1, 1 + NULL, 'a' || NULL;

-- NULL | NULL | NULL | NULL

Łączenie warunków odbywa się według ostrożnych zasad. Oto wyniki naszego testu, gdzie 1 to prawda, a 0 to fałsz:

Wiersze, które znikają z WHERE

WHERE przechowuje wiersz tylko wtedy, gdy jego warunek jest true. Wiersze, których wynik jest nieznany, są usuwane wraz z tymi, które są fałszywe. Prowadzi to do zaskakujących wyników:

SELECT name FROM employees WHERE phone <> '555-0101';

-- Sara

Można się spodziewać, że pojawią się Arjun i Ravi, ponieważ ich telefon z pewnością nie jest tym numerem. Ale ich telefon jest nieznany, więc wynik jest nieznany i znikają. Aby je uwzględnić, powiedz to wyraźnie: WHERE phone <> '555-0101' OR phone IS NULL. Negacja też nie pomaga:

SELECT name FROM employees WHERE NOT (bonus > 400);

-- Arjun

Sara i Ravi nie mają żadnego bonusu, więc nie pojawiają się ani w bonus > 400, ani w jego przeciwieństwie. Jest to kluczowa rzecz do zapamiętania: warunek i jego negacja nie zawsze obejmują każdy wiersz.

COALESCE: zapewnienie rozwiązania awaryjnego

COALESCE zwraca pierwszą wartość inną niż NULL na swojej liście i działa ona we wszystkich głównych bazach danych:

SELECT COALESCE(NULL, NULL, 'third', 'fourth');
-- third

SELECT name, COALESCE(phone, 'no phone') FROM employees;
-- Meera | 555-0101
-- Arjun | no phone
-- Sara  |
-- Ravi  | no phone

Sara nadal jest pusta, ponieważ pusty ciąg nie ma wartości NULL. Istnieją podobne funkcje, takie jak IFNULL w MySQL i SQLite, ISNULL w SQL Server i NVL w Oracle. COALESCE to przenośny wybór. Nasz wcześniejszy punkt dotyczący obliczeń ma zastosowanie również tutaj. bonus + 100 daje NULL dla każdego bez premii, więc napisz COALESCE(bonus, 0) + 100, jeśli traktowanie brakującego bonusu jako zera jest prawidłowe dla Twojej firmy.

NULLIF: zmień wartość na NULL

NULLIF(a, b) zwraca NULL, jeśli dwie wartości są równe, w przeciwnym razie zwraca a. Szczególnie przydatne są dwa zastosowania. Może wyczyścić wartości zastępcze:

SELECT name, NULLIF(phone, '') FROM employees WHERE id = 3;
-- Sara | NULL

SELECT name FROM employees
WHERE COALESCE(NULLIF(phone, ''), '') = '';
-- Arjun
-- Sara
-- Ravi

To drugie zapytanie to wygodny sposób na znalezienie wartości „brakujących lub pustych”. Może także ustrzec się przed dzieleniem przez zero, co powoduje błąd w wielu bazach danych:

SELECT total / NULLIF(quantity, 0) FROM sales;

Gdy quantity wynosi 0, daje to NULL zamiast kończyć się niepowodzeniem. W naszym teście SELECT 100 / NULLIF(0, 0) zwróciło NULL.

Agregaty pomijają wartość NULL

W tym miejscu wartość NULL powoduje największe szkody w raportach:

SELECT COUNT(*), COUNT(bonus), SUM(bonus), AVG(bonus),
       AVG(COALESCE(bonus, 0))
FROM employees;

-- 4 | 2 | 800 | 400.0 | 200.0

Jaka jest więc średnia premia, 400 czy 200? To zależy od tego, co oznacza NULL w twoich danych. Jeśli NULL oznacza „nie wypłacono żadnej premii”, to 200 jest prawidłowe. Jeśli oznacza to „jeszcze nie zdecydowano”, to 400 może mieć rację. Baza danych nie może podjąć tej decyzji za Ciebie, dlatego pytanie należy skierować do kogoś, kto zna się na branży.

Powiązana pułapka: SUM braku wartości to NULL, a nie 0. Gdy zsumowaliśmy tylko wiersze bez premii, otrzymaliśmy NULL, podczas gdy COUNT zwróciło 0. Kod aplikacji oczekujący liczby może się zawiesić, więc zawiń go: COALESCE(SUM(bonus), 0).

GROUP BY, sortowanie i DISTINCT

Chociaż wartości NULL są „nierówne” w porównaniach, operacje te traktują je jako jedną grupę:

SELECT bonus, COUNT(*) FROM employees GROUP BY bonus ORDER BY bonus;
-- NULL | 2
--  300 | 1
--  500 | 1

SELECT DISTINCT bonus FROM employees;
-- 500, 300, NULL

Zobacz SQL GROUP BY i HAVING, aby uzyskać więcej informacji na temat grupowania. Porządek sortowania to miejsce, w którym bazy danych naprawdę się różnią. SQLite i MySQL traktują NULL jako najmniejszą wartość, podczas gdy PostgreSQL i Oracle traktują ją jako największą. Zatem to samo ORDER BY bonus DESC może umieścić NULL na końcu w jednym i na pierwszym miejscu w drugim. PostgreSQL, Oracle i nowoczesny SQLite pozwalają to określić bezpośrednio, używając NULLS FIRST lub NULLS LAST.

Dołącza

Warunek złączenia przy użyciu = nigdy nie odpowiada NULL, więc wiersze z kluczem NULL wypadają z wewnętrznego złączenia:

SELECT e.name FROM employees e
JOIN employees m ON m.id = e.manager_id;
-- Arjun, Sara, Ravi    (Meera is gone)

SELECT e.name, m.name FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
-- Meera | NULL
-- Arjun | Meera
-- Sara  | Meera
-- Ravi  | Arjun

Meera nie ma menedżera, więc złączenie wewnętrzne po cichu usuwa ten wiersz. A LEFT JOIN zachowuje to, a także tworzy własne wartości NULL dla brakującej strony. Przeczytaj więcej w Wyjaśnienie złączeń SQL.

Pułapka NIE W

Pojedynczy NULL na liście NOT IN usuwa każdy wynik:

SELECT name FROM employees WHERE manager_id NOT IN (1);
-- Ravi

SELECT name FROM employees WHERE manager_id NOT IN (1, NULL);
-- (no rows)

Druga forma oznacza manager_id <> 1 AND manager_id <> NULL i ta ostatnia część nigdy nie może być prawdziwa. Jest to szczególnie niebezpieczne w przypadku podzapytań, gdzie wartość NULL może pojawić się niezauważona. Zamiast tego użyj NOT EXISTS, jak wyjaśniono w naszym samouczku dotyczącym podzapytań SQL. W pierwszym wyniku brakuje również Meery, ponieważ manager_id w tym wierszu ma wartość NULL.

Projektowanie z NULL

• Domyślnie użyj NOT NULL. Zezwól na wartość NULL tylko wtedy, gdy rzeczywiście może brakować wartości.
• Dodaj rozsądne wartości domyślne. Na przykład quantity INT NOT NULL DEFAULT 0.
• Nie używaj wartości magicznych. Data 1900-01-01 lub wiek -1 zastępujący „nieznany” zafałszuje Twoje obliczenia.
• Poznaj zachowanie UNIQUE. Większość baz danych dopuszcza kilka wartości NULL w kolumnie UNIQUE, a nasz test wstawił dwie wartości bez błędu. SQL Server jest wyjątkiem, ponieważ zwykle na to pozwala.
• Pamiętaj, że klucz podstawowy nie może mieć wartości NULL. Zobacz klucz podstawowy a klucz obcy.
• Testuj z wartościami NULL. Dane testowe bez wartości NULL ukrywają te błędy. Uwzględnij je celowo, tak jak w innych przypadkach Edge.

Podsumowanie

Traktuj wartość NULL jako „nieznaną”, a większość jej zachowań zacznie mieć sens: nieznane plus jeden jest nieznane, a nieznane równa się nieznane i nie wiadomo, czy jest prawdziwe. W tym tygodniu przejrzyj jeden ze swoich raportów. Sprawdź kolumny dopuszczające wartość null, używając COUNT(*) i COUNT(column), poszukaj dowolnego NOT IN i zdecyduj, co NULL powinno oznaczać w każdej średniej.

Najczęstsze pytania

Ucz się dalej