NULLs et gestion des données manquantes en SQL

La gestion des données manquantes (i.e. NULLs) en SQL peut être un défi. Les NULLs peuvent poser de nombreux pièges, surtout si vous ne comprenez pas comment ils fonctionnent. Dans cet article, nous allons parler de la gestion des NULL en SQL. Nous expliquerons également comment éviter les erreurs les plus courantes lorsque l'on travaille avec des NULL.

L'absence de données dans votre base de données est une réalité inévitable. Il existe de nombreuses raisons pour lesquelles des données manquent dans votre base de données : les données complètes ne sont pas encore disponibles, les utilisateurs fournissent des informations incomplètes, des modifications du schéma de la base de données, des dysfonctionnements de la base de données, des erreurs humaines, et bien d'autres encore. Travailler avec des données manquantes dans des requêtes SQL est un défi. Dans cet article, je présenterai les pièges les plus courants associés aux données incomplètes dans les requêtes SQL.

Si vous souhaitez vous entraîner à travailler avec des données manquantes ou inconnues dans SQL, je vous recommande notre pisteLa pratique du SQL . Au moment où j'écris ces lignes, elle contient 10 cours pour vous permettre de pratiquer le langage SQL - et nous continuons d'en ajouter ! Les cours sont divisés en sections et beaucoup d'entre eux ont une section dédiée à NULL. Je recommande tout particulièrement ces cours pour s'entraîner à manipuler NULL en SQL :

• Basic La pratique du SQL: A Store
• La pratique du SQL: Université
• Pratique SQL de base : Des courses aux requêtes !
• Basic La pratique du SQL: Blog et données de trafic

Qu'est-ce que NULL en SQL ?

En SQL, NULL représente une valeur manquante ou indéfinie dans une base de données. Il est utilisé pour indiquer que la valeur d'un champ est absente ou inconnue.

Un problème courant chez les débutants avec NULLs est que votre base de données n'affiche souvent pas NULLs de manière explicite. Dans le tableau ci-dessous, les champs like pour l'ID de poste 1, location pour l'ID de poste 2 et views pour l'ID de poste 3 sont tous NULL. Cependant, une base de données n'affiche pas explicitement NULL, mais un champ vide. Vous devez savoir que NULL est une possibilité de deviner que ces champs sont NULL.

Il est toutefois important de noter que NULL est différent d'une chaîne vide ou d'un zéro. NULL est l'absence de valeur ; cela signifie que la valeur est inconnue. Comme nous le verrons bientôt, NULL n'est pas une valeur réelle. Beaucoup de gens disent ou écrivent quelque chose comme "Il y a NULL valeurs dans ce champ", mais c'est techniquement incorrect.

