Obsługa NULL w obliczeniach z COALESCE()

Dziś znowu zagłębimy się w temat obsługi NULL i poznamy bardzo przydatną funkcję — COALESCE(). Ta funkcja pozwala elegancko radzić sobie z wartościami NULL w twoich danych.

Spójrzmy na taki przykład: masz tabelę z danymi o pracownikach, ale niektórzy z nich nie mają podanej pensji. Co się stanie, jeśli spróbujemy podnieść wszystkim pensje? Nic dobrego. Nie da się wykonywać operacji na NULL. A co jeśli chcemy zamienić NULL pensje na, powiedzmy, 0? Tu na scenę wchodzi COALESCE().

COALESCE() — to funkcja, która zwraca pierwszą nie-NULL wartość z przekazanej listy argumentów. Jeśli wszystkie wartości na liście to NULL, funkcja zwraca NULL. Mówiąc prosto, ona mówi: "Daj mi pierwszą sensowną wartość, jaką znajdziesz, proszę!"

Składnia

COALESCE(value1, value2, ..., value_n)

value1, value2, ..., value_n — to argumenty, które przekazujesz do funkcji. Zwróci ona pierwszą wartość, która nie jest NULL.

Przykłady działania COALESCE()

Pokażę kilka przykładów.

Przykład 1: Zamiana NULL na 0

Załóżmy, że mamy tabelę salaries:

Chcemy policzyć sumę wszystkich pensji - to proste:

SELECT SUM(salary) AS total_salary
FROM salaries;

Funkcja SUM() ignoruje NULL, więc nie ma problemu.

Ale potem chcemy policzyć sumę pensji, jeśli każdemu damy premię 1000.

SELECT SUM(salary+1000) AS total_salary
FROM salaries;

I wynik zaczyna się rozjeżdżać. Najlepiej od razu pozbyć się wartości NULL za pomocą funkcji COALESCE i zamienić je na 0. Zobaczmy jak to zrobić:

SELECT SUM(COALESCE(salary, 0)) AS total_salary
FROM salaries;

Wynik:

Tak jest lepiej i pewniej.

Przykład 2: Zamiana NULL na wartość domyślną

Załóżmy, że mamy tabelę students z imionami i adresami:

Chcemy zamienić NULL w adresach na "Nie podano":

SELECT name, COALESCE(address, 'Nie podano') AS resolved_address
FROM students;

Wynik zapytania:

Przykład 3: Użycie wielu wartości

Czasem trzeba zamienić NULL nie na jedną wartość, ale na całą sekwencję wartości. Na przykład chcemy wybrać imię, imię dla znajomych albo użyć "Bez imienia", jeśli żadne nie jest podane. Tabela users:

Zapytanie:

SELECT user_id,
       COALESCE(first_name, short_name, 'Bez imienia') AS display_name
FROM users;

Wynik:

Praktyczne zastosowanie COALESCE()

W praktyce COALESCE() — to koło ratunkowe do pracy z nieidealnymi danymi.

Zobaczmy, jak pomaga w różnych zadaniach.

Przykład 1: Zamiana wartości w polach tekstowych

Wyjściowa tabela customers:

Zapytanie:

SELECT name, COALESCE(address, 'Nie podano') AS address
FROM customers;

Wynik:

Przykład 2: Przygotowanie danych do raportów

Wyjściowa tabela sales:

Zapytanie:

SELECT SUM(COALESCE(price, 0)) AS total_sales
FROM sales;

Wynik:

Typowe błędy przy użyciu COALESCE()

Chociaż COALESCE() wydaje się prostą i uniwersalną funkcją, można się na niej przejechać na kilka sposobów.

Niezgodność typów danych. Wszystkie argumenty przekazywane do COALESCE() muszą być zgodne typem danych. Na przykład nie można mieszać wartości tekstowych i liczbowych.

-- Błąd
SELECT COALESCE(salary, 'Nie podano') FROM employees; 
-- salary — pole liczbowe, a 'Nie podano' — tekst.

Ignorowanie kolejności argumentów. COALESCE() zwraca pierwszą nie-NULL wartość, więc kolejność argumentów ma znaczenie.