Zrozumienie funkcji COALESCE () w języku SQL

Wraz z ciągłym postępem technologicznym, kluczowe jest, aby programiści na bieżąco śledzili najnowsze trendy. Niezależnie od poziomu zaawansowania, solidne zrozumienie operacji na łańcuchach znaków jest niezbędne do efektywnego przygotowywania i zarządzania danymi. Umożliwia to tworzenie niestandardowych formularzy i efektywne wykorzystanie wbudowanych funkcji serwera SQL.

Poza modyfikacją danych, istotna jest również analiza zbiorów danych, ocena wartości oraz kodowanie i dekodowanie informacji, co prowadzi do uzyskania bardziej wartościowych danych. Pomaga to w radzeniu sobie z brakującymi wartościami, zrozumieniu ich wpływu na obliczenia oraz usprawnieniu procesów przetwarzania danych, aby unikać wartości NULL, które mogą zakłócić wyniki operacji.

Ten artykuł przedstawi funkcję COALESCE w SQL, która jest istotna przy budowaniu zaawansowanych programów. Zakładamy, że masz już pewne doświadczenie z SQL i chcesz zgłębić wiedzę na temat tej konkretnej funkcji. Nasza seria przewodników SQL ma na celu szybkie wprowadzenie w tematykę.

Czym jest COALESCE() w SQL i jakie ma zastosowania?

Funkcja COALESCE w SQL analizuje parametry (argumenty) w określonej kolejności, na przykład listę, i zwraca pierwszą wartość, która nie jest NULL. W uproszczeniu, funkcja sekwencyjnie przegląda listę i kończy działanie po znalezieniu pierwszej wartości innej niż NULL. Jeżeli wszystkie argumenty na liście są NULL, funkcja zwraca wartość NULL.

Co więcej, funkcja ta jest obsługiwana w różnych bazach danych, takich jak MySQL, Azure SQL Database, Oracle i PostgreSQL.

Funkcję COALESCE można stosować w sytuacjach, gdy:

• Konieczna jest obsługa wartości NULL.
• Chcesz połączyć kilka zapytań w jedno.
• Potrzebujesz uniknąć długich i skomplikowanych instrukcji CASE.

W porównaniu do instrukcji CASE (lub funkcji ISNULL), funkcja COALESCE przyjmuje wiele parametrów, podczas gdy instrukcja CASE wymaga tylko dwóch. To pozwala na pisanie bardziej zwięzłego kodu.

Oto składnia:

COALESCE(wartośćJeden, wartośćDwa, wartośćTrzy, …, wartośćX);

Funkcja łączenia w SQL Server ma kilka cech, w tym akceptowanie argumentów tego samego typu danych, obsługę wielu parametrów oraz argumenty typu całkowitego, które funkcja może kaskadowo przetwarzać, aby zwrócić liczbę całkowitą jako wynik.

Przeczytaj także: Niezbędnik SQL – ściągawka do wykorzystania w przyszłości

Zanim przejdziemy do zastosowania funkcji COALESCE, omówmy pojęcie NULL.

Co to jest wartość NULL w SQL?

W SQL unikalny znacznik NULL informuje o braku wartości w bazie danych. Można go traktować jako wartość niezdefiniowaną lub nieznaną. Nie należy mylić jej z pustym łańcuchem znaków czy zerem; NULL oznacza brak wartości. Występowanie wartości NULL w kolumnach tabeli oznacza brakujące informacje.

Przykładowo, w kolumnie danych bazy danych sklepu internetowego, pole może zawierać wartość NULL, jeżeli klient nie poda swojego identyfikatora. W SQL NULL jest unikalne i reprezentuje stan, w odróżnieniu od innych języków programowania, gdzie może oznaczać „wskazywanie na nic”.

Wartości NULL w SQL mają istotny wpływ na relacyjne bazy danych. Po pierwsze, pozwalają na wykluczenie określonych wartości podczas korzystania z innych funkcji wewnętrznych. Przykładowo, można wygenerować listę wszystkich zamówień w trakcie realizacji, ale niektóre z nich mogą być jeszcze niekompletne. Użycie NULL jako symbolu zastępczego pozwala funkcji SUMA poprawnie zsumować wartości.

Rozważmy sytuację, gdzie musisz obliczyć średnią za pomocą funkcji AVG. Praca z zerowymi wartościami spowoduje zniekształcenie wyników. Zamiast tego baza danych może pomijać takie pola i wykorzystywać NULL, co daje dokładniejsze rezultaty.

