Comment faire face aux NULLs SQL : Fonction COALESCE

Il est inévitable que certaines données de la base de données n'aient aucune valeur, ce qui, en SQL, est représenté par le mot-clé NULL. "Aucune valeur" est ici différent de zéro, false ou une chaîne vide (mais avec des exceptions ! Dans Oracle NULL est identique à une chaîne de caractères de longueur nulle). En jouant avec la base de données, le traitement de NULLs devient de plus en plus gênant, c'est pourquoi La norme MySQL fournit de l'aide avec certaines fonctions, telles que COALESCE.

Présentons la fonction COALESCE() présente la fonction

Définition

COALESCE La fonction COALESCE renvoie un premier argument non NULL à partir de la liste d'arguments passée. Sa syntaxe est la suivante :

COALESCE(x, y, … , n)

Ce qui est important en plus, c'est que :

• Elle prend au moins deux arguments.
• Les expressions de la fonction COALESCE doivent être évaluées au même type de données (par exemple, l'instruction SQL SELECT COALESCE (1, 'aa', NULL, 23); produira une erreur).
• Si tous les arguments énumérés sont évalués à NULL, la fonction renvoie également NULL.

COALESCE est essentiellement une déclaration raccourcie de CASE présentée ci-dessous :

CASE
WHEN x IS NOT NULL THEN x
WHEN y IS NOT NULL THEN y
WHEN ... IS NOT NULL THEN …
ELSE n
END

COALESCE - exemples d'utilisation

Maintenant, regardez le prototype présenté ci-dessous du modèle de base de données universitaire. Je vais vous montrer quelques exemples qui nécessitent la fonction COALESCE et qui font référence à des tables de ce modèle.

Utilisation de COALESCE avec concaténation de valeurs

Jetez un coup d'œil à la table student du modèle de base de données universitaire présenté :

Il se peut que la personne en question n'ait pas le deuxième prénom, par conséquent, certains enregistrements de la colonne middle_name peuvent être des NULLcomme dans l'exemple ci-dessous.

Essayons de concaténer le prénom, le second prénom et le nom de l'étudiant. Celui-ci fonctionne dans PostgreSQL, Oracle.

SELECT
  first_name || ' ' || middle_name || ' ' || last_name AS full_name
FROM student;

La table de résultat contient une colonne full_name avec les chaînes concaténées.

Remarquez que seul un enregistrement a un nom complet correct. Les autres sont NULLs, parce que le deuxième prénom était aussi NULL (en concaténant les chaînes avec la valeur NULL, le résultat est aussi NULL).

Un tel résultat n'est pas très joli. Le résultat n'affiche pas le nom complet des étudiants qui n'ont pas de deuxième prénom. C'est ici que la fonction COALESCE() vient à la rescousse. Grâce à elle, nous pouvons remplacer les champs NULL par une chaîne vide, par exemple. Maintenant, la nouvelle requête se présente comme suit :

SELECT
  first_name || COALESCE(' ' || middle_name || ' ', ' ') || last_name AS full_name
FROM student;

Le résultat est maintenant complet 🙂

MySQL, MS SQL Server utilise pour la concaténation la fonction CONCAT() ou l'opérateur '+'.

De même, la requête dans MySQL ou MS SQL Server se présentera comme suit :

SELECT
  CONCAT(first_name, ' ', middle_name, ' ', last_name)
FROM student;

... et les enregistrements qui ont NULLs auront aussi :

Avec la fonction COALESCE, les NULLs qui apparaissent dans la colonne middle_name seront remplacés.

SELECT
  CONCAT(first_name, ' ', COALESCE(middle_name, ''), ' ', last_name)
FROM student;

Utilisation de PostgreSQL COALESCE avec des fonctions d'agrégation

Eh bien, NULLs peut effectivement sembler très problématique. Imaginons une situation différente. Je veux savoir quelle est la note moyenne d'un cours. Cela semble très facile, non ? Mais, attendez, attendez... même s'il s'agit d'une requête simple, nous pouvons rencontrer certains inconvénients. Le problème concerne certaines fonctions d'agrégation SQL comme SUM(), MAX() ou MIN().

EXEMPLE : Utilisation de COALESCE avec la fonction AVG

Prenons un exemple avec la table student_courses.

Comme vous le voyez ci-dessous, les étudiants qui sont inscrits au cours d'id 1 n'ont pas encore de note.

La requête, qui renvoie la note moyenne pour le cours, se présente comme suit :

SELECT
  course_instance_id,
  AVG(grade) AS average_grade_in_course
FROM student_courses
GROUP BY course_instance_id;

Pour la fonction AVG(), les champs nonNULL sont additionnés et la somme est divisée par le nombre de champs nonNULL. Ainsi, si tous les enregistrements ont NULL dans la colonne note, la note moyenne sera également NULL.

Le tableau de résultats ci-dessous :

Dans ce cas, nous voulons insérer une autre valeur, comme 0 (la note peut être une valeur de 2 à 5, donc la moyenne 0 peut nous indiquer que les étudiants n'ont pas de notes).

COALESCE() La fonction est fournie avec une aide pour remplacer une valeur. La même requête avec COALESCE sera :

SELECT
  course_instance_id,
  COALESCE(AVG(grade), 0) AS average_grade_in_course
FROM student_courses
GROUP BY course_instance_id;

Et maintenant nous pouvons obtenir le tableau de résultat comme :

EXEMPLE : Utilisation de COALESCE avec la fonction SUM()

Il s'agit d'un exemple similaire. Supposons une situation différente. Nous voulons compter le nombre total d'heures d'absence pour chaque étudiant. Jetez un coup d'œil à la partie sélectionnée du modèle :

Les données du tableau student_course_attendance se présentent comme suit.

Alors que les données student_courses sont :

Examinez attentivement les données de ces tableaux. Comme vous le voyez, deux étudiants sont inscrits au cours en question, alors que seul l'un d'entre eux a manqué des cours.

La requête, qui calculera la somme du nombre total d'heures d'absence pour un course_id = 1 sera pour chaque étudiant est :

SELECT
  student_id,
  SUM(absence_hours) AS total_absence_hours
FROM student_courses sc
LEFT JOIN student_course_attendance sca
  ON sc.id = sca.student_courses_id
WHERE course_instance_id = 1
GROUP BY 1;

... et s'évalue dans un tableau de résultats :

Le nombre total d'heures d'absence a été évalué à NULL, car en effet, cet étudiant n'a manqué aucun cours dans cette instance de cours. Il n'y avait aucun enregistrement pour cet étudiant dans la table student_course_attendance, et la fonction SUM() a retourné NULL. Nous pouvons bien sûr sécuriser cette situation en utilisant la fonction COALESCE fonction.

La nouvelle requête est la suivante :

SELECT
  student_id,
  COALESCE(SUM(absence_hours), 0)  AS total_absence_hours
FROM student_courses sc
LEFT JOIN student_course_attendance sca
  ON sc.id = sca.student_courses_id
WHERE course_instance_id = 1
GROUP BY 1;

Et le résultat est maintenant :

Abandonnons maintenant le modèle de l'université et regardons l'exemple différent.

Utilisation de COALESCE dans la création de tableaux PIVOT

Ce cas d'utilisation sera présenté pour la base de données PostgreSQL. Notez que d'autres bases de données comme Oracle et MS SQL Server ont leurs fonctions équivalentes pour créer des tableaux croisés dynamiques.

Regardez le tableau ci-dessous. Il s'agit du résultat d'une requête, qui indique le total des ventes pour chaque marque au cours d'un mois donné.

Je veux transposer les lignes en colonnes (créer un tableau croisé dynamique). Le tableau de résultat devrait avoir des colonnes : brand_id, jan, feb, ..., dec. Remarquez que le produit de la marque en question n'a pas été vendu tous les mois. Jetons un coup d'œil à l'exemple Postgres.

SELECT brand_id, jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec

FROM CROSSTAB (
  'SELECT year, month, qty FROM sales ORDER BY 1',
  'SELECT m FROM generate_series(1,12) m'
) AS (
   year int,
   "jan" int,
   "feb" int,
   "mar" int,
   "apr" int,
   "may" int,
   "jun" int,
   "jul" int,
   "aug" int,
   "sep" int,
   "oct" int,
   "nov" int,
   "dec" int
);

Après avoir exécuté une telle requête, nous recevons :

Malheureusement, la vente n'est enregistrée que pour certains mois et le nombre total de ventes n'est collé que dans ces champs. Comme vous vous y attendiez probablement, le reste des champs contient NULLs.

Pour se débarrasser des NULLs indésirables, nous pouvons utiliser la fonction COALESCE, qui insère un 0 à la place de NULL si nécessaire. La requête réparée se présente comme suit :

SELECT 
  brand_id,
  COALESCE(jan, 0),
  COALESCE(feb, 0),
  COALESCE(mar, 0),
  COALESCE(apr, 0),
  COALESCE(may, 0),
  COALESCE(jun, 0),
  COALESCE(jul, 0),
  COALESCE(aug, 0),
  COALESCE(sep, 0),
  COALESCE(oct, 0),
  COALESCE(nov, 0),
  COALESCE(dec, 0)
FROM CROSSTAB(
  'SELECT year, month, qty FROM sales ORDER BY 1',
  'SELECT m FROM generate_series(1,12) m'
) AS (
   year int,
   "jan" int,
   "feb" int,
   "mar" int,
   "apr" int,
   "may" int,
   "jun" int,
   "jul" int,
   "aug" int,
   "sep" int,
   "oct" int,
   "nov" int,
   "dec" int
);

Résumé

NULLpeuvent rendre la vie problématique. Si vous n'avez pas encore fait l'expérience du mauvais côté des valeurs manquantes, vous allez certainement y être confronté. C'est une question de temps. Pour l'instant, souvenez-vous de la fonctionCOALESCE , qui vous aidera à vous débarrasser des NULLs indésirables.