Verwaiste Datensätze mit SQL finden (und dauerhaft fernhalten)
|
6
min. Lesezeit
Eine Zahlungszeile enthält account_id = 884213. In der Tabelle accounts gibt es kein solches Konto. Es tritt kein Fehler auf, der Ladevorgang endet grün, und dem Filialbericht am nächsten Morgen fehlt genau diese Zahlung, denn der Bericht verknüpft Zahlungen per Join mit Konten, und der Join verwirft sie stillschweigend. Ein verwaister Datensatz ist eine untergeordnete Zeile, deren Fremdschlüsselwert keine passende Zeile in der referenzierten übergeordneten Tabelle hat.
Verwaiste Datensätze tauchen überall dort auf, wo Fremdschlüssel nicht erzwungen werden: in Warehouses, Staging-Schichten, Replikaten und Pipelines, die untergeordnete vor übergeordneten Daten laden. Sie zu finden ist eine Standardaufgabe für SQL, der Anti-Join. Es gibt vier gängige Schreibweisen, und eine davon meldet „keine verwaisten Datensätze“, sobald ein einziger NULL-Wert auftaucht.
Dieser Leitfaden behandelt die Muster, die Fallstricke und wie Sie aus der Abfrage eine Prüfung machen, die bei jedem Ladevorgang läuft. Zum Konzept selbst lesen Sie Finden Ihre Daten noch ihre Eltern? Referenzielle Integrität verstehen.
Das Wichtigste in Kürze
Inner Joins verbergen verwaiste Datensätze: Untergeordnete Zeilen ohne Treffer verschwinden aus dem Ergebnis, statt einen Fehler auszulösen.
Finden Sie verwaiste Datensätze mit einem Anti-Join:
LEFT JOIN … IS NULL,NOT EXISTSoderEXCEPTauf eindeutigen Schlüsseln.Vermeiden Sie
NOT INauf einer Spalte, die NULL enthalten kann: Ein einziger NULL-Wert in der Unterabfrage liefert null Zeilen, was wie ein sauberes Ergebnis aussieht.Vergleichen Sie zusammengesetzte Schlüssel über alle Spalten gemeinsam und entscheiden Sie bewusst, ob ein Fremdschlüssel mit NULL ein Fehler ist.
Eine Abfrage, an deren Ausführung Sie denken müssen, ist keine Kontrolle. In digna ist derselbe Anti-Join eine einzige Referential-Integrity-Regel, die bei jeder Inspektion läuft.
Inhaltsverzeichnis
Was ist ein verwaister Datensatz?
Warum verbergen Inner Joins verwaiste Datensätze?
Wie finden Sie verwaiste Datensätze mit SQL?
LEFT JOIN … IS NULL
NOT EXISTS
EXCEPT (MINUS) auf eindeutigen Schlüsseln
Warum liefert NOT IN keine Zeilen, sobald ein NULL-Wert vorkommt?
Welches Anti-Join-Muster sollten Sie verwenden?
Wie gehen Sie mit zusammengesetzten Schlüsseln und Fremdschlüsseln mit NULL um?
Wie finden Sie verwaiste Datensätze in großen Tabellen?
Was, wenn die übergeordnete Tabelle in einer anderen Datenbank liegt?
Wie machen Sie aus einer Abfrage nach verwaisten Datensätzen eine dauerhafte Prüfung?
Einmal finden, dann dauerhaft fernhalten
Was ist ein verwaister Datensatz?
Ein verwaister Datensatz ist eine Zeile in einer untergeordneten Tabelle, deren Fremdschlüssel auf einen übergeordneten Schlüssel verweist, der nicht existiert: eine Zahlung, deren account_id nicht in accounts steht, oder eine Medikamentengabe, deren product_code nicht in medications steht. Die Zeile kann für sich genommen vollkommen korrekt sein. Fehlerhaft ist die Beziehung.
Verwaiste Datensätze stehen immer auf der untergeordneten Seite; ein Konto ohne Zahlungen ist normal. Die üblichen Ursachen sind banal: Der übergeordnete Datensatz wurde gelöscht oder nie geladen, der untergeordnete kam vor dem morgigen Stammdatenladevorgang an, oder die Schlüsselformate unterscheiden sich ('00884213' gegenüber 884213 oder ein Leerzeichen am Ende).
Operative Datenbanken wie PostgreSQL oder Oracle erzwingen deklarierte Fremdschlüssel, aber Kopien im Warehouse übernehmen die Constraints meist nicht. Die meisten Cloud-Warehouses akzeptieren eine FOREIGN KEY-Deklaration, ohne sie zu erzwingen; die Dokumentation von BigQuery stellt fest, dass Primär- und Fremdschlüssel-Constraints nicht erzwungen werden. Warum Snowflake, BigQuery, Redshift und Databricks Fremdschlüssel nicht erzwingen, behandeln wir in einem eigenen Beitrag.
Warum verbergen Inner Joins verwaiste Datensätze?
Ein Inner Join liefert nur Zeilen, die auf beiden Seiten einen Treffer haben, daher fehlt eine untergeordnete Zeile ohne übergeordnete einfach im Ergebnis. Es gibt keinen Fehler, keine Warnung und keinen NULL-Wert, der auffallen würde. Summen fallen niedriger aus, als sie sollten, und nichts im Bericht selbst zeigt den Unterschied.
Jede Zahlung, deren account_id in accounts fehlt, fällt vor der SUM heraus. Am schnellsten sehen Sie die Lücke, wenn Sie auf beide Arten zählen:
Ist account_id in accounts eindeutig, entspricht die Differenz Ihren verwaisten Datensätzen plus allen Zeilen mit account_id gleich NULL.
Deklarierte, aber ungeprüfte Schlüssel können das noch verschlimmern. Der Planer von Amazon Redshift geht davon aus, dass deklarierte Schlüssel gültig sind, und AWS warnt, dass ungültige Schlüssel dazu führen können, dass manche Abfragen falsche Ergebnisse liefern.
Wie finden Sie verwaiste Datensätze mit SQL?
Verwaiste Datensätze finden Sie mit einem Anti-Join: einer Abfrage, die die untergeordneten Zeilen liefert, zu denen keine passende übergeordnete Zeile existiert. In SQL schreiben Sie ihn als LEFT JOIN … WHERE parent_key IS NULL, als NOT EXISTS oder als EXCEPT auf den eindeutigen Schlüsselwerten. Bei Schlüsseln ohne NULL liefern alle drei dasselbe Ergebnis.
LEFT JOIN … IS NULL
Prüfen Sie eine Spalte der übergeordneten Tabelle, die in einer Zeile mit Treffer nicht NULL sein kann, idealerweise den Join-Schlüssel. Setzen Sie Bedingungen auf die übergeordnete Tabelle in die ON-Klausel: WHERE a.status = 'ACTIVE' ist für eine Zeile ohne Treffer nie wahr, sodass die Abfrage nichts liefern würde.
NOT EXISTS
Diese Form liest sich wie die Frage selbst. Doppelte übergeordnete Schlüssel vervielfachen keine Zeilen, und NULL-Werte in accounts.account_id können sie nicht aushebeln: Ein Vergleich auf Gleichheit mit NULL ergibt nie einen Treffer.
EXCEPT (MINUS) auf eindeutigen Schlüsseln
Diese Form liefert die eindeutigen fehlenden Schlüsselwerte, nicht die Zeilen. Das ist oft die bessere erste Frage: Eine Handvoll fehlender Konten kann Tausende verwaister Zahlungen erklären. In Oracle heißt der Operator traditionell MINUS; BigQuery verlangt EXCEPT DISTINCT. Mengenoperatoren behandeln zwei NULL-Werte als gleich, ein weiterer Grund, NULL-Schlüssel explizit herauszufiltern.
Warum liefert NOT IN keine Zeilen, sobald ein NULL-Wert vorkommt?
NOT IN liefert keine Zeilen, wenn die Unterabfrage auch nur einen NULL-Wert enthält, denn durch die dreiwertige Logik von SQL ist jeder Vergleich mit diesem NULL unbekannt. x NOT IN (1, 2, NULL) bedeutet x <> 1 AND x <> 2 AND x <> NULL. Der letzte Term ist nie wahr, also ist die gesamte Bedingung nie wahr.
Bei einer Zahlung, deren Konto existiert, ist ein Vergleich falsch, und die Zeile wird korrekt ausgeschlossen. Bei einem fehlenden Konto ist jeder Vergleich mit einem echten Schlüssel wahr, der mit NULL aber unbekannt, also ist die Bedingung unbekannt, und WHERE behält nur wahre Zeilen. Das Ergebnis ist eine leere Menge, die sich nicht von „keine verwaisten Datensätze“ unterscheiden lässt.
Der Fehler passiert lautlos, mit einer falschen Entwarnung, und Schlüsselspalten im Warehouse sind oft nicht als NOT NULL deklariert. Zahlungen, deren eigene account_id NULL ist, werden ebenfalls nie erfasst. Verwenden Sie NOT EXISTS oder ergänzen Sie zumindest WHERE a.account_id IS NOT NULL in der Unterabfrage.
Welches Anti-Join-Muster sollten Sie verwenden?
Verwenden Sie NOT EXISTS als Standard für Prüfungen auf Zeilenebene, EXCEPT, wenn Sie die Liste der fehlenden Schlüsselwerte möchten, und LEFT JOIN … IS NULL, wenn Sie im selben Durchlauf auch die Anzahl der Treffer brauchen. Verwenden Sie NOT IN nur, wenn beide Spalten garantiert kein NULL enthalten.
Muster | Liefert | NULL im übergeordneten Schlüssel | Fremdschlüssel mit NULL in der untergeordneten Tabelle | Lesbarkeit | Typische Performance |
|---|---|---|---|---|---|
LEFT JOIN … IS NULL | Untergeordnete Zeilen | Sicher | Als verwaist gemeldet, sofern nicht gefiltert | Vertraut; die Absicht steht in der WHERE-Klausel | Wird meist als Anti-Join geplant |
NOT EXISTS | Untergeordnete Zeilen | Sicher | Als verwaist gemeldet, sofern nicht gefiltert | Liest sich wie die Frage | Wird meist als Anti-Join geplant |
EXCEPT / MINUS | Eindeutige Schlüsselwerte | Sicher | Einmal als NULL geliefert, sofern nicht gefiltert | Kurz und klar für Schlüssellisten | Dedupliziert beide Seiten; gut für Zusammenfassungen auf Schlüsselebene |
NOT IN | Untergeordnete Zeilen | Unsicher: Ein NULL-Wert liefert null Zeilen | Stillschweigend ausgeschlossen | Liest sich gut, führt in die Irre | Unproblematisch bei Spalten ohne NULL; bei Spalten mit NULL ist ein schlechterer Plan möglich |
Die meisten Optimizer planen LEFT JOIN und NOT EXISTS gleich; prüfen Sie den Plan auf Ihrer Plattform.
Wie gehen Sie mit zusammengesetzten Schlüsseln und Fremdschlüsseln mit NULL um?
Bei einem zusammengesetzten Schlüssel vergleichen Sie alle Schlüsselspalten gemeinsam in einem Prädikat, nie Spalte für Spalte: Eine Zeile ist verwaist, wenn ihre Kombination fehlt, auch wenn jeder einzelne Wert irgendwo existiert. Fremdschlüssel mit NULL erfordern eine bewusste Entscheidung, bevor Sie sie zählen: fehlende Referenz oder legitimerweise leer.
Angenommen, jedes Krankenhaus hat seine eigene Arzneimittelliste mit dem Schlüssel (hospital_id, product_code):
Zwei getrennte Prüfungen, „das Krankenhaus existiert“ und „das Produkt existiert“, bestehen beide bei einem Produkt, das nur in Krankenhaus A gelistet ist und in Krankenhaus B verabreicht wurde. Nur das kombinierte Prädikat erkennt den Fehler. EXCEPT verarbeitet zusammengesetzte Schlüssel ganz natürlich. Verketten Sie Schlüssel nicht zu einer Zeichenkette: '1' || '23' und '12' || '3' kollidieren.
Eine Zahlung mit account_id gleich NULL verweist nicht auf ein fehlendes Konto; sie verweist nirgendwohin. Eine Zahlung muss ein Konto haben, während eine optionale Referenz wie referring_doctor_id leer sein darf. Schließen Sie NULL-Werte aus der Abfrage nach verwaisten Datensätzen aus und zählen Sie sie separat:
Wie finden Sie verwaiste Datensätze in großen Tabellen?
Bei großen Tabellen zählen Sie, bevor Sie auflisten, vergleichen eindeutige Schlüsselwerte statt jeder Zeile und beschränken die untergeordnete Seite auf den letzten Ladevorgang oder die letzte Partition. Lassen Sie die übergeordnete Seite vollständig: Eine heute gebuchte Zahlung kann auf ein Konto verweisen, das vor Jahren eröffnet wurde, filtern Sie die übergeordnete Tabelle also nie nach Ladedatum.
Zuerst zählen. Führen Sie den Anti-Join als
COUNT(*)aus. Holen Sie Zeilen nur, wenn die Anzahl nicht null ist, und dann mit einem Limit.Eindeutige Schlüssel vergleichen. Reduzieren Sie die untergeordnete Seite zuerst auf
SELECT DISTINCT account_id. Eindeutige Schlüssel sind meist weit weniger als Zeilen.Auf den letzten Ladevorgang filtern. Beschränken Sie die untergeordnete Tabelle auf die neueste Partition oder das neueste
load_date, damit die Engine Partitionen ausschließen kann.Die Join-Spalte vergleichbar halten. Derselbe Datentyp auf beiden Seiten, kein
CASToderTRIMim Prädikat, denn das kann die Nutzung von Indizes und das Partition Pruning verhindern. Korrigieren Sie Formate stattdessen beim Laden; siehe unseren Leitfaden zur Datenbereinigung in SQL.Das Ergebnis festhalten. Speichern Sie Datum, ausgewertete Zeilen und gefundene verwaiste Datensätze, um den Trend zu sehen.
Dies zählt fehlende Schlüssel, nicht verwaiste Zeilen; die Zeilen zu diesen Schlüsseln holen Sie anschließend.
Was, wenn die übergeordnete Tabelle in einer anderen Datenbank liegt?
Ein Anti-Join funktioniert nur, wenn eine Query-Engine beide Tabellen lesen kann. Liegen die Zahlungen im Warehouse und die Konten in der Kernbankdatenbank, kann reines SQL nicht beide sehen. Sie kopieren also eine Seite hinüber, verwenden einen Datenbank-Link oder eine föderierte Abfrage oder führen die Prüfung in einem Tool aus, das beide Verbindungen erreicht.
Eine kopierte Stammtabelle ist eine weitere Pipeline mit eigener Verzögerung: Eine veraltete Kopie meldet falsche verwaiste Datensätze oder übersieht echte. Datenbank-Links hängen von der Unterstützung der Plattform ab und brauchen für jede neue Verbindung eine Sicherheitsfreigabe.
Wie machen Sie aus einer Abfrage nach verwaisten Datensätzen eine dauerhafte Prüfung?
Aus einer Abfrage nach verwaisten Datensätzen wird eine dauerhafte Prüfung, indem Sie sie bei jedem Ladevorgang ausführen, die Anzahl bestandener und fehlgeschlagener Zeilen festhalten, einen Schwellenwert für den Fehlschlag setzen und die fehlerhaften Zeilen dort ablegen, wo das verantwortliche Team sie sehen kann. In digna ist das eine einzige Referential-Integrity-Regel, konfiguriert in einem Dialog, ohne dass Sie SQL schreiben müssen.
digna Data Validation kennt drei Regelarten: Rule, Uniqueness und Referential Integrity. Die referenzielle ist der Anti-Join aus diesem Artikel: Aus Spalten einer Datenquelle und einem passenden Spaltensatz einer anderen ermittelt digna die eindeutigen Werte der anderen Tabelle und lässt jede Zeile fehlschlagen, die keinen Join-Partner findet. Die Prüfung läuft innerhalb Ihrer Quelldatenbank; Ihre Daten verlassen nie Ihre Infrastruktur. Seit Release 2026.01 funktioniert sie über verschiedene Datenbankverbindungen innerhalb desselben Projekts hinweg, ohne Daten zu replizieren.
Die folgenden Screenshots verwenden die Demodaten von digna für die Danubia Kliniken, einen fiktiven österreichischen Krankenhausverbund.
Am 2026-04-22 erfassten die Stationen 82 Gaben von Coavira 2.5 mg (Produktcode 3858646), das noch nicht im Produktstamm der Apotheke angelegt war. Jeder Bericht, der Dosen mit Produkten verknüpfte, zeigte 0 Dosen davon, während das Pflegepersonal 82 verabreicht hatte. Die Regel hc_product_in_master schlug fehl: 4.244 von 4.326 Zeilen bestanden. Von Hand bräuchten Sie:
Die Ansicht Invalid Records zeigt diese Ergebnismenge, ohne dass jemand sie schreiben muss:
Invalid Records, gefiltert auf Failed: jede verwaiste Dosis mit Krankenhaus, Station, Abteilung, Produktcode und Medikamentenname.
Die Regel einrichten:
Configuration → Datenquelle
hospital_medication_administrations→ Tab Data Validation → Add Rule. Der Dialog Add Data Validation Rule öffnet sich.Geben Sie einen Name (
hc_product_in_master) und eine Description ein.Setzen Sie Type auf Referential Integrity und wählen Sie unter Attributes
product_code.Wählen Sie unter must exist in die Data Source
hospital_medicationsund deren Attributesproduct_code. Bei einem zusammengesetzten Schlüssel wählen Sie auf beiden Seiten mehrere Spalten in derselben Reihenfolge; Listen unterschiedlicher Länge werden abgewiesen.Wählen Sie als Threshold Mode (Schwellenwertmodus) Absolute oder Relative und setzen Sie Info threshold und Warn threshold (hier: Absolute, Info 0, Warn 1). Speichern Sie. Die Regel läuft bei jeder Inspektion, geplant oder auf Abruf.
Die gesamte Regel: zwei Attributlisten, eine Zieldatenquelle und zwei Schwellenwerte.
Referential Integrity überspringt NULL-Werte, wie der Filter IS NOT NULL oben; wenn ein Wert vorhanden sein muss, ergänzen Sie eine separate Rule mit product_code IS NOT NULL. Oberhalb des Info threshold lautet der Status Uncertain, oberhalb des Warn threshold Failed, sodass bei Info 0 jeder verwaiste Datensatz die Regel aus dem Status Passed holt. Die fehlerhaften Datensätze sind dieselbe Abfrage mit negierter Bestehensbedingung, lassen sich exportieren, und Ergebnisse können das Team benachrichtigen, das für die Daten verantwortlich ist.
Seit Release 2026.06 können Regeln auch als Code über das Python SDK (pip install digna-sdk) verwaltet werden. Die vollständige Anleitung finden Sie im Beitrag zur Einrichtung einer Prüfung der referenziellen Integrität in digna oder im 2:26 Minuten langen Einrichtungsvideo.
Einmal finden, dann dauerhaft fernhalten
Zur Untersuchung verwaister Datensätze: NOT EXISTS für Zeilen, EXCEPT für fehlende Schlüssel, nie NOT IN auf einer Spalte, die NULL enthalten kann, und eine bewusste Entscheidung zu NULL-Werten. Sie dauerhaft fernzuhalten erfordert mehr: Fremdschlüssel erzwingen, wo die Datenbank es kann, übergeordnete Daten zuerst laden, verspätet eintreffende Stammdaten explizit behandeln und die referenzielle Prüfung bei jedem Ladevorgang ausführen.
Wenn Sie diese Prüfung auf Ihren eigenen Tabellen und innerhalb Ihrer eigenen Infrastruktur laufen sehen möchten, buchen Sie eine Demo mit dem digna-Team.
Häufig gestellte Fragen
Wie finde ich verwaiste Datensätze in SQL?
Verwenden Sie einen Anti-Join, der untergeordnete Zeilen ohne passende übergeordnete Zeile liefert. Die robusteste Form ist NOT EXISTS mit einer korrelierten Unterabfrage auf den Schlüssel; LEFT JOIN … WHERE parent_key IS NULL liefert dasselbe Ergebnis. Filtern Sie Fremdschlüssel mit NULL zuerst heraus und zählen Sie sie in einer separaten Vollständigkeitsprüfung.
Warum liefert NOT IN keine Zeilen, wenn die Unterabfrage einen NULL-Wert enthält?
Der Grund ist die dreiwertige Logik. x NOT IN (1, 2, NULL) wird zu x <> 1 AND x <> 2 AND x <> NULL, und der letzte Vergleich ist unbekannt, nie wahr. WHERE behält nur wahre Zeilen, also liefert die Abfrage nichts, was genau wie ein sauberes Ergebnis aussieht.
Ist NOT EXISTS schneller als LEFT JOIN IS NULL?
Meist ist keines von beiden schneller: Die meisten modernen Optimizer planen beide als Anti-Join, daher entscheidet die Lesbarkeit. NOT IN ist der Ausreißer. Auf Spalten, die NULL enthalten können, kann es einen schlechteren Plan bekommen, und es liefert überhaupt keine Zeilen, wenn die Unterabfrage einen NULL-Wert enthält.
Was ist der Unterschied zwischen einem verwaisten Datensatz und einem Fremdschlüssel mit NULL?
Ein verwaister Datensatz verweist auf einen übergeordneten Schlüssel, der nicht existiert, während ein Fremdschlüssel mit NULL nirgendwohin verweist. Beide brauchen unterschiedliche Prüfungen: referenzielle Integrität für verwaiste Datensätze und eine IS-NOT-NULL-Regel, wo die Referenz verpflichtend ist. Genau aus diesem Grund überspringt die Referential-Integrity-Regel von digna NULL-Werte.
Wie kann ich nach jedem Ladevorgang automatisch auf verwaiste Datensätze prüfen?
Machen Sie aus dem Anti-Join eine geplante Prüfung mit Schwellenwerten und gespeicherten Ergebnissen. In digna ist das eine einzige Referential-Integrity-Regel in Data Validation: Spalten wählen, die Datenquelle wählen, in der sie existieren müssen, Info- und Warn-Schwellenwert setzen, speichern. Sie läuft bei jeder Inspektion innerhalb Ihrer Datenbank.
