Datenqualität mit SQL prüfen — ein konfigurierbares Framework, das schlechte Daten generisch aufspürt

Veröffentlicht 01.07.2026
von

Ein Fremdschlüssel ins Leere ist schnell beschrieben — ein country_code, zu dem es keinen Eintrag in den Stammdaten gibt — und überraschend leicht falsch geprüft. Die intuitivste Formulierung ist ausgerechnet die gefährlichste: NOT IN (SELECT …) liest sich wie deutsche Prosa, kippt aber lautlos um, sobald die Referenzspalte einen einzigen NULL enthält. Dann meldet die Prüfung null verwaiste Sätze, läuft grün durch und findet nichts — während die schlechten Zeilen unbemerkt weiterfließen, bis sie am Ziel den Fremdschlüssel-Constraint und damit den ganzen Load kippen. Wer verwaiste Datensätze finden will, muss diese Falle kennen.

Das Wichtigste vorab:

  • Die Grundform ist ein LEFT JOIN … IS NULL: alle Kind-Sätze, die in der Master-Tabelle keinen Partner finden — generisch über jede Master-Kind-Beziehung.
  • Drei Formulierungen leisten dasselbe, aber nicht gleich sicher: LEFT JOIN … IS NULL und NOT EXISTS sind NULL-robust, NOT IN ist es nicht — ein NULL in der Referenz legt die ganze Prüfung lahm.
  • Zusammengesetzte Fremdschlüssel prüft man mit einem Mehrspalten-Join — alle Schlüsselteile gehören in die ON-Bedingung.
  • Selbst-referenzielle Beziehungen (eine Hierarchie wie manager_id → employee_id) prüft man mit einem Self-Join; Wurzel-Knoten (IS NULL) sind dabei keine Orphans.

Voraussetzung: Postgres als Beispiel-Engine und das gemeinsame Prüfgerüst aus dem Framework-Artikel — eine zentrale Fehlertabelle dq.error, in die jede Verletzung eine Zeile schreibt, und eine Staging-Schicht, gegen die geprüft wird. Dieser Artikel vertieft die dritte der drei Routinen von dort: die referenzielle Integrität.

Inhalt

Verwaiste Datensätze finden: die Grundform

Ein verwaister Satz ist eine Zeile, deren Fremdschlüssel auf einen Master zeigt, den es nicht gibt: eine Bestellung zu einem gelöschten Kunden, ein Kunde mit einem country_code, der in der Länder-Tabelle fehlt. Solange kein Fremdschlüssel-Constraint die Beziehung erzwingt — und in einer roh geladenen Staging-Schicht tut er das bewusst nicht —, ist so ein Satz technisch erlaubt und fachlich kaputt.

Generisch ausgedrückt sucht man alle Sätze der Kind-Tabelle, die per LEFT JOIN keinen Partner in der Master-Tabelle finden. Wo der Join nichts trifft, füllt der LEFT JOIN die Master-Spalten mit NULL — und genau auf dieses NULL filtert man:

  1: SELECT
  2:     T01.customer_id
  3:    ,T01.country_code
  4: FROM
  5:    staging.customer T01
  6:    LEFT JOIN staging.country T02
  7:    ON
  8:      T01.country_code = T02.country_code
  9: WHERE
 10:        T01.country_code IS NOT NULL
 11:    AND T02.country_code IS NULL;
 12: -- 3 | XX     verwaister Satz: country_code XX hat keinen Master

Das Ergebnis ist Kunde 3 mit dem Code XX, den es in der Länder-Tabelle nicht gibt. Kind-Tabelle, Kind-Spalte und Master sind die einzigen drei Stellschrauben — damit ist dieselbe Abfrage für jede Master-Kind-Beziehung verwendbar. Das ist die Dimension Konsistenz / Integrität: Zeigt jeder Verweis auf etwas, das wirklich existiert?