Wartości NULL mają pewne wady. Są traktowane jako wartości o zmiennej długości, zajmujące bajty lub kilka bajtów. Baza danych musi rezerwować miejsce na te bajty, co w przypadku przekroczenia standardowej wielkości przechowywanej w bazie, może prowadzić do zwiększenia zużycia miejsca na dysku.

Ponadto, podczas pracy z niektórymi funkcjami, konieczne jest ich dostosowanie, aby prawidłowo obsługiwać NULL. To z kolei wydłuża procedury SQL.

Obsługa wartości NULL za pomocą COALESCE()

Wartości NULL oznaczają, że potencjalnie istnieje wartość, ale nie jest znana. Do momentu uzyskania danych, które wypełnią pola rzeczywistymi wartościami, wartości NULL pełnią rolę symbolu zastępczego.

Chociaż wartości NULL mogą być używane w bazie danych dla różnych typów danych, w tym liczb dziesiętnych, łańcuchów, obiektów blob i liczb całkowitych, zaleca się unikanie ich w przypadku danych liczbowych.

Głównym problemem jest to, że podczas pracy z wartościami liczbowymi, często potrzebne jest dodatkowe wyjaśnienie przy tworzeniu kodu, który operuje na tych danych. Więcej na ten temat w dalszej części.

Istnieje kilka sposobów wykorzystania COALESCE() do obsługi wartości NULL:

Użycie COALESCE() do zastąpienia wartości NULL określoną wartością

COALESCE() pozwala na zwrócenie konkretnej wartości w miejsce wartości NULL. Na przykład, w tabeli „pracownicy” z kolumną „wynagrodzenie”, która może zawierać wartości NULL, gdy wynagrodzenie pracownika nie zostało wprowadzone, można chcieć użyć wartości 0 dla wszystkich wpisów NULL. Oto jak to zrobić:

SELECT COALESCE(wynagrodzenie, 0) AS wynagrodzenie_skorygowane
FROM pracownicy;

Użycie COALESCE() do wybrania pierwszej wartości innej niż NULL z wielu opcji

Czasami zachodzi potrzeba pracy z pierwszymi wartościami innymi niż NULL na liście wyrażeń. W takich przypadkach często masz wiele kolumn z powiązanymi danymi i chcesz nadać priorytet wartościom innym niż NULL. Składnia pozostaje taka sama.

COALESCE(wyrażenie1, wyrażenie2, …)

W praktycznym przykładzie załóżmy, że masz tabelę kontaktów z kolumnami preferowana_nazwa i pełna_nazwa. Chcesz wygenerować listę kontaktów wraz z ich preferowanymi nazwami (jeśli są dostępne), lub pełnymi nazwami, jeśli preferowana nazwa nie jest dostępna. Oto jak to zrobić.

SELECT COALESCE(preferowana_nazwa, pełna_nazwa) AS nazwa_wyswietlana
FROM kontakty.

Jeśli preferowana nazwa nie jest NULL, zostanie zwrócona. W przeciwnym razie jako nazwa wyświetlana zostanie użyta pełna nazwa.

Konkatenacja łańcuchów z COALESCE w SQL

Podczas łączenia łańcuchów znaków w SQL mogą wystąpić problemy, jeżeli w operacji biorą udział wartości NULL. W takim przypadku jako niepożądany wynik otrzymujemy NULL. Aby tego uniknąć, można użyć funkcji COALESCE. Poniżej przykład.

Prosta konkatenacja łańcuchów znaków wygląda tak:

SELECT 'Witaj, gdzie jesteś, '|| 'Jan '||'?' AS przykład

Kod zwraca:

PrzykładWitaj, gdzie jesteś, Jan?

Jednak w przypadku użycia wartości NULL, jak pokazano poniżej:

SELECT 'Witaj, gdzie jesteś, ' || null || '?' AS przykład

Wynik to:

Ponieważ każda konkatenacja zawierająca wartość NULL zwraca NULL, powyższy wynik to NULL. Ten problem można rozwiązać za pomocą funkcji COALESCE(). Dzięki niej zwracasz pusty ciąg znaków (lub spację) zamiast NULL. Załóżmy, że tworzysz listę nazw samochodów wraz z ich producentami; oto zapytanie:

SELECT 
samochód || ', producent: ' || COALESCE(producent, '—') AS marka_samochodu
FROM zapasy

Jeżeli producent ma wartość NULL, zamiast niej pojawi się znak „—”. Oto oczekiwane wyniki:

marka_samochodusamochód1, producent: —samochód2, producent: Bentley samochód3, producent: —samochód4, producent: Crown

Jak widać, wyniki NULL są eliminowane, a w ich miejsce wstawiana jest zastępcza wartość tekstowa.

Funkcja COALESCE w SQL i przestawianie

