Enregistrements orphelins : les trouver en SQL et les tenir à l'écart
|
6
minute de lecture
Une ligne de paiement indique account_id = 884213. La table accounts ne contient aucun compte portant ce numéro. Aucune erreur, le chargement se termine au vert, et le lendemain matin il manque exactement ce paiement dans le rapport de l'agence, parce que le rapport joint les paiements aux comptes et que la jointure l'écarte sans bruit. Un enregistrement orphelin est une ligne enfant dont la valeur de clé étrangère n'a aucune ligne correspondante dans la table parente à laquelle elle fait référence.
Les enregistrements orphelins, ou lignes orphelines, apparaissent partout où les clés étrangères ne sont pas appliquées : entrepôts de données, couches de staging, réplicas, pipelines qui chargent les enfants avant les parents. Les trouver est une tâche SQL classique : l'anti-jointure. Il existe quatre façons courantes de l'écrire, et l'une d'elles répond « aucun orphelin » dès qu'un seul NULL apparaît.
Ce guide présente les modèles de requête, les pièges, et la manière de faire de la requête un contrôle exécuté à chaque chargement. Pour le concept lui-même, consultez Vos données retrouvent-elles encore leurs parents ? Comprendre l'intégrité référentielle.
Points clés à retenir
Les jointures internes masquent les enregistrements orphelins : les lignes enfants sans correspondance disparaissent du résultat au lieu de déclencher une erreur.
Trouvez les orphelins avec une anti-jointure :
LEFT JOIN … IS NULL,NOT EXISTSouEXCEPTsur les clés distinctes.Évitez
NOT INsur une colonne qui peut contenir des NULL : un seul NULL dans la sous-requête renvoie zéro ligne, ce qui ressemble à un résultat propre.Faites correspondre les clés composites sur toutes leurs colonnes à la fois, et décidez délibérément si une clé étrangère NULL est une erreur.
Une requête qu'il faut penser à lancer n'est pas un contrôle. Dans digna, la même anti-jointure est une règle Referential Integrity qui s'exécute à chaque inspection.
Table des matières
Qu'est-ce qu'un enregistrement orphelin ?
Pourquoi les jointures internes masquent-elles les enregistrements orphelins ?
Comment trouver les enregistrements orphelins en SQL ?
LEFT JOIN … IS NULL
NOT EXISTS
EXCEPT (MINUS) sur les clés distinctes
Pourquoi NOT IN ne renvoie-t-il aucune ligne en présence d'un NULL ?
Quel modèle d'anti-jointure utiliser ?
Comment gérer les clés composites et les clés étrangères NULL ?
Comment trouver les enregistrements orphelins dans les grandes tables ?
Et si la table parente se trouve dans une autre base de données ?
Comment transformer une requête de recherche d'orphelins en contrôle permanent ?
Les trouver une fois, puis les tenir à l'écart
Qu'est-ce qu'un enregistrement orphelin ?
Un enregistrement orphelin est une ligne d'une table enfant dont la clé étrangère pointe vers une clé parente qui n'existe pas : un paiement dont l'account_id ne figure pas dans accounts, ou une administration de médicament dont le product_code ne figure pas dans medications. La ligne peut être parfaitement valide en elle-même. C'est la relation qui est brisée.
Les orphelins se trouvent toujours du côté enfant ; un compte sans paiement est normal. Les causes habituelles sont banales : le parent a été supprimé ou n'a jamais été chargé, l'enfant est arrivé avant le chargement des données de référence du lendemain, ou les formats de clé diffèrent ('00884213' contre 884213, ou un espace en fin de valeur).
Les bases de données opérationnelles comme PostgreSQL ou Oracle appliquent les clés étrangères déclarées, mais leurs copies dans l'entrepôt de données ne reprennent généralement pas ces contraintes. La plupart des entrepôts de données cloud acceptent une déclaration FOREIGN KEY sans l'appliquer ; la documentation de BigQuery indique qu'il n'applique pas les contraintes de clé primaire et de clé étrangère. Nous expliquons dans un article distinct pourquoi Snowflake, BigQuery, Redshift et Databricks n'appliquent pas les clés étrangères.
Pourquoi les jointures internes masquent-elles les enregistrements orphelins ?
Une jointure interne ne renvoie que les lignes qui ont une correspondance des deux côtés ; une ligne enfant sans parent est donc tout simplement absente du résultat. Pas d'erreur, pas d'avertissement, pas de NULL à remarquer. Les totaux sont inférieurs à ce qu'ils devraient être, et rien dans le rapport lui-même ne révèle la différence.
Chaque paiement dont l'account_id est absent de accounts disparaît avant le SUM. Le moyen le plus rapide de voir l'écart est de compter des deux façons :
Si account_id est unique dans accounts, la différence correspond à vos orphelins plus les éventuelles lignes dont l'account_id est NULL.
Les clés déclarées mais non vérifiées peuvent aggraver la situation. Le planificateur de requêtes d'Amazon Redshift suppose que les clés déclarées sont valides, et AWS avertit que des clés invalides peuvent amener certaines requêtes à renvoyer des résultats incorrects.
Comment trouver les enregistrements orphelins en SQL ?
On trouve les enregistrements orphelins avec une anti-jointure : une requête qui renvoie les lignes enfants pour lesquelles aucune ligne parente correspondante n'existe. En SQL, on l'écrit sous la forme LEFT JOIN … WHERE parent_key IS NULL, NOT EXISTS ou EXCEPT sur les valeurs de clé distinctes. Sur des clés non NULL, les trois donnent le même résultat.
LEFT JOIN … IS NULL
Testez une colonne du parent qui ne peut pas être NULL dans une ligne appariée, idéalement la clé de jointure. Placez les conditions portant sur le parent dans la clause ON : WHERE a.status = 'ACTIVE' n'est jamais vrai pour une ligne sans correspondance, et la requête ne renverrait donc rien.
NOT EXISTS
Cette forme se lit comme la question elle-même. Les clés parentes en double ne multiplient pas les lignes, et les NULL dans accounts.account_id ne peuvent pas la fausser : une égalité avec NULL n'est jamais satisfaite.
EXCEPT (MINUS) sur les clés distinctes
Cette forme renvoie les valeurs de clé manquantes distinctes, pas les lignes. C'est souvent la meilleure première question : une poignée de comptes manquants peut expliquer des milliers de paiements orphelins. Oracle écrit traditionnellement cet opérateur MINUS ; BigQuery exige EXCEPT DISTINCT. Les opérateurs ensemblistes considèrent deux NULL comme égaux, une raison de plus de filtrer explicitement les clés NULL.
Pourquoi NOT IN ne renvoie-t-il aucune ligne en présence d'un NULL ?
NOT IN ne renvoie aucune ligne si sa sous-requête contient ne serait-ce qu'un seul NULL, car la logique à trois valeurs de SQL rend inconnue toute comparaison avec ce NULL. x NOT IN (1, 2, NULL) signifie x <> 1 AND x <> 2 AND x <> NULL. Le dernier terme n'est jamais vrai, donc la condition entière n'est jamais vraie.
Pour un paiement dont le compte existe, une comparaison est fausse et la ligne est correctement exclue. Pour un compte manquant, chaque comparaison avec une vraie clé est vraie, mais celle avec NULL est inconnue ; la condition est donc inconnue, et WHERE ne conserve que les lignes vraies. Le résultat est un ensemble vide, impossible à distinguer de « aucun orphelin ».
L'échec est silencieux, avec un faux feu vert, et les colonnes de clé des entrepôts de données ne sont souvent pas déclarées NOT NULL. Les paiements dont l'account_id est lui-même NULL ne sont jamais retenus non plus. Utilisez NOT EXISTS, ou ajoutez au minimum WHERE a.account_id IS NOT NULL dans la sous-requête.
Quel modèle d'anti-jointure utiliser ?
Utilisez NOT EXISTS par défaut pour les contrôles d'orphelins au niveau des lignes, EXCEPT lorsque vous voulez la liste des valeurs de clé manquantes, et LEFT JOIN … IS NULL lorsque vous voulez aussi le nombre de correspondances dans la même passe. N'utilisez NOT IN que lorsque les deux colonnes sont garanties non NULL.
Modèle | Renvoie | NULL dans la clé parente | Clé étrangère NULL dans l'enfant | Lisibilité | Performances typiques |
|---|---|---|---|---|---|
LEFT JOIN … IS NULL | Lignes enfants | Sûr | Signalée comme orpheline, sauf si filtrée | Familier ; l'intention se trouve dans la clause WHERE | Généralement planifié comme une anti-jointure |
NOT EXISTS | Lignes enfants | Sûr | Signalée comme orpheline, sauf si filtrée | Se lit comme la question | Généralement planifié comme une anti-jointure |
EXCEPT / MINUS | Valeurs de clé distinctes | Sûr | Renvoyée une fois comme NULL, sauf si filtrée | Court et clair pour des listes de clés | Dédoublonne les deux côtés ; adapté aux synthèses au niveau des clés |
NOT IN | Lignes enfants | Dangereux : un seul NULL renvoie zéro ligne | Exclue en silence | Se lit bien, mais induit en erreur | Correct sur des colonnes non NULL ; peut obtenir un plan moins bon sur des colonnes qui acceptent les NULL |
La plupart des optimiseurs planifient LEFT JOIN et NOT EXISTS de la même manière ; vérifiez le plan d'exécution sur votre plateforme.
Comment gérer les clés composites et les clés étrangères NULL ?
Pour une clé composite, faites correspondre toutes les colonnes de la clé ensemble, dans un seul prédicat, jamais colonne par colonne : une ligne est orpheline lorsque sa combinaison est absente, même si chaque valeur existe quelque part isolément. Les clés étrangères NULL exigent une décision délibérée avant de les compter : référence manquante, ou valeur légitimement vide.
Supposons que chaque hôpital possède son propre livret thérapeutique, identifié par (hospital_id, product_code) :
Deux contrôles séparés, « l'hôpital existe » et « le produit existe », réussissent tous deux pour un produit référencé uniquement à l'hôpital A et administré à l'hôpital B. Seul le prédicat combiné le détecte. EXCEPT gère naturellement les clés composites. Ne concaténez pas les clés en une seule chaîne : '1' || '23' et '12' || '3' entrent en collision.
Un paiement dont l'account_id est NULL ne pointe pas vers un compte manquant ; il ne pointe nulle part. Un paiement doit avoir un compte, tandis qu'une référence facultative comme referring_doctor_id peut être vide. Excluez les NULL de la requête de recherche d'orphelins et comptez-les séparément :
Comment trouver les enregistrements orphelins dans les grandes tables ?
Sur les grandes tables, comptez avant de lister, comparez les valeurs de clé distinctes plutôt que chaque ligne, et limitez le côté enfant au dernier chargement ou à la dernière partition. Gardez le côté parent complet : un paiement comptabilisé aujourd'hui peut faire référence à un compte ouvert il y a des années ; ne filtrez donc jamais le parent par date de chargement.
Comptez d'abord. Exécutez l'anti-jointure sous forme de
COUNT(*). Ne récupérez les lignes que si le nombre est non nul, et alors avec une limite.Comparez les clés distinctes. Réduisez d'abord le côté enfant à
SELECT DISTINCT account_id. Les clés distinctes sont généralement bien moins nombreuses que les lignes.Filtrez sur le dernier chargement. Limitez l'enfant à la partition la plus récente ou à la dernière
load_date, afin que le moteur puisse élaguer.Gardez la colonne de jointure comparable. Même type de données des deux côtés, pas de
CASTni deTRIMdans le prédicat, ce qui peut empêcher l'utilisation des index et l'élagage. Corrigez plutôt les formats au chargement ; consultez notre guide sur le nettoyage des données en SQL.Enregistrez le résultat. Conservez la date, le nombre de lignes évaluées et le nombre d'orphelins trouvés pour suivre la tendance.
Cette requête compte les clés manquantes, pas les lignes orphelines ; récupérez ensuite les lignes correspondant à ces clés.
Et si la table parente se trouve dans une autre base de données ?
Une anti-jointure ne fonctionne que si un même moteur de requête peut lire les deux tables. Si les paiements se trouvent dans l'entrepôt de données et les comptes dans la base du core banking, le SQL seul ne voit pas les deux : vous copiez alors un côté, utilisez un lien de base de données ou une requête fédérée, ou exécutez le contrôle dans un outil capable d'accéder aux deux connexions.
Une table de référence copiée est un pipeline de plus, avec son propre décalage : une copie périmée signale de faux orphelins ou passe à côté de vrais. Les liens de base de données dépendent de la prise en charge par la plateforme et exigent une validation de sécurité pour chaque nouvelle connexion.
Comment transformer une requête de recherche d'orphelins en contrôle permanent ?
Pour transformer une requête de recherche d'orphelins en contrôle permanent, exécutez-la à chaque chargement, enregistrez le nombre de lignes réussies et en échec, fixez un seuil d'échec et conservez les lignes en échec là où l'équipe responsable peut les voir. Dans digna, cela correspond à une seule règle Referential Integrity, configurée dans une boîte de dialogue, sans SQL à écrire.
digna Data Validation propose trois types de règles : Rule, Uniqueness et Referential Integrity. Le type référentiel correspond à l'anti-jointure de cet article : à partir de colonnes d'une source de données et d'un ensemble correspondant sur une autre, digna conserve les valeurs distinctes de l'autre table et fait échouer toute ligne sans correspondance. Le contrôle s'exécute dans votre base de données source ; vos données ne quittent jamais votre infrastructure. Depuis la Release 2026.01, il fonctionne entre différentes connexions de bases de données d'un même projet, sans répliquer les données.
Les captures d'écran ci-dessous utilisent les données de démonstration de digna pour Danubia Kliniken, un groupe hospitalier autrichien fictif.
Le 22 avril 2026, les services ont enregistré 82 administrations de Coavira 2.5 mg (code produit 3858646), qui ne figurait pas encore dans le référentiel produits de la pharmacie. Chaque rapport joignant les doses aux produits affichait 0 dose de ce produit, alors que le personnel infirmier en avait administré 82. La règle hc_product_in_master a échoué : 4 244 lignes sur 4 326 ont réussi. À la main, il vous faudrait :
La vue Invalid Records affiche ce jeu de résultats sans que personne n'ait à écrire la requête :
Invalid Records, filtré sur Failed : chaque dose orpheline avec l'hôpital, le service, le département, le code produit et le nom du médicament.
Configuration de la règle :
Configuration → source de données
hospital_medication_administrations→ onglet Data Validation → Add Rule. La boîte de dialogue Add Data Validation Rule s'ouvre.Saisissez un Name (
hc_product_in_master) et une Description.Réglez Type sur Referential Integrity et choisissez dans Attributes (attributs) la colonne
product_code.Sous must exist in (doit exister dans), choisissez la Data Source
hospital_medicationset ses Attributesproduct_code. Pour une clé composite, choisissez plusieurs colonnes des deux côtés, dans le même ordre ; les listes de longueurs différentes sont rejetées.Choisissez le Threshold Mode (mode de seuil) Absolute ou Relative et définissez l'Info threshold et le Warn threshold (ici : Absolute, Info 0, Warn 1). Enregistrez. La règle s'exécute à chaque inspection, planifiée ou à la demande.
La règle complète : deux listes d'attributs, une source de données cible et deux seuils.
Referential Integrity ignore les NULL, comme le filtre IS NOT NULL ci-dessus ; si la présence est obligatoire, ajoutez une règle distincte de type Rule avec product_code IS NOT NULL. Au-dessus de l'Info threshold, le statut est Uncertain ; au-dessus du Warn threshold, il est Failed. Avec Info à 0, le moindre orphelin fait donc sortir la règle de Passed. Les enregistrements en échec correspondent à la même requête avec la condition de réussite inversée ; ils sont exportables, et les résultats peuvent notifier l'équipe responsable des données.
Depuis la Release 2026.06, les règles peuvent aussi être gérées sous forme de code via le SDK Python (pip install digna-sdk). Pour la procédure complète, consultez notre article sur la configuration d'un contrôle d'intégrité référentielle dans digna ou la vidéo de configuration de 2 min 26.
Les trouver une fois, puis les tenir à l'écart
Pour enquêter sur les enregistrements orphelins : NOT EXISTS pour les lignes, EXCEPT pour les clés manquantes, jamais NOT IN sur une colonne qui peut contenir des NULL, et une décision délibérée sur les NULL. Les tenir à l'écart demande davantage : appliquer les clés étrangères là où la base de données le permet, charger les parents en premier, traiter explicitement les données de référence arrivant en retard et exécuter le contrôle référentiel à chaque chargement.
Pour voir ce contrôle s'exécuter sur vos propres tables, au sein de votre propre infrastructure, réservez une démo avec l'équipe digna.
Questions fréquentes
Comment trouver les enregistrements orphelins en SQL ?
Utilisez une anti-jointure qui renvoie les lignes enfants sans parent correspondant. La forme la plus robuste est NOT EXISTS avec une sous-requête corrélée sur la clé ; LEFT JOIN … WHERE parent_key IS NULL donne le même résultat. Filtrez d'abord les clés étrangères NULL et comptez-les dans un contrôle de complétude distinct.
Pourquoi NOT IN ne renvoie-t-il aucune ligne lorsque la sous-requête contient un NULL ?
À cause de la logique à trois valeurs. x NOT IN (1, 2, NULL) se développe en x <> 1 AND x <> 2 AND x <> NULL, et la dernière comparaison est inconnue, jamais vraie. WHERE ne conserve que les lignes vraies ; la requête ne renvoie donc rien, ce qui ressemble exactement à un résultat propre.
NOT EXISTS est-il plus rapide que LEFT JOIN IS NULL ?
En général, aucun des deux n'est plus rapide : la plupart des optimiseurs modernes planifient les deux comme une anti-jointure, et le choix se résume donc à la lisibilité. NOT IN est l'exception. Sur des colonnes qui acceptent les NULL, il peut obtenir un plan moins bon, et il ne renvoie aucune ligne lorsque la sous-requête contient un NULL.
Quelle est la différence entre un enregistrement orphelin et une clé étrangère NULL ?
Un enregistrement orphelin pointe vers une clé parente qui n'existe pas, tandis qu'une clé étrangère NULL ne pointe nulle part. Ils nécessitent des contrôles différents : l'intégrité référentielle pour les orphelins, et une règle IS NOT NULL lorsque la référence est obligatoire. C'est précisément pour cette raison que la règle Referential Integrity de digna ignore les NULL.
Comment contrôler automatiquement les enregistrements orphelins après chaque chargement ?
Transformez l'anti-jointure en un contrôle planifié, avec des seuils et des résultats enregistrés. Dans digna, il s'agit d'une seule règle Referential Integrity dans Data Validation : choisissez les colonnes, choisissez la source de données dans laquelle elles doivent exister, définissez les seuils Info et Warn, enregistrez. La règle s'exécute dans votre base de données à chaque inspection.