Die Bedingung in Zeile 10 (T01.country_code IS NOT NULL) ist kein Beiwerk — sie trennt „unbekannter Wert“ von „kein Wert“. Dazu gleich mehr; zuerst zur eigentlichen Falle.

LEFT JOIN vs. NOT EXISTS vs. NOT IN — und die NULL-Falle

Dieselbe Frage — „welche Kind-Sätze haben keinen Master?“ — lässt sich auf drei Arten formulieren. Alle drei liefern auf sauberen Daten dasselbe Ergebnis; unter NULL driften sie auseinander.

Die ersten beiden sind robust. Der LEFT JOIN … IS NULL aus der Grundform ist die eine Form; NOT EXISTS ist die andere und liest sich oft am direktesten — „behalte die Zeile, wenn es keinen passenden Master gibt“:

  1: SELECT
  2:     T01.customer_id
  3:    ,T01.country_code
  4: FROM
  5:    staging.customer T01
  6: WHERE
  7:    T01.country_code IS NOT NULL
  8:    AND NOT EXISTS (
  9:       SELECT 1
 10:       FROM   staging.country T02
 11:       WHERE  T02.country_code = T01.country_code
 12:    );
 13: -- 3 | XX     identisch zur LEFT-JOIN-Form

Jetzt die Falle. Die naheliegendste Formulierung ist NOT IN — „behalte die Zeile, wenn ihr Code nicht in der Liste der Länder steht“. Sie funktioniert tadellos, solange die Referenzspalte kein NULL enthält. Sobald aber eine einzige Master-Zeile einen NULL-Code hat — ein Platzhalter, ein Importfehler, eine schlicht unbeschränkte Staging-Spalte —, meldet NOT IN für alle Zeilen nichts mehr:

  1: SELECT
  2:     T01.customer_id
  3:    ,T01.country_code
  4: FROM
  5:    staging.customer T01
  6: WHERE
  7:    T01.country_code NOT IN (
  8:       SELECT country_code
  9:       FROM   staging.country
 10:    );
 11: -- (0 rows)     die Pruefung laeuft gruen und findet NICHTS

Der Grund liegt in der drei-wertigen Logik. Die Referenz-Subquery liefert vier Werte — darunter das eine NULL:

staging.country
DE
FR
US
NULL ← die böse Zeile

Für den echten verwaisten Satz XX rechnet Postgres NOT IN als Kette von Ungleich-Vergleichen aus:

'XX' NOT IN ('DE', 'FR', 'US', NULL)
  =  'XX' <> 'DE'  AND  'XX' <> 'FR'  AND  'XX' <> 'US'  AND  'XX' <> NULL
  =  TRUE          AND  TRUE          AND  TRUE          AND  UNKNOWN
  =  UNKNOWN

Der letzte Vergleich 'XX' <> NULL ergibt nicht FALSE, sondern UNKNOWN — die Datenbank kann nicht behaupten, XX sei ungleich einem unbekannten Wert (der könnte ja XX sein). Und irgendetwas AND UNKNOWN wird nie TRUE. Das WHERE behält aber nur TRUE-Zeilen: Für jede Zeile fällt der Gesamtausdruck auf UNKNOWN, keine passiert. Die Prüfung läuft grün und übersieht den echten verwaisten Satz XX — der Worst Case einer Qualitätsprüfung: nicht ein falscher Treffer, sondern stilles Durchwinken.

Wer bei NOT IN bleiben will, muss das NULL aus der Subquery ausschließen:

  1: SELECT
  2:     T01.customer_id
  3:    ,T01.country_code
  4: FROM
  5:    staging.customer T01
  6: WHERE
  7:    T01.country_code IS NOT NULL
  8:    AND T01.country_code NOT IN (
  9:       SELECT country_code
 10:       FROM   staging.country
 11:       WHERE  country_code IS NOT NULL
 12:    );
 13: -- 3 | XX     jetzt wieder korrekt