Lors de la conception d'une table dans une base de données, un concepteur de base de données peut décider que NULL n'est pas autorisé pour un champ particulier. Pour ce faire, il définit une contrainte NOT NULL pour une colonne lors de la création de la table. Les clés primaires (colonnes qui identifient les lignes d'une table) sont également NOT NULL par défaut.

La meilleure pratique consiste à éviter NULLs dans la conception de votre base de données. Ainsi, autant de colonnes que possible doivent être définies comme NOT NULL. Il est préférable de n'autoriser NULLs qu'en cas de stricte nécessité. Toutefois, les données peuvent être désordonnées et il est parfois impossible d'éviter NULL.

L'ensemble de données

Dans cet article, nous utiliserons les données de la table posts. Imaginez que vous récupériez des données de votre plateforme de médias sociaux préférée pour les analyser. Les données de cette plateforme sont stockées dans cette table. Voici les champs :

• id - L'ID du message.
• title - Le titre du message.
• url - L'URL (adresse web) du message.
• creator - Le nom du créateur du message.
• published - La date de publication du billet.
• type - Le type de message.
• location Le lieu de publication de l'article ; il peut s'agir de NULL si le lieu est inconnu ou non pertinent.
• views - Le nombre de vues de chaque message ; cette valeur peut être NULL si le créateur choisit de ne pas rendre ces données visibles au public.
• likes - Le nombre de likes accordés à l'article ; il peut s'agir de NULL si le créateur choisit de ne pas rendre cette donnée publique.
• dislikes - Le nombre de personnes qui n'ont pas aimé l'article ; il peut s'agir de NULL car la plateforme n'affiche plus ces données. Toutefois, il est possible que nous disposions de ces données pour des articles plus anciens.

Maintenant que nous avons examiné les données, utilisons-les pour comprendre NULL.

Opérateurs de comparaison avec NULL

Même quelque chose d'apparemment simple comme la façon dont NULL se comporte lorsqu'il est utilisé avec des opérateurs de comparaison peut être contre-intuitif et surprenant pour les débutants.

Supposons que nous voulions savoir combien de lignes du tableau posts ont des champs views manquants. Voici un exemple :

SELECT COUNT(*)
FROM posts
WHERE views = NULL;

Résultat : 0 ligne

Super, zéro ligne avec des vues manquantes. Incroyable ! Combien d'entre elles ne sont pas manquantes, alors ?

SELECT COUNT(*)
FROM posts
WHERE views <> NULL;

Résultat : 0 ligne

Également zéro ? Il doit y avoir un problème.

Le problème ici est que vous devez utiliser les opérateurs IS NULL and IS NOT NULL pour tester NULLs:

SELECT COUNT(*)
FROM posts
WHERE views IS NULL;

Résultat : 34 lignes

SELECT COUNT(*)
FROM posts
WHERE views IS NOT NULL;

Résultat : 66 lignes

Pourquoi ces résultats sont-ils si différents des deux requêtes précédentes ?

Logique à trois valeurs en SQL

Les problèmes de comparaison pour NULL proviennent du fait que NULL n'est pas une valeur réelle. C'est le point le plus important que vous devez comprendre pour travailler efficacement avec NULL.

SQL utilise une logique à trois valeurs. Chaque condition logique en SQL peut avoir l'une des trois valeurs suivantes : TRUE, FALSE ou NULL. NULL signifie ici "je ne sais pas". Chaque fois que vous utilisez une condition dans WHERE, SQL renvoie les lignes pour lesquelles la condition logique dans WHERE est TRUE. Il ne renvoie pas les lignes pour lesquelles la condition est FALSE (comme vous vous y attendez) et pour lesquelles la condition est NULL (ce qui n'est pas toujours le cas).

Examinons un exemple du fonctionnement de WHERE:

SELECT COUNT(*)
FROM posts
WHERE views < 100;

Cette requête compte les messages pour lesquels la colonne views a une valeur et cette valeur est inférieure à 100. Les articles dont le nombre de vues est inconnu ne sont pas comptabilisés. Cela peut être contre-intuitif : lorsque vous affichez les données relatives aux messages, vous constatez que de nombreux champs relatifs aux vues sont vides. Vous pensez intuitivement que ce vide est sûrement inférieur à 100. Mais le vide signifie que la base de données ne dispose pas des données ; puisqu'elle ne dispose pas des données, elle ne peut pas dire si c'est inférieur à 100 ou non. Les lignes dont le champ views est vide ne sont donc pas comptées.

SELECT COUNT(*)
FROM posts
WHERE views < likes;

Cette requête renvoie les lignes où le nombre de vues et le nombre de likes sont tous deux connus et où la valeur views est inférieure à la valeur likes. Elle ne renvoie pas les lignes où la valeur views est inconnue ou la valeur likes est inconnue.

Si vous souhaitez inclure dans le résultat les articles dont les champs views sont vides, vous devez explicitement filtrer pour NULL:

SELECT COUNT(*)
FROM posts
WHERE views < likes OR views IS NULL;

Revenons aux requêtes avec lesquelles nous avons commencé :

SELECT COUNT(*)
FROM posts
WHERE views = NULL;

