Instrukcja CASE w SQL i SQLite: WHEN, THEN, ELSE i IIF

CASE to SQL-owe if/else

CASE pozwala umieścić logikę warunkową w zapytaniu. Przechodzi po gałęziach WHEN po kolei, wybiera pierwszą pasującą i zwraca wartość po THEN. Jeśli nic nie pasuje, zwraca wartość z ELSE albo NULL, jeśli ELSE nie ma.

Najważniejsze słowo to wyrażenie. CASE daje wartość, więc pasuje wszędzie tam, gdzie dozwolona jest wartość: jako kolumna w SELECT, klucz w ORDER BY, prawa strona porównania, argument funkcji.

SELECT
    'apple' AS item,
    CASE
        WHEN 5 > 3 THEN 'yes'
        ELSE 'no'
    END AS bigger;

Tak wygląda cała konstrukcja: CASE, jedno lub więcej WHEN ... THEN ..., opcjonalne ELSE, a na końcu END. END jest obowiązkowe, a zapominanie o nim to najczęstsza literówka.

Realistyczny przykład

Powiedzmy, że masz tabelę zamówień i chcesz oznaczyć każdy wiersz według wielkości. Zbuduj małą tabelę w miejscu, żeby móc uruchomić zapytanie:

WITH orders(id, total) AS (
    VALUES
        (1, 12.00),
        (2, 89.50),
        (3, 250.00),
        (4, 5.25),
        (5, 1200.00)
)
SELECT
    id,
    total,
    CASE
        WHEN total < 10 THEN 'tiny'
        WHEN total < 100 THEN 'small'
        WHEN total < 500 THEN 'medium'
        ELSE 'large'
    END AS size_bucket
FROM orders;

Gałęzie są sprawdzane od góry do dołu. Wygrywa pierwsze dopasowanie, więc ustawiaj je od najbardziej szczegółowej do najbardziej ogólnej. ELSE łapie wszystko, co nie pasowało: bez niego 1200.00 wróciłoby jako NULL, a nie 'large'.

Forma z warunkami a forma prosta

To, co widzisz powyżej, to forma z warunkami (searched CASE): każde WHEN ma własny warunek logiczny. Gdy porównujesz jedno wyrażenie z kilkoma stałymi, jest krótsza forma, czyli forma prosta:

WITH users(id, status) AS (
    VALUES
        (1, 'A'),
        (2, 'B'),
        (3, 'C'),
        (4, 'A')
)
SELECT
    id,
    status,
    CASE status
        WHEN 'A' THEN 'active'
        WHEN 'B' THEN 'banned'
        WHEN 'C' THEN 'closed'
        ELSE 'unknown'
    END AS status_label
FROM users;

Wyrażenie po CASE jest obliczane raz i porównywane przez = z każdą wartością WHEN. To czytelniejsze, gdy sprawdzasz równość w jednej kolumnie.

Jeden haczyk: prosty CASE używa =, a NULL = NULL w SQL nie jest prawdą. Jeśli status może być NULL, gałęzie 'A'/'B'/'C' go nie złapią i trafisz do ELSE. Aby jawnie obsłużyć NULL, przejdź na formę z warunkami i użyj WHEN status IS NULL THEN ....

CASE w ORDER BY

ORDER BY przyjmuje dowolne wyrażenie, więc CASE sprawdza się tam bez problemu. Przydaje się, gdy chcesz własnej kolejności sortowania, innej niż alfabetyczna czy liczbowa:

WITH tickets(id, priority) AS (
    VALUES
        (1, 'low'),
        (2, 'high'),
        (3, 'medium'),
        (4, 'high'),
        (5, 'low')
)
SELECT id, priority
FROM tickets
ORDER BY
    CASE priority
        WHEN 'high' THEN 1
        WHEN 'medium' THEN 2
        WHEN 'low' THEN 3
    END,
    id;

Alfabetycznie 'high' < 'low' < 'medium', co jest bezużyteczne przy ustalaniu priorytetów. Zamiana każdego priorytetu na liczbę przez CASE daje kolejność, której naprawdę chcesz. Końcowe , id stabilnie rozstrzyga remisy.