Das funktioniert — aber es ist genau der Zusatzaufwand, den man bei LEFT JOIN … IS NULL und NOT EXISTS nie braucht. Deshalb die einfache Faustregel: für Orphan-Prüfungen NOT EXISTS oder LEFT JOIN … IS NULL nehmen, NOT IN für diese Aufgabe meiden.

„Unbekannt“ ist nicht „leer“

In den robusten Varianten — der LEFT JOIN-Grundform und der NOT EXISTS-Fassung (und ebenso in der reparierten NOT IN-Variante) — steht die Bedingung T01.country_code IS NOT NULL. Sie ist der zweite stille Stolperstein. Ein verwaister Satz hat einen Wert, der ins Leere zeigt (XX — existiert nicht); davon zu unterscheiden ist der Satz, der gar keinen Wert hat (country_code IS NULL — Kunde 4 im Demo-Datensatz).

Beides sind Datenfehler, aber verschiedene: Ein unbekannter Verweis ist ein Integritäts-Problem (Routine 3, dieser Artikel), ein fehlender Pflichtwert ein Vollständigkeits-Problem (Routine 1, Sache von Daten mit SQL validieren). Würfelt man beides in eine Prüfung, landet derselbe Satz in zwei Befunden mit zwei Meldungen, und die Fehlertabelle wird unscharf. Die IS NOT NULL-Bedingung zieht die Grenze sauber: Dieser Check meldet nur „zeigt auf etwas Nicht-Existierendes“, nicht „zeigt auf nichts“.

Zusammengesetzte Fremdschlüssel

Selten ist der Fremdschlüssel eine einzige Spalte. Ein Verkaufssatz verweist vielleicht über region_code und product_code gemeinsam auf eine Stammdaten-Tabelle, die festhält, welches Produkt in welcher Region überhaupt geführt wird. Verwaist ist der Satz dann, wenn das vollständige Paar im Master fehlt — auch wenn jeder Teil für sich vorkommt. Alle Schlüsselteile gehören in die ON-Bedingung:

  1: SELECT
  2:     T01.sale_id
  3:    ,T01.region_code
  4:    ,T01.product_code
  5: FROM
  6:    staging.sales T01
  7:    LEFT JOIN staging.product_region T02
  8:    ON
  9:           T02.region_code  = T01.region_code
 10:       AND T02.product_code = T01.product_code
 11: WHERE
 12:        T01.region_code  IS NOT NULL
 13:    AND T01.product_code IS NOT NULL
 14:    AND T02.region_code  IS NULL;
 15: -- 2 | US | B     das Paar (US, B) hat keinen Master

Der Treffer ist Verkauf 2 mit (US, B): Die Region US gibt es im Master, das Produkt B auch — aber nicht die Kombination (US, B). Vergisst man eine Schlüsselspalte in der ON-Bedingung, prüft man einen gröberen Schlüssel und bekommt falsche Treffer: Mit nur region_code im Join gälte (US, B) als gedeckt, weil US ja vorkommt, und der verwaiste Satz bliebe unentdeckt. Es genügt, dass die Master-Seite eines Schlüsselteils NULL ist (Zeile 14 prüft region_code), um den fehlenden Partner zu erkennen.

Selbst-referenzielle Beziehungen

Manche Tabellen verweisen auf sich selbst. Eine Mitarbeiter-Hierarchie hält in manager_id die employee_id des Vorgesetzten — derselben Tabelle. Verwaist ist ein Satz, dessen manager_id auf keine existierende employee_id zeigt. Die Prüfung ist ein Self-Join: dieselbe Tabelle zweimal, T02 als „Manager-Seite“:

  1: SELECT
  2:     T01.employee_id
  3:    ,T01.manager_id
  4: FROM
  5:    staging.employee T01
  6:    LEFT JOIN staging.employee T02
  7:    ON
  8:      T02.employee_id = T01.manager_id
  9: WHERE
 10:        T01.manager_id  IS NOT NULL
 11:    AND T02.employee_id IS NULL;
 12: -- 4 | 99     manager_id 99 hat keinen passenden employee_id