SELECT COUNT(*)
FROM posts
WHERE views <> NULL;

La condition WHERE compare la colonne views avec NULL. Cependant, NULL signifie "Je ne connais pas la valeur". La base de données ne peut pas dire si views est égal (ou non) à une valeur inconnue. Elle peut l'être ou non, donc la base de données dit NULL - c'est-à-dire "Je ne sais pas" - et ces lignes ne sont pas renvoyées dans le résultat.

Utilisation de NULL dans les fonctions SQL

NULL L'utilisation de NULL est également problématique dans les opérateurs et les fonctions. La plupart des fonctions et des opérateurs renvoient NULL lorsqu'ils reçoivent NULL comme argument.

Exemple 1 : Imaginons que nous voulions retourner l'en-tête de chaque article. (L'en-tête se compose du titre, d'un tiret et de l'emplacement). Voici la requête :

SELECT 
  title || ‘ - ‘ || location
FROM posts;

Résultat :

La requête renvoie NULL si title ou location sont manquants. L'article portant l'ID 2 a pour résultat NULL, puisque son location est inconnu.

Exemple 2 : Il en va de même pour les opérateurs arithmétiques. Supposons que vous souhaitiez calculer l'engagement d'un message comme étant la somme de likes et dislikes:

SELECT 
  title, 
  likes, 
  dislikes, 
  likes + dislikes AS engagement
FROM posts;

Résultat :

Si l'un des champs likes ou dislikes est NULL, la valeur renvoyée dans la colonne engagement est également NULL.

Exemple 3 : Le même comportement est observé avec des fonctions régulières, comme UPPER():

SELECT 
  title, 
  UPPER(creator)
FROM posts;

Le créateur de l'article "Eco-Friendly Living Tips" est inconnu, et l'expression UPPER(creator) renvoie donc NULL.

Fonctions qui fonctionnent avec les NULL

Heureusement, il existe des fonctions SQL qui permettent d'atténuer ces problèmes avec NULL.

COALESCE

COALESCE() prend plusieurs arguments et renvoie la première valeur nonNULL de ses arguments. Elle est généralement utilisée pour remplacer NULL par une valeur significative dans une autre fonction ou expression. Nous pourrions modifier notre requête d'engagement comme suit :

SELECT 
  title, 
  likes, 
  dislikes, 
  COALESCE(likes, 0) + COALESCE(dislikes, 0) AS engagement
FROM posts;

Chaque fois que la valeur likes ou dislikes est NULL, la fonction COALESCE() la remplace par 0. La nouvelle valeur est utilisée dans le calcul et nous évitons les résultats NULL:

Vous pouvez également utiliser COALESCE() pour donner une étiquette significative à NULLs dans les résultats. La requête suivante remplace NULL par "Inconnu" dans l'ensemble des résultats ; le champ lui-même est toujours NULL dans la base de données :

SELECT 
  title, 
  COALESCE(location, ‘Unknown’) AS location
FROM posts;

Voici le résultat :

NULLIF

Une autre fonction fonctionnant avec NULL est NULLIF. Celle-ci est un peu bizarre : elle prend deux arguments et renvoie NULL si les arguments sont égaux. En pratique, vous utilisez NULLIF pour éviter la division par zéro :

SELECT 
  title, 
  likes / NULLIF(views, 0)
FROM posts;

Vous voulez calculer le rapport likes sur views pour les messages. Cependant, si la valeur de views est 0, vous risquez d'obtenir une erreur de division par zéro. Pour éviter cela, vous utilisez la fonction NULLIF. Si views est égal à zéro, alors NULLIF(views, 0) renvoie NULL.

NULL La division de NULL donne le résultat de et évite l'erreur de division par zéro. Ici, nous tirons parti de NULL en cascade sur les résultats des calculs.

NULL dans les fonctions GROUP BY et Aggregate