Przestawianie SQL to technika służąca do transformacji wierszy w kolumny. Umożliwia transpozycję danych z formy „znormalizowanej” (z wieloma wierszami i mniejszą liczbą kolumn) do formy „odnormalizowanej” (z mniejszą liczbą wierszy i większą liczbą kolumn). Funkcję COALESCE można użyć w połączeniu z przestawianiem SQL w celu obsługi wartości NULL w przestawionych wynikach.

Podczas operacji PIVOT, wiersze są przekształcane w kolumny, a kolumny wynikowe są funkcjami agregującymi dane. Jeżeli w konkretnym przypadku agregacja da w efekcie wartość NULL dla danej komórki, można zastosować funkcję `COALESCE`, aby zastąpić wartości NULL wartością domyślną lub odpowiednią reprezentacją. Poniżej przykład.

Rozważ tabelę „sprzedaż” z kolumnami rok, kwartał i przychód. Chcesz przestawić dane tak, aby lata były kolumnami, a sumy przychodów dla każdego kwartału wartościami. Jednak niektóre kwartały mogą nie zawierać danych o przychodach, co da wartości NULL w wyniku przestawiania. W takim przypadku można użyć funkcji COALESCE, aby zastąpić wartości NULL w wynikowej tabeli wartością zero (0).

SELECT
    rok,
    COALESCE(SUM(CASE WHEN kwartał="Q1" THEN przychód END), 0) AS Przychód_Q1,
    COALESCE(SUM(CASE WHEN kwartał="Q2" THEN przychód END), 0) AS Przychód_Q2,
    COALESCE(SUM(CASE WHEN kwartał="Q3" THEN przychód END), 0) AS Przychód_Q3,
    COALESCE(SUM(CASE WHEN kwartał="Q4" THEN przychód END), 0) AS Przychód_Q4
FROM sprzedaż
GROUP BY rok;

Skalarna funkcja zdefiniowana przez użytkownika i funkcja COALESCE w SQL

Możesz wykorzystać skalarne UDF i funkcję COALESCE do wykonywania złożonej logiki, która obsługuje wartości NULL. Połączenie tych funkcji pozwala na uzyskanie bardziej zaawansowanych transformacji danych i obliczeń w zapytaniach SQL. Rozważmy tabelę „Pracownicy” o następującej strukturze:

CREATE TABLE Pracownicy (
    IdPracownika INT PRIMARY KEY,
    Imie VARCHAR(50),
    Nazwisko VARCHAR(50),
    Wynagrodzenie INT,
    Premia INT
);

Możesz chcieć obliczyć całkowite zarobki każdego pracownika (wynagrodzenie plus premia). Jednak niektóre wartości mogą być brakujące. W takiej sytuacji skalarna UDF może zająć się dodawaniem wynagrodzenia i premii, podczas gdy funkcja COALESCE poradzi sobie z wartościami NULL. Oto skalarna UDF obliczająca łączne zarobki:

CREATE FUNCTION dbo.ObliczCalkowiteZarobki (@wynagrodzenie INT, @premia INT)
RETURNS INT
AS
BEGIN
    DECLARE @calkowiteZarobki INT;
    SET @calkowiteZarobki = @wynagrodzenie + COALESCE(@premia, 0);
    RETURN @calkowiteZarobki;
END;
Możesz następnie użyć skalarnej UDF z funkcją COALESCE w zapytaniu:
SELECT IdPracownika, Imie, Nazwisko,
       Wynagrodzenie, Premia, dbo.ObliczCalkowiteZarobki(Wynagrodzenie, Premia) AS CalkowiteZarobki
FROM Pracownicy;

Walidacja danych za pomocą SQL COALESCE

Podczas pracy z bazami danych często zachodzi potrzeba sprawdzenia poprawności wartości liczbowych. Załóżmy, że masz kolumny nazwa_produktu, cena i rabat w tabeli „produkty”. Chcesz pobrać nazwy produktów, ceny i rabaty dla każdego elementu, przy czym wszystkie wartości rabatu NULL mają być traktowane jako 0. Funkcja COALESCE będzie w tym pomocna. Oto przykład:

SELECT nazwa_produktu, cena, COALESCE(rabat, 0) AS rabat 
FROM produkty

COALESCE w SQL i kolumny obliczeniowe

Kolumny obliczeniowe to wirtualne kolumny, których wartości są obliczane na podstawie wyrażeń lub innych kolumn w tabeli. Ponieważ kolumny obliczeniowe nie są fizycznie przechowywane w bazie danych, można je wykorzystać z funkcją COALESCE w złożonych scenariuszach i transformacjach. Poniżej praktyczny przykład.

