La maggior parte dei report necessita di una certa logica: etichettare gli ordini come grandi o piccoli, contare gli ordini pagati e quelli in sospeso fianco a fianco o mostrare prima gli articoli urgenti. SQL CASE WHEN gestisce tutto questo direttamente all’interno di una query e funziona su tutti i principali database.

Abbiamo eseguito ogni query qui in SQLite 3.50 e i risultati mostrati provengono da quell’esecuzione. CASE fa parte di SQL standard, quindi si comporta allo stesso modo in PostgreSQL, MySQL, SQL Server e Oracle per questi esempi.

Cos’è CASO QUANDO?

CASE è un’espressione, non un comando separato. Valuta le condizioni in ordine e restituisce un valore, quindi può andare ovunque sia consentito un valore: in SELECT, WHERE, GROUP BY, ORDER BY o all’interno di funzioni. Termina sempre con END.

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE fallback
END

La tabella dei campioni

CREATE TABLE orders (id INT, customer TEXT, total INT, status TEXT);

INSERT INTO orders VALUES
 (1,'Asha',1200,'paid'), (2,'Ben',300,'pending'),
 (3,'Asha',800,'paid'), (4,'Chitra',2500,'shipped'),
 (5,'Dev',150,'cancelled'), (6,'Ben',NULL,'pending');

Nota che l’ordine 6 non ha totale. Quella riga conta più tardi.

CASO cercato

Questa è la forma più flessibile, poiché ciascuno WHEN ha una propria condizione completa:

SELECT id, total,
       CASE
         WHEN total >= 1000 THEN 'large'
         WHEN total >= 500 THEN 'medium'
         ELSE 'small'
       END AS size
FROM orders;

-- 1 | 1200 | large
-- 2 |  300 | small
-- 3 |  800 | medium
-- 4 | 2500 | large
-- 5 |  150 | small
-- 6 | NULL | small

Guarda l’ultima riga. L’ordine 6 non ha un totale, ma è etichettato come “piccolo”. Entrambi i confronti con NULL non erano veri, quindi è passato a ELSE. Mostriamo come risolvere questo problema nella sezione NULL. Assegna sempre un nome al risultato utilizzando AS, altrimenti potresti ottenere un nome di colonna scomodo.

CASO semplice

Quando confronti una colonna con valori fissi, la forma più breve si legge meglio:

SELECT id,
       CASE status
         WHEN 'paid' THEN 'Complete'
         WHEN 'shipped' THEN 'Complete'
         WHEN 'pending' THEN 'Waiting'
       END AS label
FROM orders;

-- 1 | Complete
-- 2 | Waiting
-- 3 | Complete
-- 4 | Complete
-- 5 | NULL
-- 6 | Waiting

L’ordine conta

CASE si ferma alla prima condizione vera e questo è il bug logico più comune. Scambia le due condizioni della nostra query precedente:

SELECT id, total,
       CASE
         WHEN total >= 500 THEN 'medium'
         WHEN total >= 1000 THEN 'large'
         ELSE 'small'
       END AS size
FROM orders WHERE id IN (1, 4);

-- 1 | 1200 | medium
-- 4 | 2500 | medium

Ormai nulla potrà mai essere “grande”. Un totale di 2500 è anche almeno 500, quindi vince il primo ramo e il secondo non viene mai raggiunto. Non viene visualizzato alcun errore, solo etichette errate. Per gli intervalli, metti prima la condizione più restrittiva oppure scrivi intervalli completi, come total >= 500 AND total < 1000.

Cosa succede senza ALTRO?

Nel nostro semplice esempio CASE sopra, l’ordine 5 è stato annullato e nessuna filiale lo ha gestito, quindi il risultato è stato NULL. A volte è esattamente quello che vuoi, come mostra la sezione successiva. Ma quando si creano etichette, un chiaro ELSE 'other' è solitamente migliore, poiché rende visibili i valori inaspettati anziché lasciare spazi vuoti in un report.

Rapporti in stile pivot con SUM

Questo è uno dei modelli più utili nell’SQL quotidiano. Il confezionamento di CASE in un aggregato trasforma le righe in colonne, con una riga per cliente:

SELECT customer,
       SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS paid,
       SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders
FROM orders
GROUP BY customer
ORDER BY customer;

-- Asha   | 2000 | 0
-- Ben    |    0 | 2
-- Chitra |    0 | 0
-- Dev    |    0 | 0

Una query ora fornisce sia le entrate pagate che il numero di ordini in sospeso, senza la necessità di query o unioni separate. Il modello funziona in quasi tutti i database, a differenza della sintassi dedicata PIVOT che solo alcuni offrono. Se GROUP BY è nuovo per te, leggi SQL GROUP BY e HAVING.

Conteggi condizionali

COUNT salta i NULL, quindi un CASE senza ELSE conta solo le righe che corrispondono:

SELECT COUNT(*) AS all_orders,
       COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_orders,
       COUNT(CASE WHEN total >= 1000 THEN 1 END) AS large_orders