Wieder leistet die IS NOT NULL-Bedingung (Zeile 10) die entscheidende Arbeit, hier mit anderer Bedeutung: Die Wurzel der Hierarchie — der CEO ohne Vorgesetzten — hat legitim manager_id IS NULL. Das ist kein verwaister Satz, sondern das erwartete Ende der Kette. Ohne die Bedingung würde jede Wurzel fälschlich als Orphan gemeldet. Übrig bleibt korrekt nur Mitarbeiter 4, dessen manager_id 99 auf niemanden zeigt.

Performance: der Index auf der Kind-Spalte

Die Orphan-Prüfung ist im Kern ein Anti-Join, und den setzt Postgres effizient um. Liegt auf der Kind-Spalte (dem Fremdschlüssel) ein Index, kann der Planer für NOT EXISTS oder LEFT JOIN … IS NULL einen Hash Anti Join oder einen index-gestützten Plan wählen, statt für jede Kind-Zeile einzeln in den Master zu schauen. Erzwingen lässt sich das nicht — der Planer entscheidet nach Kosten und greift bei kleinen Mengen oft trotzdem zum Sequential Scan — aber bei großen Tabellen kann der Index auf der Fremdschlüssel-Spalte den Ausschlag geben. Genau dieser Index fehlt in roh geladenen Staging-Tabellen oft, weil dort eben keine Constraints liegen.

Im Kern bleibt es ein Durchlauf, der jede Kind-Zeile einmal gegen den Master abgleicht. Derselbe Hebel wie bei jeder Staging-Prüfung gilt deshalb auch hier: Man begrenzt die Prüfung mit einem Lade-Marker (load_id, batch_id) auf den aktuell geladenen Batch, statt jedes Mal den gesamten Bestand neu zu prüfen. Und wenn am Ziel ohnehin ein Fremdschlüssel-Constraint auf der Beziehung liegt, ist die Vorab-Prüfung exakt die Arbeit, die die Datenbank beim Laden sowieso leisten würde — nur eben in der Quelle, wo ein Verstoß meldbar statt fatal ist.

Generisch über die Config-Routine

Hartkodiert ist auch diese Prüfung nicht praktikabel. Generisch wird sie über eine Konfigurationszeile, aus der der Runner des Framework-Artikels zur Laufzeit das LEFT JOIN … IS NULL gegen die konfigurierte Referenztabelle baut:

  1: INSERT INTO dq.check_rule
  2:    (check_type, schema_name, table_name, id1_column, check_column, ref_schema, ref_table, ref_column, severity, message)
  3: VALUES
  4:    ('lookup', 'staging', 'customer', 'customer_id', 'country_code', 'staging', 'country', 'country_code', 'E', 'Unbekannter country_code');

check_type = 'lookup' wählt im Runner den Integritäts-Zweig; check_column ist die Kind-Spalte, ref_schema/ref_table/ref_column benennen den Master. Alle Bezeichner setzt der Runner über format() mit %I ein (injection-sicher) — und weil der Zweig den NULL-robusten LEFT JOIN … IS NULL baut, nicht NOT IN, ist die Falle aus diesem Artikel im generischen Code von vornherein vermieden. Ein Integritäts-Check braucht zudem kein frei konfiguriertes Roh-SQL und kommt damit ohne die Vertrauensgrenze der WHERE-Routine aus. Die vollständige Runner-Mechanik steht im Framework-Artikel.

Brücke: dasselbe in SQL Server