Lorsque l'on travaille avec des valeurs manquantes, il est bon de savoir comment NULL se comporte dans les fonctions GROUP BY et d'agrégation.

NULL et GROUP BY

GROUP BY regroupent les lignes en fonction des valeurs communes d'une colonne donnée. Vous pouvez ensuite appliquer des fonctions d'agrégation à chaque groupe et calculer des résumés pour chaque groupe. Cette requête compte le nombre de messages pour chaque lieu :

SELECT 
  location, 
  COUNT(*)
FROM posts
GROUP BY location;

Avec GROUP BY, toutes les lignes dont la colonne contient NULL sont placées dans un groupe ; vous calculez des statistiques pour ce groupe comme pour n'importe quel autre.

Dans notre exemple, tous les messages dont la localisation est inconnue sont placés dans un seul groupe :

NULL et les fonctions d'agrégation

D'une manière générale, les fonctions d'agrégation ignorent également NULLs. Mais il existe des variantes importantes dans la façon dont certaines fonctions d'agrégation traitent NULLs.

Les fonctions SUM(), MIN(), MAX() ignorent toutes NULLs:

SELECT 
  type, 
  SUM(views), 
  MIN(views), 
MAX(views)
FROM posts
GROUP BY type;

La fonction SUM() traite NULL comme s'il s'agissait de 0, de sorte que NULL n'influence pas le résultat de SUM. Mais si toutes les valeurs du groupe sont NULL, le résultat de SUM() est NULL. Dans notre exemple, nous ne disposons d'aucune donnée de vue pour le groupe infographie, de sorte que la somme est NULL pour ce groupe.

Les fonctions MIN() et MAX() ignorent également NULL; elles renvoient les valeurs minimales et maximales des valeurs connues. Ce n'est que si toutes les valeurs du groupe sont NULL que ces fonctions renvoient NULL. Notre groupe d'infographies ne contient aucune donnée, de sorte que les valeurs minimales et maximales sont indiquées comme étant NULL.

La fonction COUNT() est un peu plus subtile lorsqu'il s'agit de traiter NULL. Il existe trois variantes de la syntaxe COUNT: COUNT(*), COUNT(expression), COUNT(DISTINCT). Vous pouvez les découvrir dans notre article Quelle est la différence entre COUNT(*), COUNT(1), COUNT(column) et COUNT(DISTINCT) ?

SELECT 
  COUNT(*), 
  COUNT(location), 
  COUNT(DISTINCT location)
FROM posts;

L'expression COUNT(*) compte toutes les lignes du jeu de résultats. Il y a 100 messages dans notre posts notre tableau, cette expression renvoie donc 100.

L'expression COUNT(location) compte les valeurs nonNULL dans la colonne donnée. Dans notre exemple, elle comptera les messages dont la colonne location n'est pas NULL. Elle ignore les messages dont la localisation est inconnue.

Enfin, l'expression COUNT(DISTINCT location) compte les valeurs distinctes nonNULL; en d'autres termes, elle ignore les valeurs répétées. Il comptera le nombre d'emplacements différents dans notre tableau posts tableau.

La fonction AVG() ignore NULL. C'est généralement ce que vous attendez. Il convient toutefois d'être prudent lorsque l'on utilise AVG() avec COALESCE(). Toutes les variantes suivantes renvoient des valeurs différentes : AVG(views), AVG(COALESCE(views,0)), COALESCE(AVG(views)).

NULL et JOIN

Vous devez vous souvenir de NULL lorsque vous utilisez JOIN, en particulier avec OUTER JOINs comme LEFT JOIN ou FULL JOIN. Il peut y avoir NULLs dans les colonnes provenant de la bonne table.

Imaginons que nous ayons une autre table, commentsqui contient des données sur les commentaires des articles. Elle contient des informations dans les colonnes suivantes :

