COALESCE w SQL: NULL, wartości domyślne i pułapki średniej

Puste pole w raporcie wygląda jak zaproszenie do wpisania zera. Zanim to zrobię, sprawdzam, co oznacza brak. Zero punktów może być prawidłowym wynikiem, a brak wyniku może oznaczać, że pomiaru jeszcze nie wykonano. Po zamianie obu sytuacji na zero trudno je później odróżnić.

COALESCE pozwala wybrać wartość zastępczą albo pierwsze dostępne pole. Sama składnia jest krótka. Więcej uwagi wymaga decyzja, kiedy takie zastępstwo ma sens. Pokażę Ci wybór nazwy, porządkowanie pustego tekstu i dwa zapisy średniej, które wyglądają podobnie, a odpowiadają na inne pytania.

Wszystkie bloki SQL i tabele wyników poniżej sprawdziłem w SQLite 3.53.1 przez node:sqlite w Node 24.18.0. Wykonuj je kolejno w jednym pustym połączeniu testowym. Uwagi o PostgreSQL, SQL Server i MySQL pochodzą z dokumentacji tych silników; nie są deklaracją wykonania na nich tych przykładów.

Co wybiera COALESCE

COALESCE(a, b, c) zwraca pierwszy argument różny od NULL, w kolejności od lewej. Jeśli wszystkie mają NULL, wynik też jest NULL. SQLite wymaga co najmniej dwóch argumentów. Dokumentacja funkcji SQLite opisuje tę regułę; nie wynika z niej, że baza ocenia jakość znalezionego tekstu.

Najpierw mały test bez tabeli:

-- example: basics
SELECT COALESCE(NULL, 'A', 'B') AS chosen,
       COALESCE(NULL, NULL) AS missing,
       COALESCE(0, 99) AS zero_value,
       LENGTH(COALESCE('', 'fallback')) AS text_length;

W pierwszej kolumnie wygrywa A, chociaż po prawej istnieje jeszcze B. W drugiej nie ma czego wybrać. W trzeciej zero zatrzymuje wybór. Ostatnia kolumna mierzy długość wybranego tekstu: wynik zero oznacza, że pozostał pusty napis, a słowo fallback nie zostało użyte.

To wybór jednej wartości w obrębie bieżącego wiersza. Funkcja nie skleja imion, nie szuka pierwszego pasującego rekordu w całej tabeli i nie wybiera najnowszego adresu. Jeśli potrzebujesz pełnego imienia i nazwiska, sam wybór jednego z tych pól nie zbuduje pełnej nazwy. Kolejność argumentów jest częścią reguły raportu.

Dane, na których widać braki

Użyję pięciu syntetycznych profili. preferred_name to nazwa preferowana, fallback_name to nazwa zapasowa. score oznacza wynik pomiaru w punktach, a adjustment korektę punktową. Nazwy i liczby są wymyślone. Nie trzeba pobierać żadnego pliku ani podawać prawdziwych danych.

-- example: fixture
CREATE TEMP TABLE profiles (
  id INTEGER PRIMARY KEY,
  preferred_name TEXT,
  fallback_name TEXT,
  score INTEGER,
  adjustment INTEGER
);
INSERT INTO profiles VALUES
  (1, 'Ada', 'A', 10, 2),
  (2, NULL, 'Bea', NULL, 3),
  (3, '', 'Cara', 0, NULL),
  (4, '   ', 'Dan', 20, 0),
  (5, NULL, NULL, NULL, NULL);

Warto zatrzymać się przy trzecim i czwartym wierszu. Cara ma wynik zero i pustą nazwę preferowaną. Dan ma wynik dwadzieścia, ale nazwa preferowana zawiera trzy zwykłe spacje. Piąty profil nie ma ani nazwy, ani pomiaru. Te różnice będą widoczne w kolejnych wynikach.

Tabele są tymczasowe i służą do ćwiczenia w tym połączeniu. Dalsze zapytania tylko odczytują dane. Zastosowanie COALESCE w SELECT nie zapisuje etykiety do kolumny i nie usuwa oryginalnych braków. To przydatne: można poprawić sposób prezentacji, zachowując materiał do kontroli jakości.

Nazwa preferowana, zapasowa i pusty tekst

Zacznę od prostego priorytetu: nazwa preferowana, potem zapasowa, na końcu etykieta Anonymous. Używam tej samej etykiety w obu wersjach językowych ćwiczenia. Funkcja SQLite QUOTE pokazuje tekst jako literał SQL, dzięki czemu w tabeli widać pusty napis i spacje.

-- example: priority
SELECT id,
       QUOTE(COALESCE(preferred_name, fallback_name, 'Anonymous'))
         AS label_literal
FROM profiles
ORDER BY id;

Ada zachowuje nazwę preferowaną. Bea korzysta z zapasowej. Cara i Dan nadal wyglądają na brakujące nazwy, choć ich pola nie mają NULL. Samo dodanie kolejnych argumentów do COALESCE tego nie naprawi: pierwszy argument już zawiera wartość.

