Suite à notre article sur le langage SQL dans lequel nous avons montré l’importance et l’utilisation de ce dernier, nous avons entamé une série d’articles détaillant ses fonctionnalités. Dans cet article, nous allons vous apprendre à rédiger des requêtes SQL intelligentes avec SQL CASE.
Soumettre des conditions est essentiel lorsque l’on effectue une requête SQL, car c’est grâce à cela que l’on obtient des résultats pertinents. Plusieurs méthodes peuvent être employées pour cela. SQL CASE fait partie de ces méthodes. C’est pour cela que, dans cet article, nous allons vous faire découvrir cette instruction et ses différents cas d’utilisation.
SQL CASE : définition
Dans le langage SQL, la commande CASE WHEN est utilisée comme une instruction if…else dans les langages de programmation informatique. Elle est utilisable avec les instructions ou clauses comme SELECT, UPDATE, DELETE, WHERE, ORDER BY ou HAVING. Sans plus attendre, entrons dans le vif du sujet.
La syntaxe de la commande CASE SQL
Il existe deux syntaxes possibles pour cette commande :
- Mettre en place une série de conditions booléennes pour obtenir un résultat ;
- Faire la comparaison d’une colonne à un set résultat possible.
Mettre en place une série de conditions booléennes
Vous pouvez obtenir un résultat en effectuant une condition plus complexe. Pour ce faire, vous pouvez exprimer les conditions de la manière suivante :
CASE
WHEN expression_boolean THEN resultat_expression [ ...n ]
[ ELSE negation_resultat_expression ]
END
Prenons l’expression suivante comme exemple :
CASE
WHEN a=b THEN 'A égal à B'
WHEN a>b THEN 'A supérieur à B'
ELSE 'A inférieur à B'
END
Ici, les colonnes a,b,c peuvent être de type numérique ou alphanumérique. Les valeurs spécifiées après l’instruction THEN ne sont retournées que lorsque la condition est vérifiée.
Cette syntaxe peut également s’écrire de la manière suivante :
CASE
WHEN a=1 THEN 'un'
WHEN a=2 THEN 'deux'
WHEN a=3 THEN 'trois'
ELSE 'autre'
END
Comparer une colonne à un set résultat
Pour comparer une colonne à un set enregistrement, il faut procéder par une syntaxe de ce style :
CASE expression_entrer
WHEN when_expression THEN resultat_expression [ ...n ]
[ ELSE negation_resultat_expression ]
END
Voici un exemple d’utilisation de cette syntaxe :
CASE a
WHEN 1 THEN 'un'
WHEN 2 THEN 'deux'
WHEN 3 THEN 'trois'
ELSE 'autre'
END
Dans cet exemple, chaque valeur de la colonne a est comparée à 1, 2 et 3. Si la réponse est True, alors la valeur qui se trouve après THEN sera affichée.
Note : Il faut savoir que l’instruction ELSE n’est pas obligatoire dans l’instruction. Ici, si toutes les conditions ne sont pas vérifiées, c’est la valeur après ELSE qui sera retournée par défaut.
Les arguments
Voici les arguments acceptés par l’instruction SQL CASE :
- expression_entrer : Le format CASE simple utilise cette expression pour l’évaluation ;
- when_expression : C’est cette expression qui est utilisée pour effectuer la comparaison. Cette expression doit être valide ;
Il faut savoir que l’expression_entrer et when_expression doivent être de même type ;
- resultat_expression : il s’agit du résultat de la comparaison lorsque le résultat de celle-ci est True;
- negation_resultat_expression : c’est la valeur retournée lorsque la comparaison n’a pas pour valeur True;
- expression_boolean : c’est l’expression booléenne évaluée.
Illustration de la commande SQL CASE
Pour bien comprendre le fonctionnement de cette commande, considérons une table de la base de données d’un site e-commerce. Cette table contient des colonnes pour le nom de produit, le prix unitaire, la quantité achetée ainsi qu’une colonne surcharge.
La requête suivante affichera un message en fonction de la valeur des lignes de la colonne surcharge. Le message à afficher sera différent si la marge est égale à 1, supérieure à 1 ou inférieure à 1. La requête en question est la suivante :
SELECT id, nom, surcharge, prix_unitaire, quantite,
CASE
WHEN surcharge=1 THEN 'Prix ordinaire'
WHEN surcharge>1 THEN 'Prix supérieur à la normale'
ELSE 'Prix inférieur à la normale'
END
FROM `achat`
Le résultat obtenu à l’issue de cette requête est :
Afficher un prix unitaire différent selon une condition
Vous pouvez élaborer des requêtes plus avancées grâce à la commande CASE. Prenons par exemple le cas suivant :
On souhaite multiplier par 2 le prix unitaire si la surcharge est supérieure à 1, diviser par 2 si la surcharge est inférieure à 1 et laisser le prix initial si la surcharge est égale à 1. Pour ce faire, la requête est la suivante :
SELECT id, nom, surcharge, prix_unitaire, quantite,
CASE
WHEN surcharge=1 THEN prix_unitaire
WHEN surcharge>1 THEN prix_unitaire*2
ELSE prix_unitaire/2
END
FROM ‘achat’ ;
Comparer un champ à une valeur donnée
Maintenant, si le site propose des réductions à un certain nombre de produits achetés, par exemple :
- 1 produit acheté donne droit à une réduction de -5% pour le prochain achat ;
- 2 produits achetés donnent droit à une réduction de -6% pour le prochain achat ;
- 3 produits achetés donnent droit à une réduction de -8% pour le prochain achat ;
- Plus de 3 produits achetés donnent droit à une réduction de -10% pour le prochain achat.
Pour réussir une telle instruction, il faut simplement comparer la colonne quantité aux différentes valeurs spécifiées et afficher un message en retour. La requête peut être la suivante :
SELECT id, nom, surcharge, prix_unitaire, quantite,
CASE quantite
WHEN 0 THEN 'Erreur'
WHEN 1 THEN 'Offre de -5% pour le prochain achat'
WHEN 2 THEN 'Offre de -6% pour le prochain achat'
WHEN 3 THEN 'Offre de -8% pour le prochain achat'
ELSE 'Offre de -10% pour le prochain achat'
END
FROM `achat`
Le résultat de ces instructions est :
UPDATE avec CASE
Comme nous l’avons mentionné au début de l’article, il est possible d’utiliser la commande CASE avec UPDATE pour la mise à jour d’une colonne.
Pour illustrer cela, reprenons notre exemple avec le cas suivant :
Pour tous les achats qui ont une surcharge inférieure à 1, on offre un produit de plus. Et l’on retire un produit si les achats ont une surcharge supérieure à 1. Voici la requête pour effectuer cela :
UPDATE `achat`
SET `quantite` = (
CASE
WHEN `surcharge` 1 THEN `quantite` - 1
ELSE quantite
END
)
Le résultat obtenu est :
Utilisation de SELECT avec SQL CASE
Dans une instruction SELECT, CASE se charge d’afficher les différents résultats possibles suite à une comparaison. Dans l’exemple qui suit, CASE affichera un commentaire en fonction de l’intervalle où le prix du produit se situe.
USE AdventureWorks2012;
GO
SELECT ProductNumber, Name, "Price Range" =
CASE
WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
WHEN ListPrice = 50 and ListPrice = 250 and ListPrice < 1000 THEN 'Under $1000'
ELSE 'Over $1000'
END
FROM Production.Product
ORDER BY ProductNumber ;
GO
Utilisation de CASE dans une instruction SET
Considérons la base de données AdventureWorks2012 dans laquelle toutes les informations relatives aux employés sont stockées. Pour obtenir des informations sur un employé, dès lors que le nom de la personne est entré dans la fonction, l’expression CASE dans SET se charge des informations.
USE AdventureWorks2012;
GO
CREATE FUNCTION dbo.GetContactInformation(@BusinessEntityID INT)
RETURNS @retContactInformation TABLE
(
BusinessEntityID INT NOT NULL,
FirstName NVARCHAR(50) NULL,
LastName NVARCHAR(50) NULL,
ContactType NVARCHAR(50) NULL,
PRIMARY KEY CLUSTERED (BusinessEntityID ASC)
)
AS
-- Returns the first name, last name and contact type for the specified contact.
BEGIN
DECLARE
@FirstName NVARCHAR(50),
@LastName NVARCHAR(50),
@ContactType NVARCHAR(50);
-- Get common contact information
SELECT
@BusinessEntityID = BusinessEntityID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Person
WHERE BusinessEntityID = @BusinessEntityID;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e
WHERE e.BusinessEntityID = @BusinessEntityID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Person.BusinessEntityContact AS bec
WHERE bec.BusinessEntityID = @BusinessEntityID)
THEN 'Vendor'
-- Check for store
WHEN EXISTS(SELECT * FROM Purchasing.Vendor AS v
WHERE v.BusinessEntityID = @BusinessEntityID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Customer AS c
WHERE c.PersonID = @BusinessEntityID)
THEN 'Consumer'
END;
-- Return the information to the caller
IF @BusinessEntityID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @BusinessEntityID, @FirstName, @LastName, @ContactType;
END;
RETURN;
END;
GO
SELECT BusinessEntityID, FirstName, LastName, ContactType
FROM dbo.GetContactInformation(2200);
GO
SELECT BusinessEntityID, FirstName, LastName, ContactType
FROM dbo.GetContactInformation(5);
Instruction CASE avec la clause Order By
En SQL, on utilise la clause ORDER BY pour trier les résultats par ordre croissant ou décroissant. Vous pouvez inclure la commande CASE dans cette clause. Pour cela, considérons la table employee suivante :
On peut par exemple trier le salaire des employés masculins par ordre croissant et par ordre décroissant pour les employés féminins. Pour cela, il faut exécuter cette instruction :
Select Nom_Employee,Genre,Salary
from Employee
ORDER BY CASE Genre
WHEN 'F' THEN Salary End DESC,
Case WHEN Genre='M' THEN Salary
END
Instruction SQL CASE avec la clause HAVING
La clause HAVING est utilisée pour restreindre les lignes. Considérons l’exemple suivant :
Pour une table HumanResources.Employee, on veut obtenir un taux de rémunération supérieur à 50$ pour les hommes et de 10$ pour les femmes.
USE AdventureWorks2012;
GO
SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID
GROUP BY JobTitle
HAVING (MAX(CASE WHEN Gender = 'M'
THEN ph1.Rate
ELSE NULL END) > 50.00
OR MAX(CASE WHEN Gender = 'F'
THEN ph1.Rate
ELSE NULL END) > 10.00)
ORDER BY MaximumRate DESC;
Ici, la clause HAVING est utilisée pour effectuer cette restriction.
Instruction CASE avec la clause GROUP BY
Cette clause est idéale si l’on souhaite regrouper les employés en fonction de leur salaire. Pour calculer le salaire minimum et maximum d’un groupe d’employés, il faut exécuter cette instruction :
Select
CASE
WHEN Salary >=80000 AND Salary =50000 AND Salary =80000 AND Salary =50000 AND Salary <80000 THEN 'Senior Consultant'
Else 'Director'
END
Insérer des données avec SQL CASE
Il est possible d’insérer des valeurs grâce à la clause CASE. Si, par exemple, il existe une application qui insère des données dans la table employée. Nous aurons les correspondances suivantes :
| Valeur | Employé | Valeur requise dans la table |
|---|---|---|
| 0 | Sexe Mas | M |
| 1 | Sexe Fem | F |
Ici, les valeurs à insérer ne sont pas 0 et 1, mais plutôt M et F.
Declare @Nom_Employee varchar(100)
Declare @Genre int
Declare @Etat_code char(2)
Declare @salary money
Set @Nom_Employee='Raj'
Set @Genre=0
Set @Etat_code='FL'
set @salary=52000
Insert into employe
values
(@Nom_Employee,
CASE @Genre
WHEN 0 THEN 'M'
WHEN 1 THEN 'F'
end,
@Etat_code,
@salary)
Dans cette requête, des variables sont spécifiées pour stocker les valeurs de colonne. Les valeurs requises sont vérifiées et les valeurs de l’expression THEN sont insérées dans la table.
Instruction CASE avec la fonction SUM
Pour illustrer l’utilisation de l’instruction SQL CASE dans une fonction SUM, considérons l’exemple suivant :
SELECT
SUM(CASE
WHEN order_status = 1
THEN 1
ELSE 0
END) AS 'En attente',
SUM(CASE
WHEN order_status = 2
THEN 1
ELSE 0
END) AS 'Traitement',
SUM(CASE
WHEN order_status = 3
THEN 1
ELSE 0
END) AS 'Rejeté',
SUM(CASE
WHEN order_status = 4
THEN 1
ELSE 0
END) AS 'Achevé',
COUNT(*) AS Total
FROM
sales.orders
WHERE
YEAR(order_date) = 2018;
Dans ce code, vous avez :
- Premièrement, dans la clause WHERE, l’instruction qui donne la date de la commande client en 2018 ;
- Deuxièmement, en fonction de l’état de la commande, l’instruction CASE qui retourne 1 ou 0 ;
- Troisièmement, la fonction SUM qui additionne le nombre de commandes pour chaque état ;
- Quatrièmement, la fonction COUNT() qui renvoie le total de la commande.
Nous sommes arrivés à la fin de cet article sur SQL CASE. Pour conclure, il faut retenir que l’instruction CASE en SQL fonctionne de la même manière comme l’instruction if-then-else en langage de programmation informatique. Il donne en retour un résultat après vérification de certaines conditions requises. Et si les conditions ne sont pas vérifiées, il retourne par défaut NULL.
Maintenant, vous pouvez utiliser cette instruction dans vos prochaines requêtes SQL afin de mieux les structurer et ainsi d’obtenir de meilleurs résultats.
Si vous souhaitez apprendre davantage sur le langage SQL et ses instructions, il existe déjà quelques articles sur ce sujet sur notre blog. Nous vous invitons également à télécharger ce tutoriel sur le langage Scala si vous souhaitez améliorer vos compétences en Big Data et en programmation informatique.
