La fonction SQL Coalesce : Traitement des valeurs nulles

Vous savez peut-être déjà comment retourner des valeurs nulles en SQL. Maintenant, nous allons apprendre à faire le contraire. Bien que la fonction SQL COALESCE puisse sembler complexe, elle est en fait très simple et utile. Examinons plusieurs exemples de la façon dont la fonction SQL COALESCE peut être utilisée pour travailler avec des valeurs NULL en SQL.

La nécessité de la fonction Coalesce en SQL

Avant de nous plonger dans les détails de la fonction SQL COALESCE, vous devez comprendre comment les valeurs NULL se comportent dans les expressions. En termes simples, une valeur NULL indique qu'il n'y a actuellement aucune valeur pour une entrée particulière dans une colonne de table.

Toute expression impliquant des nombres, des dates, des chaînes de caractères ou des booléens et une valeur NULL renverra elle-même NULL. Cela est dû au fait que NULL représente une valeur inconnue. Puisque toute expression impliquant une valeur inconnue ne peut être entièrement déterminée, elle doit naturellement retourner NULL !

Regardez les exemples d'expressions suivants :

	2  +  NULL 			returns		 NULL

	2  *  NULL 			returns		 NULL

	'hello'  ||  NULL 		returns		 NULL

	NULL  +  interval '1 day' 	returns		 NULL

	TRUE and NULL	                returns		 NULL

Travailler avec les amendes de transit

Pour expliquer le fonctionnement de la fonction SQL COALESCE, nous allons utiliser l'exemple guidé des amendes de transit. Le tableau ci-dessous stocke l'ID du conducteur, l'amende qu'il a reçue, la date et l'heure à laquelle l'amende a été imposée, le niveau/degré de l'infraction commise par le conducteur, et le montant des amendes impayées précédentes que le conducteur a accumulées.

Notez que la colonne offense_level peut prendre les valeurs "Bande A", "Bande B" ou "Bande C". Un niveau d'infraction de la bande B indique que le conducteur devra payer 1,5 fois le montant de l'amende s'il ne paie pas l'amende initiale dans les 30 jours. La catégorie C double le montant de l'amende après 30 jours. Un niveau d'infraction de la bande A n'affecte pas le montant de l'amende si elle n'est pas payée dans les 30 jours.

Le mystère de l'amende qui disparaît

Nous allons d'abord examiner les amendes payées dans le délai de 30 jours. Dans un autre exemple, nous examinerons les frais supplémentaires qui peuvent être facturés au conducteur après le délai de 30 jours.

En théorie, la requête suivante devrait renvoyer la valeur de chaque amende et la valeur totale de toutes les amendes que le conducteur doit payer (y compris les amendes précédentes non payées).

SELECT 	driver_id,
		fine_value 			as "fine_amount", 
		fine_value + unpaid_fines 	as "total_due" 
FROM 	fines;

Cependant, si nous examinons les résultats de la requête dans le tableau suivant, nous remarquons que le premier enregistrement ne contient aucune valeur pour la colonne total_due! Que s'est-il passé ?

La raison pour laquelle cela se produit est assez simple. Comme le conducteur dont l'ID est 16877165 n'a pas d'amende impayée, la valeur stockée dans la colonne unpaid_fines pour ce conducteur a été, par défaut, traitée comme NULL.

Comme nous l'avons déjà mentionné, toute expression impliquant une valeur NULL renvoie NULL. Ainsi, l'ajout de fine_value à unpaid_fines a produit par inadvertance NULL, ce qui s'est traduit par une cellule vide dans le tableau de résultats.

SQL Coalesce à la rescousse

Pour résoudre ce problème, nous allons utiliser la fonction SQL COALESCE, qui renvoie la première valeur non NULL d'une liste d'arguments que nous lui donnons. La fonction peut prendre autant d'arguments que nécessaire. Dans ce cas, cependant, nous ne transmettrons que deux arguments à l'appel de la fonction : unpaid_fines et une valeur de 0. Voici la requête :

SELECT 	driver_id,
		fine_value 						as "fine_amount", 
		fine_value + COALESCE(unpaid_fines,0)	as "total_due" 
FROM 	fines;

Expliquons maintenant comment et pourquoi cela fonctionne.

Lorsque COALESCE(unpaid_fines, 0) est évalué, si unpaid_fines est NULL pour un enregistrement particulier de la table, SQL ignorera cette valeur et passera au prochain argument que nous avons transmis à la fonction. Rappelons que dans ce contexte, la valeur NULL indique qu'un conducteur n'a pas d'amende impayée.

