SQL Server n'est pas si NULL !

Lors de la conception de bases de données relationnelles, l'un des pièges courants est l'utilisation excessive des colonnes nullable. Bien que cela puisse sembler pratique pour permettre la gestion de données manquantes, l'utilisation de NULL peut avoir des conséquences sur la robustesse et la fiabilité de vos données, en plus d'impacter la performance des requêtes.
En tant que développeur ou DBA, vous devez vous poser la question : comment organiser les tables et les relations pour minimiser (voire éliminer) les valeurs NULL ?

L'objectif de cet article est de discuter de l'importance d'éviter les colonnes NULLables, d'explorer les alternatives, et de montrer comment structurer les données relationnelles pour améliorer la performance et la cohérence des bases de données.

Prenons l'exemple d'une table Users, avec la struture suivante :

CREATE TABLE users (
    users_id   INT IDENTITY (1,1) NOT NULL,
    email VARCHAR(150) NOT NULL,
    password VARCHAR(60) NOT NULL,
    city VARCHAR(100) NULL

  CONSTRAINT PK_users PRIMARY KEY CLUSTERED (users_id),
);

Dans cette table, l'une des colonnes — city — est nullable. Cela signifie qu'un utilisateur peut exister dans la table sans que sa ville ne soit renseignée (avec une valeur NULL). À première vue, cela semble raisonnable, car tous les utilisateurs ne fournissent peut-être pas cette information. Mais voici où cela devient problématique.

1. Problème de la colonne nullable "city"

Lorsqu'on commence à vouloir extraire des données de la table, les NULL introduisent des cas particuliers qu'il faut gérer. Par exemple, si vous voulez récupérer tous les utilisateurs avec leurs informations de ville, vous devez prendre en compte ces valeurs NULL pour éviter des résultats incohérents, ou des erreurs dans les calculs.

Une première solution à ce problème consiste à retirer les informations concernant la ville de la table users et à les déplacer dans une table séparée, dédiée aux informations de localisation, par exemple users_localization.

Cela permet de structurer les données de manière plus propre. Désormais, si un utilisateur n'a pas renseigné sa ville, il n'aura simplement pas de ligne dans la table users_localization, plutôt qu'une valeur NULL. Cette séparation entre les données obligatoires et optionnelles permet de réduire la complexité des requêtes.
De plus, cette table utilisera la colonne users_id comme clé primaire et clé étrangère, en s'assurant qu'elle soit indexée de manière optimale avec un index clustered.

Voici comment définir cette table :

CREATE TABLE users_localization (
    users_id   INT NOT NULL,
    city       VARCHAR(100) NOT NULL,

    CONSTRAINT PK_users_localization PRIMARY KEY CLUSTERED (users_id),
    CONSTRAINT FK_users_localization FOREIGN KEY (users_id) REFERENCES users(users_id)
);

Dans cette conception :

Pourquoi cette approche ?

• Cela permet d'éviter les valeurs NULL dans la colonne city, car une localisation n'existe que si elle est renseignée.
• La colonne users_id dans users_localization sert de clé primaire et de clé étrangère, établissant une relation forte avec la table users.
• users_id est également un index clustered, ce qui améliore les performances lors des recherches ou des jointures sur cette colonne.

2. Premier essai : L'utilisation d' INNER JOIN

Avec cette nouvelle organisation, nous voulons maintenant récupérer les utilisateurs avec leur ville. Une solution classique consiste à utiliser l'INNER JOIN pour lier les deux tables. Cependant, il y a une subtilité importante ici : l'INNER JOIN ne retournera que les utilisateurs qui ont une ville associée dans users_localization.

Imaginons une requête simple comme celle-ci :

SELECT u.users_id, u.email, l.city
FROM users u
INNER JOIN users_localization l ON u.users_id = l.users_id;

Cette requête renvoie uniquement les utilisateurs qui ont une ville. Si un utilisateur n'a pas renseigné de ville, il ne sera pas inclus dans le résultat. Cela crée un filtre naturel qui n'affiche que les utilisateurs "complètement renseignés". C'est une bonne approche si nous sommes sûrs que tous les utilisateurs doivent avoir une ville.