FROM orders;

-- 6 | 2 | 2

Fai attenzione: se aggiungi ELSE 0 all’interno di un COUNT, ogni riga viene conteggiata, perché 0 non è NULL. Usa ELSE 0 con SUM e lascialo fuori con COUNT. PostgreSQL e SQLite supportano anche COUNT(*) FILTER (WHERE ...), che si legge bene, ma non è disponibile ovunque.

Raggruppamento per CASO

Puoi creare i tuoi bucket e poi riassumerli:

SELECT CASE WHEN total >= 1000 THEN 'large' ELSE 'other' END AS bucket,
       COUNT(*)
FROM orders
GROUP BY bucket
ORDER BY bucket;

-- large | 2
-- other | 4

Il raggruppamento per alias funziona in SQLite, PostgreSQL e MySQL. SQL Server non lo consente, quindi dovresti ripetere l’intera espressione o inserire prima la query in un CTE o in una sottoquery.

Ordinamento personalizzato con ORDER BY

L’ordine alfabetico è raramente ciò che un’azienda desidera. CASE ti consente di definire il tuo ordine:

SELECT id, status FROM orders
ORDER BY CASE status
           WHEN 'pending' THEN 1
           WHEN 'paid' THEN 2
           ELSE 3
         END, id;

-- 2 | pending
-- 6 | pending
-- 1 | paid
-- 3 | paid
-- 4 | shipped
-- 5 | cancelled

Gli ordini in sospeso vengono prima, il che è utile per una coda di lavoro. La seconda colonna di ordinamento, id, mantiene le righe in un ordine prevedibile all’interno di ciascun gruppo.

CASO in AGGIORNAMENTO

CASE consente a un’istruzione di applicare modifiche diverse a righe diverse. Qui, i piccoli ordini in sospeso vengono contrassegnati per la revisione:

UPDATE orders
SET status = CASE
               WHEN status = 'pending' AND total < 500 THEN 'review'
               ELSE status
             END;

SELECT id, status FROM orders WHERE customer = 'Ben';

-- 2 | review
-- 6 | pending

Il ELSE status mantiene invariate le altre righe. Tralasciarlo è pericoloso, perché le righe senza corrispondenza verrebbero impostate su NULL. Senza una clausola WHERE questa istruzione tocca anche ogni riga, quindi su tabelle di grandi dimensioni aggiungi un filtro e considera di racchiudere la modifica in una transazione in modo da poter controllare il risultato prima di impegnarti. Si noti inoltre che l’ordine 6 non è cambiato, perché il suo totale NULL non soddisfaceva total < 500.

Trappole NULL

NULL causa due problemi separati con CASE. Innanzitutto, la forma semplice confronta utilizzando l’uguaglianza e nulla è uguale a NULL, nemmeno lo stesso NULL:

SELECT CASE NULL WHEN NULL THEN 'matched' ELSE 'not matched' END;

-- not matched

Quindi CASE col WHEN NULL non corrisponde mai. Utilizzare invece il modulo cercato con IS NULL. In secondo luogo, come abbiamo visto con l’ordine 6, un valore NULL supera i confronti e finisce in ELSE. La soluzione è verificarlo prima:

SELECT id,
       CASE
         WHEN total IS NULL THEN 'unknown'
         WHEN total >= 1000 THEN 'large'
         ELSE 'other'
       END AS size
FROM orders WHERE id IN (1, 2, 6);

-- 1 | large
-- 2 | other
-- 6 | unknown

L’ordine 6 ora ottiene un’etichetta onesta. La nostra guida ai valori NULL SQL copre questo comportamento in modo completo.

CASO rispetto ad altre opzioni

Quest’ultima opzione merita una riflessione. Se un CASO cresce fino a trenta rami e appare in cinque query, sposta la mappatura in una tabella e unisciti ad essa. Quindi una modifica è un aggiornamento di una singola riga, anziché una modifica del codice in cinque punti.

Errori comuni

• Ordine delle condizioni errato. Una condizione ampia nasconde prima quelle più ristrette.
• Dimenticare END. Questo è l’errore di sintassi più frequente.
• Utilizzando WHEN NULL. Non corrisponde mai, quindi utilizza WHEN col IS NULL.
• Aggiungendo ELSE 0 all’interno di COUNT. Conta tutte le righe.
• Omissione di ELSE in un UPDATE. Può cancellare i valori con NULL.
• Filtro per CASE in WHERE. Può impedire l’utilizzo di un indice. Consulta la nostra guida all’ottimizzazione delle query SQL.

Considerazioni finali

CASE WHEN è una piccola funzionalità che fa molto lavoro. Ricorda tre regole: vince il primo ramo vero, le righe senza corrispondenza diventano NULL senza ELSE e NULL necessita del proprio controllo. Per esercitarti, prendi un report che attualmente crei con diverse query e prova a produrlo come un’unica query utilizzando SUM(CASE ...).

Domande frequenti

Continua a imparare