Dans ce cas, la valeur suivante que nous avons fournie après unpaid_fines est 0. Comme il s'agit de la première valeur non NULL que nous avons rencontrée en allant de gauche à droite pour le premier conducteur, l'appel de fonction renverra simplement 0. C'est précisément ce que nous voulons : si un conducteur n'a pas d'amendes impayées, nous devons simplement considérer que ses amendes impayées sont nulles !

Cependant, si un conducteur a eu des amendes impayées, la valeur stockée sous unpaid_fines pour cet enregistrement particulier ne sera pas NULL. Dans ce cas, l'appel à COALESCE(unpaid_fines, 0) renverra la valeur stockée sous unpaid_fines, ce qui nous permettra d'ajouter un montant impayé non nul à l'amende actuelle du conducteur pour obtenir le montant total dû.

Délais de paiement manqués : Frais supplémentaires encourus

Rappelez-vous que nous avons dit que si l'amende n'est pas payée dans les 30 jours, le montant que vous devez payer sera multiplié par le taux d'infraction. Dans la prochaine requête utilisant la fonction SQL COALESCE, nous tiendrons compte du site offense_rate dans nos calculs. Regardez :

SELECT driver_id,
	     fine_value as "fine_amount", 
				COALESCE(unpaid_fines,0) as "unpaid_fines",
	     fine_value + COALESCE(unpaid_fines,0)	as "total_due" ,
	     fine_value * ofense_rate + COALESCE(unpaid_fines,0)	as "total due_after_30_days" 
FROM  fines;

Cette fois-ci, nous avons obtenu une valeur vide dans la quatrième ligne sous la colonne total_due_after_30_days. J'espère que vous êtes en mesure de déterminer ce que nous devons ajouter à notre requête pour qu'elle fonctionne correctement.

Comme précédemment, nous devons faire appel à COALESCE. Cette fois, nous devons utiliser SQL COALESCE pour calculer le multiplicateur fin. Cela permettra de prendre en compte la possibilité que l'infraction du conducteur n'ait pas été spécifiée, auquel cas l'amende devra être traitée comme si l'infraction avait été classée dans la catégorie A (pas de sanction).

Naturellement, nous transmettrons une valeur de 1 comme deuxième argument. Ainsi, si la valeur indiquée sur offense_level est NULL pour un conducteur donné, la valeur de l'amende restera la même (elle sera multipliée par 1).

Voici la requête mise à jour :

SELECT driver_id,
	     fine_value 	as "fine_amount", 
                COALESCE(unpaid_fines,0)	as "upaid_fines",
	     fine_value + COALESCE(unpaid_fines,0)	as "total_due" ,
	     fine_value * COALESCE(offense_level, 1) + COALESCE(unpaid_fines,0)	as "total_due_after_30_days" 
FROM  fines;

Pour notre dernier exemple, nous allons faire une concaténation de chaînes de caractères pour observer ce qui se passe lorsque l'une des colonnes avec lesquelles nous travaillons contient une valeur NULL. Voici la requête :

SELECT	driver_id,
		reason ||' - '|| offense_level  			as “reason_and_severity”,
		fine_value + COALESCE(unpaid_fines,0)	as "total_due" 
FROM		fines

Si nous regardons le quatrième enregistrement, nous remarquerons à nouveau une valeur vide, cette fois dans la colonne reason_and_severity. Une fois encore, c'est parce que nous avons tenté d'évaluer une expression impliquant une valeur NULL.

Si nous utilisons à la place la fonction SQL COALESCE, nous pouvons spécifier une valeur sur laquelle nous pouvons nous rabattre si nous rencontrons NULL. Pour nos besoins, nous allons passer la chaîne 'No Band' comme valeur par défaut. Voici la requête mise à jour, qui s'exécute comme prévu :

SELECT	driver_id,
		reason ||' - '|| COALESCE(offense_level, 'No Band')  	as "reason_and_severity",
		fine_value + COALESCE(unpaid_fines,0)		as "total_due" 
FROM		fines

Réflexions finales sur la fonction SQL COALESCE

Tous les gestionnaires de bases de données n'ont pas accès à la fonction SQL COALESCE, mais toutes les bases de données offrent des fonctions similaires pour travailler avec des valeurs NULL. Des fonctions telles que IFNULL(), NVL() et ISNULL(), entre autres, vous permettent de détecter, remplacer ou transformer les valeurs NULL.

Si vous souhaitez en savoir plus sur les fonctions SQL, consultez notre cours LearnSQL.fr Fonctions SQL standards , où nous abordons de nombreuses opérations et fonctions SQL essentielles.