• id - Un identifiant unique pour chaque commentaire.
• post_id - L'identifiant de l'article sur lequel porte le commentaire.
• content - Le contenu du commentaire
• author - L'auteur du commentaire
• upvotes - Le nombre de votes positifs attribués à ce commentaire ; il peut s'agir du nombre de votes négatifs attribués à ce commentaire. NULL
• downvotes - Le nombre de votes négatifs attribués à ce commentaire ; cela peut être NULL

Nous voulons compter le nombre de commentaires pour chaque article, mais nous voulons inclure les articles sans commentaires dans les résultats. Vous devez utiliser posts LEFT JOIN comments pour inclure tous les posts.

Ensuite, vous devez vous rappeler d'utiliser COUNT(comments.id) et non COUNT(*) pour compter les commentaires. Ce dernier comptera les lignes sans tenir compte du fait que la ligne est liée ou non au commentaire. La bonne méthode consiste à utiliser COUNT(comments.id). S'il n'y a pas de commentaires, l'identifiant est NULL et il n'est pas compté.

SELECT 
  posts.title, 
  COUNT(comments.id)
FROM posts
LEFT JOIN comments
  ON posts.id = comments.post_id;

Un autre problème à garder à l'esprit est que la condition WHERE peut parfois "annuler" la condition OUTER JOIN. Dans la requête ci-dessous, nous voulons trouver les commentaires dont upvotes est supérieur à 100. Si l'article contient des commentaires avec un nombre inconnu de votes positifs, ces commentaires ne seront pas inclus dans le résultat. Si l'article n'a que des commentaires avec un nombre inconnu de votes positifs, l'article ne sera pas inclus du tout - malgré l'utilisation de LEFT JOIN. La condition WHERE "annulera" effectivement la condition LEFT JOIN:

SELECT 
  posts.title, 
  comments.content
FROM posts
LEFT JOIN comments
  ON posts.id = comments.post_id
WHERE upvotes > 100;

NULL dans ORDER BY

Lorsque vous créez un rapport, vous souhaitez souvent trier les données dans un ordre spécifique, par exemple par ordre alphabétique, croissant ou décroissant. Comment NULL se comporte-t-il lors d'un tri ?

Lorsque vous effectuez un tri en fonction d'une colonne contenant NULL, les lignes contenant NULL apparaissent en premier ou en dernier, selon le moteur de base de données que vous utilisez. Par exemple, MySQL place NULLs en premier pour les tris par ordre croissant, tandis qu'Oracle les place en dernier pour les tris par ordre croissant. Vous pouvez vérifier le comportement par défaut de votre base de données dans sa documentation.

Si vous ne vous souvenez pas du comportement par défaut ou si vous ne l'aimez pas, vous pouvez utiliser les opérateurs NULLS FIRST ou NULLS LAST après ORDER BY pour spécifier le comportement souhaité :

SELECT 
  title, 
  views
FROM posts
ORDER BY views DESC NULLS LAST;

Cela permet de s'assurer que toutes les lignes contenant un NULL sont listées en dernier :

Vous pouvez lire en détail comment NULL fonctionne avec ORDER BY dans Comment ORDER BY et NULL fonctionnent ensemble en SQL.

Gérer les données manquantes avec NULL en SQL !

La gestion de NULL et des données manquantes en SQL est une compétence importante pour toute personne travaillant avec des données. Comprendre les nuances de NULL, son comportement dans différentes opérations et les meilleures pratiques pour gérer les données manquantes garantit la précision de vos requêtes et la fiabilité de vos analyses.

Pour approfondir votre compréhension de SQL, envisagez d'acheter notre packageTout à vie SQL. Cette offre de paiement unique fournit un accès à vie à tous les cours SQL actuels et futurs. Les cours couvrent tout, des requêtes de base au SQL avancé ; ce que vous apprenez vous sera utile à tous les niveaux de votre carrière. N'oubliez pas non plus de consulter notre siteLa pratique du SQL qui propose 10 cours de pratique SQL et plus de 1 000 exercices. Améliorez vos compétences à long terme avec LearnSQL.fr!