Rozważ tabelę „produkty” z kolumnami „cena”, „rabat” i „stawka_podatku”. Chcesz utworzyć kolumnę obliczeniową „cena_calkowita”, która przedstawi ostateczną cenę produktu po zastosowaniu rabatu i podatku. Jeżeli rabat lub podatek nie są określone (NULL), obliczenia powinny być wykonane z użyciem zera. Oto jak wykorzystać funkcję COALESCE:

CREATE TABLE produkty(
cena DECIMAL(10, 2),
rabat DECIMAL(10, 2),
stawka_podatku DECIMAL(5, 2),
cena_calkowita AS (COALESCE(cena, 0) – COALESCE(cena*rabat, 0))* COALESCE(1+stawka_podatku, 1)
);

W powyższym kodzie:

Kolumna obliczeniowa cena_calkowita jest definiowana jako (COALESCE(cena, 0) – COALESCE(cena*rabat, 0))* COALESCE(1+stawka_podatku, 1).

Jeżeli cena ma wartość NULL, COALESCE(cena*rabat, 0) zapewnia, że jest traktowana jako 0.

Jeżeli rabat ma wartość NULL, funkcja COALESCE(cena*rabat) zapewnia, że jest on traktowany jako 0, a mnożenie nie wpływa na obliczenia.

Jeżeli stawka_podatku ma wartość NULL, funkcja COALESCE(1 + stawka_podatku, 1) zapewnia, że jest traktowana jako 0, co oznacza, że podatek nie jest naliczany, a mnożenie nie wpływa na obliczenia.

Powyższa konfiguracja pozwala na wygenerowanie kolumny obliczeniowej cena_calkowita, która zawiera rzeczywistą cenę końcową, nawet w przypadku braku lub występowania wartości NULL.

COALESCE w SQL i wyrażenie CASE

Funkcję COALESCE można zaimplementować za pomocą wyrażenia CASE. Oto przykład:

SELECT
NazwaProduktu + ' '+ data_dostawy szczegóły_produktu,
dostawca,
CASE
WHEN telefon_komórkowy is NOT NULL Then telefon_komórkowy
WHEN telefon_sluzbowy is NOT NULL Then telefon_sluzbowy
ELSE 'NA'
END
NumerKontaktowyWnagłychPrzypadkach
FROM
dbo.tb_KontaktWnagłychPrzypadkach

Powyższa konfiguracja zapytania CASE działa podobnie do funkcji COALESCE.

Możliwe jest również użycie wyrażeń COALESCE i CASE w tym samym zapytaniu. Te dwie techniki mogą jednocześnie obsługiwać wartości NULL i stosować logikę warunkową. Poniżej przykład:

Załóżmy, że masz tabelę „produkty” z kolumnami id_produktu, nazwa_produktu, cena i rabat. Niektóre produkty mają określoną zniżkę, a inne nie. Jeżeli produkt ma zniżkę, chcesz pokazać obniżoną cenę, w przeciwnym razie ma być wyświetlona cena regularna.

SELECT 
    id_produktu,
    nazwa_produktu,
    cena,
    COALESCE(
        CASE
            WHEN rabat > 0 THEN cena - (cena * rabat / 100)
            ELSE NULL
        END,
        cena
    ) AS cena_po_rabacie
FROM produkty;

W powyższym kodzie `CASE` sprawdza, czy `rabat` jest większy od zera i oblicza obniżoną cenę, w przeciwnym razie zwraca NULL. Funkcja `COALESCE` przyjmuje jako parametry wynik z `CASE` i `cena`. Zwraca pierwszą wartość różną od NULL, efektywnie zwracając cenę po rabacie, jeżeli jest dostępna, lub normalną cenę, jeżeli nie.

Podsumowanie

W tym artykule przedstawiono różne sposoby wykorzystania funkcji `COALESCE` w zapytaniach do bazy danych. Poprzez ocenę parametrów w określonej kolejności i zwracanie pierwszej wartości różnej od NULL, funkcja COALESCE upraszcza zapytania i sprawia, że są one bardziej efektywne.

Funkcja COALESCE jest wszechstronna i niezastąpiona, niezależnie od tego, czy zajmujesz się obsługą wartości NULL, łączeniem łańcuchów, przestawianiem danych, sprawdzaniem poprawności czy pracą z kolumnami obliczeniowymi. Dzięki opanowaniu funkcji COALESCE, programiści mogą skutecznie zarządzać brakującymi danymi i tworzyć bardziej niezawodne projekty baz danych. Nie zapomnij o solidnej praktyce, aby w pełni opanować tę technikę.

Możesz teraz sprawdzić, jak tworzyć ograniczenia klucza obcego w SQL.