Utilizzo di CASE per aggiungere logica a una SELECT

Quando si scrive una query SQL, può essere necessario ottenere valori da più colonne e cambiare i valori da una forma all'altra. Il modo più semplice per raggiungere questo obiettivo è aggiungere un'espressione CASE all'istruzione SELECT. In questo articolo vi presenteremo la sintassi, i formati e gli usi dell'espressione CASE.

L'espressione CASE è un'espressione condizionale: valuta i dati e restituisce un risultato. Il risultato restituito si basa sul fatto che i dati soddisfino determinati criteri. Questa espressione può essere utilizzata nelle istruzioni SELECT, UPDATE e DELETE o nelle clausole WHERE, IN, LIKE, ORDER BY e HAVING. Ci concentreremo sull'istruzione SELECT e utilizzeremo gli operatori IN e LIKE.

Dovete sapere che esistono due tipi di espressioni CASE: semplici e ricercate. Le esamineremo entrambe, ma prima inizieremo con la sintassi di base di questa espressione.

Sintassi di CASE

La sintassi di base dell'espressione CASE è presentata di seguito:

CASE   [ column or expression ]
	WHEN  value or condition THEN when_result
	...
	ELSE else_result
END

L'espressione inizia con la parola chiave CASE e termina con la parola chiave END. I nomi di colonne o espressioni specifiche vengono inseriti dopo la parola chiave CASE. Le parole chiave WHEN e THEN definiscono le condizioni logiche. Dopo la clausola WHEN, viene indicata la condizione o il valore da confrontare; il THEN determina il risultato se la condizione è soddisfatta. Un'istruzione opzionale ELSE determina il risultato restituito se la condizione WHEN non è soddisfatta. Se ELSE non è definito e WHEN non è soddisfatto, verrà restituito NULL.

Esistono due tipi di espressioni CASE: semplici e ricercate. Ognuna ha una sintassi leggermente diversa e ognuna funziona in modo diverso.

Inizieremo con il formato semplice.

CASO: Il formato semplice

Il formato semplice CASE confronta un valore con una o più WHEN condizioni. Se soddisfa una condizione WHEN viene restituito il risultato THEN. Ecco come si presenta la sintassi:

CASE column_or_expression
    WHEN  value THEN when_result
    ...
    ELSE else_result
END

In un'espressione CASE semplice, il nome della colonna o dell'espressione da valutare è assolutamente necessario. Va dopo la parola chiave CASE. Il valore impostato va dopo WHEN. Se il valore valutato è uguale a quello impostato, viene restituito il risultato definito in THEN. Se il valore della colonna o dell'espressione non corrisponde a nessun valore di WHEN, viene visualizzato il risultato di ELSE. Ricordate che ELSE è opzionale.

Le seguenti query di esempio sono basate sui dati della tabella animal come mostrato di seguito:

Guardate questo semplice CASE:

CASE  zoo 
  WHEN 1 THEN 'Brookfield Zoo'
  WHEN 2 THEN 'Dublin zoo'
  ELSE 'unknown'
END 

Questo esempio seleziona i dati dalla tabella animal e visualizza il nome dello zoo attuale dell'animale. Se zoo=1, verrà visualizzato Brookfield Zoo; se zoo=2, verrà visualizzato Dublin Zoo; in tutti gli altri casi verrà visualizzato unknown.

Il risultato è:

Notate che se il valore della colonna zoo non corrisponde a nessuno dei valori delle clausole WHEN (o è NULL), il risultato è unknown.

Finora abbiamo considerato semplici espressioni CASE che hanno un nome di colonna dopo la parola chiave CASE. È anche possibile inserire un'espressione dopo la parola chiave CASE.

Immaginate di voler visualizzare, oltre al peso degli animali in chilogrammi, anche il peso totale dei mammiferi per specie, ma solo se questi valori sono speciali. In questo caso, è necessario determinare tre numeri caratteristici. Questi verranno visualizzati come stringhe. Se il peso totale non è uno di questi tre numeri, ne verranno mostrati altri.

Ecco la query:

SELECT name, weight, number_of_animal,
		( CASE  number_of_animal*weight 
  			WHEN 18 THEN 'small'
  			WHEN 360 THEN 'middle'
  			WHEN 24000 THEN 'big'
  			ELSE 'other'
		  END 
                 ) as total_weight
FROM  animal ;

Guardate il risultato:

Questa espressione CASE ha una clausola ELSE che visualizza altro. Senza la clausola ELSE, tutti i pesi non corrispondenti visualizzerebbero un NULL. Ecco lo stesso codice senza la clausola ELSE:

SELECT  name, weight, number_of_animal,
			 CASE  number_of_animal*weight 
  				WHEN 18 THEN 'small'
  				WHEN 360 THEN 'middle'
  				WHEN 24000 THEN 'big'
			 END 
                         as total_weight
FROM  animal ;

E i risultati:

Uso degli alias nelle dichiarazioni CASE

L'espressione CASE può avere un alias, ovvero un nome per la nuova colonna risultante. La query precedente, ora con un alias, si presenta così:

SELECT name, zoo, 
			  CASE  zoo 
  			     WHEN 1 THEN 'Brookfield Zoo'
  			     WHEN 2 THEN 'Dublin zoo'
  			     ELSE 'unknown'
			   END 
			  as name_zoo
FROM  animal ;

E il risultato:

Uso di CASE con altri tipi di dati

È possibile utilizzare l'espressione CASE con altri tipi di dati, come CHARACTER.

Guardate questa query:

SELECT  name, zoo, 
			   ( CASE  country 
 			 	WHEN 'Russia' THEN 'Europe'
  				WHEN 'Congo' THEN 'Africa'
 				WHEN 'Turkey' THEN 'Asia'
  				WHEN 'Australia' THEN 'Australia' 
  				WHEN 'China' THEN 'Asia'
  				ELSE 'unknown'
				END 
                            ) as continent
FROM  animal ;

Questa query seleziona i dati dalla tabella e visualizza il continente da cui proviene l'animale. Il continente è basato sulla colonna country, che utilizza il tipo di dati CHARACTER.

Questo è il risultato:

L'espressione CASE ricercata

L'espressione cercata CASE è il formato più comunemente utilizzato. Ma invece di confrontare una colonna o un'espressione con un insieme definito di valori, un'espressione ricercata può confrontare più WHEN condizioni e determinare un risultato. Questo fa sì che un'espressione CASE ricercata sia la scelta migliore per la logica più complicata.

Ecco la sintassi:

CASE 
    WHEN  conditions THEN when_result
    ...
    ELSE else_result
END

Ancora una volta, l'espressione inizia con la parola chiave CASE, ma non c'è alcun nome di colonna o espressione che la segua. Ci possono essere più costruzioni WHEN - THEN, proprio come il semplice CASE. Tuttavia, dopo la parola chiave WHEN, si inseriscono una o più condizioni, invece di impostare dei valori. Queste condizioni confronteranno un valore recuperato e restituiranno il primo che incontra come TRUE. In questo modo verrà visualizzato il risultato di THEN. Se tutte le condizioni restituiscono FALSE, viene visualizzato il risultato di ELSE. E ancora, ELSE è opzionale.

Tutto ciò potrebbe sembrare difficile da capire, quindi analizziamo la nostra query di esempio:

SELECT  name, weight,
		  CASE 
  		    WHEN weight>1000 THEN 'big'
  		    WHEN weight BETWEEN 100  AND 1000 THEN 'middle'
		    WHEN weight<100 THEN 'small'
	            ELSE '---'
		  END 
		  as description_weight
FROM  animal ;

Guardate i risultati:

La prima clausola WHEN controlla se weight è maggiore di 1.000 chilogrammi. Se è vera, viene restituita la stringa big. La successiva condizione WHEN verifica se weight è compreso tra 100 e 1.000 chilogrammi. In caso affermativo, viene restituita la stringa middle. In questo caso, l'operatore BETWEEN AND viene utilizzato per definire l'intervallo. Per gli animali il cui weight è inferiore a 100 chilogrammi, verrà visualizzata la stringa small. In tutti gli altri casi, il valore restituito sarà "- ".

Uso di CASE con LIKE