Mais dans la réalité, tous les utilisateurs n'ont peut-être pas encore renseigné leur ville, et nous voulons peut-être afficher tous les utilisateurs, avec ou sans ville. Comment faire ?

3. La solution : <LEFT|RIGHT> OUTER JOIN ou OUTER APPLY

Pour gérer les utilisateurs sans ville tout en permettant de les lister, la solution la plus courante est d'utiliser un LEFT OUTER JOIN, qui permet d'afficher tous les utilisateurs, même si aucune correspondance n'existe dans users_localization.

Voici comment cette requête peut être écrite :

SELECT u.users_id, u.email, l.city
FROM users u
LEFT OUTER JOIN users_localization l ON u.users_id = l.users_id;

Dans cette requête, tous les utilisateurs sont affichés. Si un utilisateur n'a pas de ville, la colonne city retournera simplement NULL. Cela permet de signaler l'absence de données, mais sans exclure les utilisateurs de la liste.

Mais pourquoi ne pas aller encore plus loin ? Supposons que nous ayons besoin de plus de flexibilité dans la manière dont nous récupérons les données, ou que nous souhaitions exécuter des sous-requêtes pour chaque utilisateur. C'est là qu'entre en jeu OUTER APPLY.

4. (avancé) OUTER APPLY : La flexibilité ultime

OUTER APPLY permet de traiter chaque ligne de la table users individuellement, en lui appliquant une logique spécifique. C'est similaire à un LEFT OUTER JOIN, mais avec une flexibilité accrue car il permet d'exécuter des sous-requêtes ou d'appliquer des fonctions table pour chaque ligne.

Par exemple, si nous voulions récupérer la ville pour chaque utilisateur, mais avec la possibilité d'ajouter des logiques supplémentaires dans le futur, nous pourrions utiliser OUTER APPLY comme ceci :

SELECT u.users_id, u.email, l.city
FROM users u
OUTER APPLY (SELECT city FROM users_localization l WHERE u.users_id = l.users_id) l;

Ce code effectue une recherche dans users_localization pour chaque ligne de users, mais retourne tout de même les utilisateurs même si aucune ville n'est trouvée (la colonne city sera alors NULL).

L'intérêt ici est que OUTER APPLY permet de manipuler les sous-requêtes de manière dynamique et flexible, tout en offrant une solution élégante au problème des utilisateurs sans localisation.

Conclusion : Le bon usage des jointures et des vues

Dans cet article, nous avons exploré les différentes approches pour gérer la relation entre utilisateurs et localisation, tout en évitant l'utilisation de valeurs NULL. La solution consistant à séparer les données optionnelles dans une table à part permet d'assurer l'intégrité des données, et l'utilisation de jointures intelligentes (telles que INNER JOIN, LEFT OUTER JOIN et OUTER APPLY) permet de maintenir une flexibilité dans la manière de récupérer et d'afficher ces données.

Le choix entre un INNER JOIN (relation forte) et un OUTER APPLY dépendra de la manière dont vous souhaitez gérer les utilisateurs qui n'ont pas de ville associée. Dans ce cas, OUTER APPLY offre une flexibilité supplémentaire pour les cas où une logique conditionnelle serait nécessaire.

Enfin, l'utilisation de vues permet de simplifier l'accès aux données tout en cachant la complexité des jointures. Par exemple, une vue pourrait combiner les tables users et users_localization pour offrir une interface simple aux développeurs :

CREATE VIEW v_Users AS
SELECT u.users_id as UserId, u.email as Email, u.password as Password, l.city as City
FROM users u
LEFT OUTER JOIN users_localization l ON u.users_id = l.users_id;

Cette vue permet de simplifier les requêtes tout en encapsulant la logique de jointure. Elle utilise également un plan d'exécution optimisé, ce qui contribue à améliorer les performances des requêtes.

Bonus. Colonne booléenne nullable : un piège classique

Un autre piège très commun est l'utilisation de booléens nullable. Cela conduit souvent à une logique à trois états (True, False, NULL), ce qui complique la lecture et la compréhension du code, ainsi que les requêtes SQL. Cela introduit de la confusion pour les développeurs qui devront gérer des vérifications supplémentaires de valeurs nulles.