Utilisation de CASE pour ajouter de la logique à une instruction SELECT

Lorsque vous écrivez une requête SQL, vous pouvez avoir besoin d'obtenir des valeurs de plusieurs colonnes et de changer des valeurs d'une forme à une autre. Le moyen le plus simple d'atteindre cet objectif est d'ajouter une expression CASE à votre instruction SELECT. Dans cet article, nous allons vous présenter la syntaxe, les formats et les utilisations de l'expression CASE.

L'expression CASE est une expression conditionnelle : elle évalue des données et renvoie un résultat. Le résultat qu'elle renvoie est basé sur le fait que les données répondent à certains critères. Cette expression peut être utilisée dans les instructions SELECT, UPDATE, et DELETE ou dans les clauses WHERE, IN, LIKE, ORDER BY, et HAVING. Nous allons nous concentrer sur l'instruction SELECT et utiliser les opérateurs IN et LIKE.

Vous devez savoir qu'il existe deux types d'expressions CASE: simples et recherchées. Nous examinerons les deux, mais nous commencerons par la syntaxe de base de cette expression.

Syntaxe CASE

La syntaxe de base de l'expression CASE est présentée ci-dessous :

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

L'expression commence par le mot clé CASE et se termine par le mot clé END. Les noms des colonnes ou expressions spécifiques sont saisis après le mot-clé CASE. Les mots-clés WHEN et THEN définissent les conditions logiques. Après la clause WHEN, on voit la condition ou la valeur à comparer ; le THEN détermine le résultat si la condition est remplie. Une instruction facultative ELSE détermine le résultat renvoyé si la condition WHEN n'est pas remplie. Si ELSE n'est pas définie et que WHEN n'est pas satisfaite, un NULL sera renvoyé.

Il existe deux types d'expressions CASE: simples et recherchées. Chacune a une syntaxe légèrement différente, et chacune fonctionne un peu différemment.

Nous allons commencer par le format simple.

CASE : le format simple

L'expression simple CASE compare une valeur à une ou plusieurs conditions WHEN. Si elle répond à une condition WHEN, le résultat THEN est renvoyé. Voici à quoi ressemble la syntaxe :

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

Dans une expression simple CASE, le nom de la colonne ou de l'expression à évaluer est absolument nécessaire. Il est placé après le mot-clé CASE. La valeur définie est placée après le mot clé WHEN. Si la valeur évaluée est la même que la valeur définie, le résultat défini dans THEN est retourné. Si la valeur de la colonne ou de l'expression ne correspond à aucune valeur de WHEN, le résultat de ELSE est affiché. N'oubliez pas que ELSE est facultatif.

Les exemples de requêtes suivants sont basés sur les données de la table animal comme indiqué ci-dessous :

Regardez ce simple CASE:

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

Cet exemple sélectionne les données de la table animal et affiche le nom du zoo actuel de l'animal. Si zoo=1, alors Brookfield Zoo sera affiché ; si zoo=2 alors nous verrons Dublin Zoo; tous les autres cas seront affichés comme unknown.

Le résultat est :

Remarquez que si la valeur de la colonne zoo n'est pas la même que l'une des valeurs des clauses WHEN (ou est NULL), le résultat est unknown.

Jusqu'à présent, nous avons considéré les expressions simples CASE qui ont un nom de colonne après le mot-clé CASE. Vous pouvez également placer une expression après le mot-clé CASE.

Imaginez que, outre le poids des animaux en kilogrammes, vous souhaitiez également afficher le poids total des mammifères par espèce - mais seulement si ces valeurs sont particulières. Dans ce cas, trois nombres caractéristiques doivent être déterminés. Ils seront affichés sous forme de chaînes de caractères. Si le poids total ne correspond pas à l'un de ces trois nombres, un autre sera affiché.

Voici la requête :

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 ;

Regardez le résultat :

Cette expression CASE contient une clause ELSE qui affiche other. Sans le ELSE, tous les poids non correspondants afficheraient un NULL. Voici le même code sans 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 ;

Et les résultats :

Utilisation d'alias dans les déclarations CASE

L'expression CASE peut avoir un alias, ou un nom pour la nouvelle colonne résultante. La requête ci-dessus, maintenant avec un alias, ressemble à ceci :

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

Et le résultat :

Utilisation de CASE avec d'autres types de données

Vous pouvez utiliser l'expression CASE avec d'autres types de données, tels que CHARACTER.

Regardez cette requête :

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 ;

Cette requête sélectionne des données dans la table et affiche le continent d'où provient l'animal. Le continent est basé sur la colonne country, qui utilise le type de données CHARACTER.

Voici le résultat :

L'expression CASE recherchée