CASE w WHERE

CASE możesz umieścić w WHERE, ale zwykle nie ma takiej potrzeby: łańcuch AND/OR jest czytelniejszy. Błyszczy wtedy, gdy sam warunek zależy od innej wartości:

WITH products(id, name, price, on_sale) AS (
    VALUES
        (1, 'mug', 12.00, 1),
        (2, 'shirt', 25.00, 0),
        (3, 'cap', 18.00, 1),
        (4, 'bag', 45.00, 0)
)
SELECT id, name, price
FROM products
WHERE price < CASE WHEN on_sale = 1 THEN 20 ELSE 30 END;

Produkty w promocji kwalifikują się poniżej 20, zwykłe poniżej 30. Sam próg jest warunkowy. Bez CASE trzeba by napisać (on_sale = 1 AND price < 20) OR (on_sale = 0 AND price < 30): ten sam wynik, więcej szumu.

CASE wewnątrz funkcji agregujących

Tu CASE naprawdę zarabia na siebie. Połącz go z SUM lub COUNT, aby w jednym przejściu policzyć sumy dla podzbioru wierszy. To SQL-owy odpowiednik "policz, ile z nich pasuje":

WITH orders(id, total, status) AS (
    VALUES
        (1, 50.00, 'paid'),
        (2, 30.00, 'pending'),
        (3, 75.00, 'paid'),
        (4, 20.00, 'cancelled'),
        (5, 100.00, 'paid')
)
SELECT
    COUNT(*) AS all_orders,
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count,
    SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS paid_revenue
FROM orders;

CASE zwraca 1 dla pasujących wierszy i 0 dla pozostałych, więc SUM staje się liczeniem warunkowym. Ta sama sztuczka działa dla przychodu: zwróć total dla pasujących wierszy i 0 wszędzie indziej. Jeden skan tabeli, kilka warunkowych agregatów.

IIF: skrót dla dwóch gałęzi

Dla jednego warunku z dwoma wynikami SQLite ma IIF(cond, when_true, when_false). To czysty skrót od CASE WHEN cond THEN when_true ELSE when_false END:

WITH accounts(id, balance) AS (
    VALUES
        (1, 100.00),
        (2, -25.00),
        (3, 0.00),
        (4, 1500.00)
)
SELECT
    id,
    balance,
    IIF(balance < 0, 'overdrawn', 'ok') AS state
FROM accounts;

Używaj IIF, gdy logika jest binarna i lepiej czyta się w jednej linii. Przejdź na CASE, gdy masz trzy lub więcej gałęzi, musisz osobno obsłużyć NULL albo zależy ci na kolejności sprawdzania kilku klauzul WHEN.

Pułapki, które warto znać

Kilka rzeczy, na których ludzie się potykają:

• Zapomniane END. CASE otwiera blok, a END go zamyka. SQLite zgłosi błąd parsowania dopiero daleko za prawdziwym błędem.
• Brak ELSE oznacza NULL. Jeśli żadna gałąź WHEN nie pasuje, a pominięto ELSE, wynikiem jest NULL. Czasem o to chodzi, ale zwykle nie.
• Kolejność gałęzi ma znaczenie. W formie z warunkami wygrywa pierwsze pasujące WHEN. Jeśli WHEN total < 500 stoi przed WHEN total < 100, druga gałąź jest nieosiągalna.
• Mieszanie typów. Każda gałąź może zwracać inny typ i SQLite nie będzie narzekać, ale dalszy kod może. Staraj się, żeby wszystkie gałęzie zwracały zgodne typy (sam tekst albo same liczby).
• Prosty CASE i NULL. Jak wspomniano: forma prosta używa =, które nigdy nie pasuje do NULL. Gdy w grę wchodzą NULL, używaj formy z warunkami.

Dalej: funkcje tekstowe

CASE pozwala rozgałęziać logikę na podstawie wartości, a następny rozdział zaczyna się od przekształcania wartości. Funkcje tekstowe (UPPER, LOWER, SUBSTR, REPLACE, wzorce LIKE) wykonują codzienną pracę czyszczenia i przeformatowywania kolumn tekstowych. To temat następnej strony.