Dla tego raportu przyjmuję jawną regułę: nazwa pusta albo złożona wyłącznie ze zwykłych spacji jest brakiem. Najpierw usuwam spacje z końców przez TRIM, potem NULLIF(..., '') zamienia pusty wynik na NULL. Dopiero wtedy wybieram nazwę:

-- example: clean
SELECT id,
       COALESCE(NULLIF(TRIM(preferred_name), ''),
                NULLIF(TRIM(fallback_name), ''),
                'Anonymous') AS display_name
FROM profiles
ORDER BY id;

Teraz wyniki odpowiadają tej regule. Domyślny TRIM w SQLite usuwa zwykłą spację z końców; nie jest uniwersalnym czyszczeniem tabulatorów, nowych linii i wszystkich znaków Unicode. Semantykę TRIM, NULLIF i QUOTE znajdziesz w dokumentacji SQLite.

Nie usuwałbym automatycznie każdego białego znaku w dowolnej kolumnie. Nazwa wyświetlana, hasło i identyfikator pliku mogą mieć inne zasady. Najpierw ustalam znaczenie pola, a potem wybieram przekształcenie. Inaczej łatwo otrzymać ładniejszą tabelę, która ukrywa problem importu.

Kiedy przydaje się CASE

Tę samą kolejność wyboru można zapisać dłużej. Poniżej porównuję wynik CASE z prostym COALESCE sprzed czyszczenia tekstu. Jedynka w SQLite oznacza prawdziwe porównanie.

-- example: cases
SELECT id,
       CASE
         WHEN preferred_name IS NOT NULL THEN preferred_name
         WHEN fallback_name IS NOT NULL THEN fallback_name
         ELSE 'Anonymous'
       END = COALESCE(preferred_name, fallback_name, 'Anonymous')
         AS same_result
FROM profiles
ORDER BY id;

Wszystkie wyniki są zgodne, także dla pustego tekstu i spacji. Warunki w CASE sprawdzają dokładnie to, czego potrzebujemy: czy dana wartość jest różna od NULL. Zapis z warunkiem niezwiązanym z wybieranym polem nie byłby równoważnym porównaniem.

Ja używam COALESCE, gdy chodzi o samą dostępność wartości. Po CASE sięgam wtedy, gdy wybór zależy od innego warunku, na przykład statusu weryfikacji profilu. Jeśli adres istnieje, ale nie wolno go użyć w danym raporcie, jego obecność nie wystarcza do podjęcia decyzji. Składnię warunków rozwinąłem w haśle o CASE WHEN w SQL.

W którym miejscu zastąpić brak w obliczeniu

Załóżmy wyłącznie na potrzeby ćwiczenia, że brak korekty oznacza korektę zerową, natomiast brak pomiaru nadal oznacza nieznany wynik. Porównaj trzy obliczenia:

-- example: arithmetic
SELECT id,
       score + adjustment AS raw_total,
       score + COALESCE(adjustment, 0) AS adjusted_total,
       COALESCE(score + adjustment, 0) AS defaulted_total
FROM profiles
ORDER BY id;

Dla Cary surowa suma jest nieznana, bo korekta ma NULL. Po zastąpieniu samej korekty zerem dostajemy prawidłowe zero. Dla Bei wynik nadal pozostaje nieznany: znamy korektę trzy, ale nie znamy punktów wyjściowych.

Ostatnia kolumna zastępuje zerem całe nieznane wyrażenie. W ten sposób Bea i profil bez żadnego pomiaru dostają taki sam wynik jak Cara. Oba zapisy są poprawne składniowo. Tylko środkowy realizuje przyjęte założenie, że wolno uzupełnić korektę, a samego pomiaru nie wolno zgadywać.

Nie traktuję wartości domyślnej jako naprawy każdej operacji. COALESCE reaguje na wynik NULL; nie jest ogólnym mechanizmem przechwytywania błędów. Jeśli problemem jest nieprawidłowy typ, trzeba poprawić typ albo walidację wejścia. W raportach zachowuję też osobną informację, które wartości były brakujące.

Dlaczego średnia może zmienić się z dziesięciu na sześć

W danych mamy trzy znane pomiary: dziesięć, zero i dwadzieścia. Ich suma to trzydzieści. Średnia z wykonanych pomiarów wynosi dziesięć. Jeśli dwa brakujące pomiary zastąpimy zerami, ten sam licznik podzielimy przez pięć i dostaniemy sześć.

-- example: averages
SELECT AVG(score) AS observed_mean,
       AVG(COALESCE(score, 0)) AS imputed_mean,
       COALESCE(AVG(score), 0) AS displayed_mean,
       COUNT(score) AS measured_rows,
       COUNT(*) AS all_rows
FROM profiles;

AVG(COALESCE(score, 0)) zmienia dane wejściowe agregacji. COALESCE(AVG(score), 0) zmienia dopiero jej wynik, gdy jest NULL. W tym zestawie zwykła średnia istnieje, więc zewnętrzne COALESCE pozostawia dziesięć. Zasady pomijania braków przez AVG opisuje dokumentacja agregatów SQLite.

Żeby zobaczyć drugą sytuację, ograniczę wejście do dwóch profili bez pomiaru:

-- example: allnull
SELECT AVG(score) AS observed_mean,
       AVG(COALESCE(score, 0)) AS imputed_mean,
       COALESCE(AVG(score), 0) AS displayed_mean,
       COUNT(score) AS measured_rows,
       COUNT(*) AS all_rows
FROM profiles
WHERE id IN (2, 5);

Wyświetlone zero w trzeciej kolumnie nie jest dowodem wykonania pomiaru. Dlatego obok średniej pokazałem licznik znanych wartości i licznik wszystkich wierszy. Gdy raport ma służyć do oceny jakości danych, te dwie liczby bywają ważniejsze od wygodnej etykiety. Więcej o ich znaczeniu znajdziesz w haśle COUNT w SQL.

Jeszcze inny przypadek to brak jakichkolwiek wierszy wejściowych:

-- example: empty
SELECT COALESCE(SUM(score), 0) AS displayed_sum,
       COALESCE(AVG(score), 0) AS displayed_mean,
       AVG(COALESCE(score, 0)) AS imputed_mean
FROM profiles
WHERE id < 0;

Wewnętrzne zastąpienie nie ma tu czego przetworzyć. Zewnętrzne może zamienić NULL zwrócony przez agregat na zero. Nie oznacza to jednak, że funkcja dopisuje brakujące grupy:

-- example: groups
SELECT preferred_name,
       COALESCE(SUM(score), 0) AS displayed_sum
FROM profiles
WHERE id < 0
GROUP BY preferred_name;

Ostatnia tabela ma nagłówki i zero wierszy. Żaden profil nie spełnia filtra, więc nie powstaje grupa. Raport wymagający każdego miesiąca, także bez zdarzeń, potrzebuje listy miesięcy i odpowiedniego złączenia. Samo COALESCE nie utworzy tej listy.

COALESCE, ISNULL i typy w różnych silnikach

Nie przenoszę nazwy ISNULL między bazami bez sprawdzenia znaczenia. W SQL Server przyjmuje dwa argumenty i zwraca jedną wartość, zastępując brak pierwszego argumentu drugim. W MySQL przyjmuje jeden argument i zwraca informację o braku: jeden albo zero. SQLite ma dwargumentowe IFNULL, opisane w dokumentacji funkcji.

Różnice obejmują również typ wyniku. PostgreSQL wymaga wspólnego typu, do którego da się przekonwertować argumenty. SQL Server COALESCE stosuje pierwszeństwo typów, a ISNULL zwykle opiera typ na pierwszym argumencie, z wyjątkiem literalnego NULL. Zastępnik tekstowy może przy tym zostać przycięty.

Dlatego liczbę zostawiam liczbą, a opis braku umieszczam w osobnym polu. Nie zakładam, że połączenie liczby i tekstu 'Unknown' w jednym wyrażeniu zachowa się tak samo w każdej bazie.

Microsoft opisuje też możliwość wielokrotnego obliczenia argumentu zawierającego podzapytanie w COALESCE. Przy zwykłych kolumnach z tego ćwiczenia problem się nie pojawia. Dla kosztownych lub zmiennych wyrażeń trzeba sprawdzić reguły konkretnego silnika. Nie obiecuję przyspieszenia zapytania po zamianie jednej funkcji na drugą.

Ćwiczenie: pokaż nazwę i pochodzenie wartości

Przygotuj wynik z nazwą oczyszczoną według wcześniejszej reguły. Dodaj pole, które mówi, czy pochodzi ona z nazwy preferowanej, zapasowej, czy z etykiety dla braku. Osobno oznacz brak pomiaru. Zero punktów powinno pozostać wykonanym pomiarem.

Najpierw spróbuj samodzielnie. Potem porównaj rozwiązanie:

-- example: exercise
WITH cleaned AS (
  SELECT id, score,
         NULLIF(TRIM(preferred_name), '') AS preferred,
         NULLIF(TRIM(fallback_name), '') AS fallback
  FROM profiles
)
SELECT id,
       COALESCE(preferred, fallback, 'Anonymous') AS display_name,
       CASE
         WHEN preferred IS NOT NULL THEN 'preferred'
         WHEN fallback IS NOT NULL THEN 'fallback'
         ELSE 'missing'
       END AS name_source,
       CASE WHEN score IS NULL THEN 1 ELSE 0 END AS score_missing
FROM cleaned
ORDER BY id;

Wspólne wyrażenie tabelaryczne cleaned nadaje nazwy oczyszczonym polom, żeby nie powtarzać reguły w kilku miejscach. COALESCE wybiera etykietę, a CASE opisuje jej źródło. Dzięki temu profil numer pięć nie staje się pozornie kompletnym rekordem tylko dlatego, że raport umie go wyświetlić.

Na koniec zmień w testowym zestawie nazwę preferowaną Bei na poprawny tekst. Powinna wygrać z zapasową, a źródło powinno zmienić się na preferred. Potem ustaw wszystkie pomiary na NULL i sprawdź liczniki. Taki mały test szybko pokazuje, czy wartość domyślna realizuje ustaloną regułę i czy nadal widzisz to, czego w danych brakuje.