Das Muster ist nicht Postgres-spezifisch. In SQL Server übernimmt sp_executesql die Rolle von EXECUTE format(), Bezeichner schützt QUOTENAME() statt %I. LEFT JOIN … IS NULL und NOT EXISTS sind wortgleich — und genauso wichtig: die NOT IN-+-NULL-Falle gilt in SQL Server identisch. Auch dort macht ein einzelnes NULL in der Subquery die ganze NOT IN-Prüfung zu UNKNOWN; die drei-wertige Logik ist SQL-Standard, kein Dialekt-Detail. Struktur, Fehlertabelle und Schweregrad-Gate bleiben gleich; nur die Werkzeuge zum Bauen des dynamischen SQL unterscheiden sich.

FAQ

Warum findet NOT IN keine verwaisten Sätze?

Weil ein einziges NULL in der Referenz-Subquery die ganze Prüfung lahmlegt. code NOT IN (…, NULL) enthält den Vergleich code <> NULL, der nie TRUE oder FALSE ergibt, sondern UNKNOWN — und damit fällt der gesamte AND-Ausdruck für jede Zeile auf UNKNOWN. Keine Zeile passiert das WHERE, die Prüfung meldet null Treffer. Abhilfe: NOT EXISTS oder LEFT JOIN … IS NULL nehmen, oder das NULL mit WHERE … IS NOT NULL aus der Subquery ausschließen.

LEFT JOIN … IS NULL oder NOT EXISTS — was ist besser?

Beide sind NULL-robust und liefern dasselbe Ergebnis; der Planer optimiert sie oft sogar zum selben Anti-Join. NOT EXISTS liest sich meist direkter („behalte die Zeile, wenn kein Master existiert“) und braucht keine IS NULL-Bedingung auf der Master-Seite. LEFT JOIN … IS NULL ist Geschmackssache und gleichwertig. Hauptsache nicht NOT IN.

Wie prüfe ich einen zusammengesetzten Fremdschlüssel?

Mit einem Mehrspalten-Join: alle Schlüsselteile in die ON-Bedingung, verknüpft mit AND. Verwaist ist ein Satz nur, wenn das vollständige Tupel im Master fehlt — die Region allein oder das Produkt allein zu prüfen, übersieht fehlende Kombinationen. Im WHERE genügt es, auf die NULL-gefüllte Master-Seite eines Schlüsselteils zu filtern.

Wie prüfe ich eine selbst-referenzielle Tabelle?

Mit einem Self-Join: dieselbe Tabelle zweimal, der zweite Alias als „Parent-Seite“. Die ON-Bedingung verbindet die Fremdschlüssel-Spalte (manager_id) mit dem Primärschlüssel (employee_id) desselben Tabellen-Alias. Wichtig: Wurzel-Knoten mit manager_id IS NULL sind legitim und keine Orphans — sie über WHERE manager_id IS NOT NULL ausschließen.

Geht das auch in SQL Server?

Ja. LEFT JOIN … IS NULL und NOT EXISTS sind wortgleich, sp_executesql ersetzt EXECUTE format(), QUOTENAME() ersetzt %I. Und die NOT IN-+-NULL-Falle gilt dort genauso — die drei-wertige Logik ist SQL-Standard, kein Postgres-Spezifikum.

Verwandte Artikel

  • Datenqualität mit SQL prüfen — das Framework, aus dem dieser Artikel die Integritätsroutine vertieft: gemeinsame Fehlertabelle, Schweregrad-Gate, der dynamische Runner.
  • Daten mit SQL validieren — die Schwester-Routine zu Validität und Vollständigkeit, inklusive der Unterscheidung „kein Wert“ vs. „unbekannter Wert“.
  • Duplikate finden mit SQL — die Schwester-Routine zur Eindeutigkeit: maximale Kardinalität, zusammengesetzte Schlüssel und die NULL-Falle der UNIQUE-Semantik.
  • Design Pattern // Architektur eines ETL-Prozesses — wie sich schlechte Daten über geschichtete Staging-Ebenen sauber isolieren lassen, bevor sie das strikte Ziel erreichen.