È possibile utilizzare altri operatori con l'espressione CASE. Nell'esempio che segue, utilizzeremo l'operatore LIKE, che corrisponde a modelli nelle sottostringhe di ricerca. (Per ulteriori informazioni sull'operatore LIKE, leggere Utilizzo di LIKE per la corrispondenza di modelli in SQL).

Ecco l'esempio:

SELECT name, 
		( CASE 
  			WHEN name LIKE '____' THEN '4 letters'
  			WHEN name LIKE '_____' THEN '5 letters'
 			ELSE 'other'
		  END 
		) as description
FROM  animal ;

Ed ecco il risultato:

In questa espressione CASE ricercata, l'operatore LIKE verifica se il nome dell'animale è composto da quattro o cinque lettere. In altri casi, come nel caso dell'elefante africano, il valore restituito è altro.

Uso di CASE con IN

Nel prossimo esempio, proveremo l'operatore IN. Ricordate la semplice espressione CASE che restituiva il nome di un continente in base al paese associato a un animale? Il codice per questa query era lungo, grazie a molte costruzioni separate WHEN - THEN. Di seguito, diamo un'altra occhiata al vecchio codice:

SELECT  name, country,
			( CASE  country 
  				WHEN 'Russia' THEN 'Europe'
  				WHEN 'Congo' THEN 'Africa'
  				WHEN 'Turkey' THEN 'Asia'
 				WHEN 'Australia' THEN 'Australia' 
  				WHEN 'China' THEN 'Asia'
  				ELSE 'unknown'
			  END 
                         ) as continent
FROM  animal ;

L'operatore IN consente di ridurre la quantità di codice. Guardate una ricerca CASE con l'operatore IN:

SELECT name, country, 
		( CASE   
 		    WHEN country IN ('Russia') THEN 'Europe'
 		    WHEN country IN ('Congo') THEN 'Africa'
		    WHEN country IN ('Turkey','China') THEN 'Asia'
 		    WHEN country IN ('Australia') THEN 'Australia' 
	            ELSE 'unknown'
		  END 
		) as continent
FROM  animal ;

Possiamo usare l'operatore IN per elencare più paesi dello stesso continente. Se il valore della colonna country corrisponde a una delle stringhe della clausola IN, il risultato è il continente della clausola THEN. Naturalmente, se il nome del paese non è elencato in nessun operatore IN, interviene ELSE e restituisce il valore unknown.

Guardate la tabella dei risultati qui sotto:

Ordinare le condizioni in CASE

Ora è possibile creare condizioni nella clausola WHEN. Ma è importante costruirle in modo accurato. Se il valore del test corrisponde alla prima clausola WHEN, la clausola THEN viene restituita e l'espressione CASE è terminata. Anche se un valore soddisfa diverse condizioni WHEN, la prima condizione soddisfatta è l'unica che verrà restituita.

Osservate il seguente esempio:

SELECT name, weight,
				( CASE 
  					WHEN weight<1000 THEN 'not big'
  					WHEN weight<100 THEN 'not middle'
  					WHEN weight<10 THEN 'very small'
 					ELSE '---'
			  	  END 
				) as description_weight
FROM  animal ;

In questo caso, il valore weight sarà il primo a soddisfare la condizione WHEN iniziale. Nella nostra tabella, la maggior parte degli animali avrà un valore weight inferiore a 1.000 kg. Solo gli animali con un weight associato di 1.000 o più (o che hanno un valore NULL in weight) riceveranno un valore "-".

Ecco i risultati:

Ricordate che l'ordine delle condizioni WHEN è molto importante e può influenzare i risultati ottenuti.

Per saperne di più sull'espressione SQL CASE

Saper utilizzare un'espressione CASE correttamente formattata è una buona capacità da coltivare. Permette di ottenere i risultati attesi dalle query.

Se siete interessati a saperne di più sull'espressione CASE, consultate il corso di LearnSQL. Standard SQL Functions di LearnSQL. Vi mostrerà come costruire query che utilizzano questa espressione e molte altre. Avrete anche la possibilità di mettere in pratica le vostre abilità. Provatelo oggi stesso!