L'expression recherchée CASE est le format le plus couramment utilisé. Mais au lieu de comparer une colonne ou une expression à un ensemble défini de valeurs, une expression recherchée peut comparer plusieurs conditions WHEN et déterminer un résultat. Cela fait d'une expression recherchée CASE le meilleur choix pour une logique plus complexe.

Voici la syntaxe :

CASE 
    WHEN  conditions THEN when_result
    ...
    ELSE else_result
END

Une fois encore, l'expression commence par le mot-clé CASE, mais aucun nom de colonne ni aucune expression ne suit. Il peut y avoir plusieurs constructions WHEN - THEN, tout comme le simple CASE. Mais après le mot-clé WHEN, nous saisissons une ou plusieurs conditions plutôt que des valeurs définies. Ces conditions compareront une valeur extraite et renverront la première qu'elle rencontre comme TRUE. Le résultat de THEN s'affichera alors. Si toutes les conditions renvoient FALSE, c'est le résultat de ELSE qui s'affiche. Et encore une fois, ELSE est facultatif.

Tout cela peut sembler difficile à comprendre, alors examinons notre exemple de requête :

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 ;

Regardez les résultats :

La première clause WHEN vérifie si weight est supérieur à 1 000 kilogrammes. Si c'est le cas, la chaîne big est renvoyée. La condition suivante WHEN vérifie si weight se situe entre 100 et 1 000 kilogrammes. Si c'est le cas, la chaîne middle est renvoyée. Dans ce cas, l'opérateur BETWEEN AND est utilisé pour définir l'intervalle. Pour les animaux dont weight est inférieur à 100 kilogrammes, la chaîne small sera affichée. Dans tous les autres cas, la valeur de retour sera "-".

Utilisation de CASE avec LIKE

Vous pouvez utiliser d'autres opérateurs avec l'expression CASE. Dans l'exemple suivant, nous utiliserons l'opérateur LIKE, qui correspond à des motifs dans les sous-chaînes de recherche. (Pour plus d'informations sur l'opérateur LIKE, lisez Utilisation de LIKE pour faire correspondre des motifs en SQL).

Voici l'exemple :

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

Et voici le résultat :

Dans cette expression recherchée CASE, l'opérateur LIKE teste si le nom de l'animal est composé de quatre ou cinq lettres. Dans d'autres cas - comme pour l'éléphant d'Afrique - la valeur de retour est autre.

Utilisation de CASE avec IN

Dans l'exemple suivant, nous allons tester l'opérateur IN. Vous vous souvenez de la simple expression CASE qui renvoyait le nom d'un continent en fonction du pays associé à un animal ? Le code de cette requête était long, grâce à de nombreuses constructions distinctes WHEN - THEN. Ci-dessous, nous vous donnons un autre aperçu de l'ancien code :

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'opérateur IN vous permet de réduire la quantité de code. Regardez une recherche CASE avec l'opérateur 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 ;

Nous pouvons utiliser l'opérateur IN pour lister plusieurs pays d'un même continent. Si la valeur de la colonne country correspond à l'une des chaînes de caractères de la clause IN, le résultat est le continent de la clause THEN. Bien entendu, si le nom du pays n'est répertorié dans aucun des opérateurs IN, ELSE entre en jeu et renvoie une valeur unknown.

Regardez le tableau de résultats ci-dessous :

Ordonner les conditions dans CASE

Vous pouvez maintenant créer des conditions dans la clause WHEN. Mais il est important de les construire avec précision. Si votre valeur de test correspond à la première clause WHEN, la clause THEN est renvoyée et l'expression CASE est terminée. Même si une valeur répond en fait à plusieurs conditions WHEN, la première condition qu'elle remplit est la seule qui sera renvoyée.

Regardez l'exemple suivant :

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 ;

Dans ce cas, la valeur weight sera la première à remplir la condition initiale WHEN. Dans notre tableau, la plupart des animaux auront une valeur weight inférieure à 1 000 kg. Seuls les animaux dont la valeur associée à weight est supérieure ou égale à 1 000 (ou dont la valeur de weight est NULL) se verront renvoyer une valeur "-".

Voici les résultats :

N'oubliez pas que l'ordre des conditions WHEN est très important ; il peut affecter les résultats que vous obtenez.

En savoir plus sur l'expression SQL CASE

Savoir utiliser une expression CASE correctement formatée est une bonne aptitude à cultiver. Elle vous permet d'obtenir les résultats que vous attendez de vos requêtes.

Si vous souhaitez en savoir plus sur l'expression CASE, consultez le cours LearnSQL Fonctions SQL standards de LearnSQL. Il vous montrera comment construire des requêtes qui utilisent cette expression et bien d'autres. Vous aurez également l'occasion de mettre en pratique vos connaissances. Essayez-le dès aujourd'hui !