Wie wählt man Zeilen aus, die keinen passenden Eintrag in einer anderen Tabelle haben?

Question

Ich führe gerade Wartungsarbeiten an einer Datenbankanwendung durch und habe festgestellt, dass, welch eine Freude, obwohl Werte aus einer Tabelle im Stil von Fremdschlüsseln verwendet werden, es keine Fremdschlüssel-Beschränkungen für die Tabellen gibt.

Ich versuche, FK-Beschränkungen für diese Spalten hinzuzufügen, aber ich stelle fest, dass ich die Zeilen, die nicht mit der anderen Tabelle übereinstimmen, finden und dann löschen muss, weil es bereits eine ganze Reihe von fehlerhaften Daten in den Tabellen gibt, die aus früheren Fehlern stammen, die naiv korrigiert wurden.

Ich habe im Internet einige Beispiele für diese Art von Abfragen gefunden, aber sie scheinen alle eher Beispiele als Erklärungen zu liefern, und ich verstehe nicht, warum sie funktionieren.

Kann mir jemand erklären, wie man eine Abfrage konstruiert, die alle Zeilen ohne Übereinstimmungen in einer anderen Tabelle zurückgibt, und was sie tut, so dass ich diese Abfragen selbst machen kann, anstatt zu SO für jede Tabelle in dieser Chaos die keine FK-Beschränkungen hat?

Answer 1

Hier ist eine einfache Abfrage:

SELECT t1.ID
FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

Die wichtigsten Punkte sind:

1. LEFT JOIN verwendet wird; dies gibt ALLE Zeilen aus Table1 unabhängig davon, ob es eine passende Zeile in Table2 .

2. El WHERE t2.ID IS NULL Klausel; dadurch werden die zurückgegebenen Ergebnisse auf die Zeilen beschränkt, in denen die ID aus Table2 null ist - mit anderen Worten, es gibt NO einschreiben Table2 für diese bestimmte ID aus Table1 . Table2.ID wird als NULL zurückgegeben für alle Datensätze von Table1 wo die ID nicht in Table2 .

Answer 2

Ich würde verwenden EXISTS Ausdruck, da er leistungsfähiger ist, können Sie z. B. die Zeilen, die Sie verbinden möchten, genauer auswählen. Im Fall von LEFT JOIN müssen Sie alles nehmen, was in der verbundenen Tabelle steht. Die Effizienz ist wahrscheinlich die gleiche wie im Fall von LEFT JOIN mit Null-Beschränkung.

SELECT t1.ID
FROM Table1 t1
WHERE NOT EXISTS (SELECT t2.ID FROM Table2 t2 WHERE t1.ID = t2.ID)

Answer 3

SELECT id FROM table1 WHERE foreign_key_id_column NOT IN (SELECT id FROM table2)

Tabelle 1 hat eine Spalte, zu der Sie die Fremdschlüssel-Beschränkung hinzufügen möchten, aber die Werte in der Spalte foreign_key_id_column passen nicht alle zu einer id in Tabelle 2.

1. Die anfängliche Auswahl listet die id s aus Tabelle1. Dies sind die Zeilen, die wir löschen wollen.
2. El NOT IN Klausel in der where-Anweisung schränkt die Abfrage auf die Zeilen ein, in denen der Wert im Feld foreign_key_id_column nicht in der Liste der Tabelle 2 enthalten ist id s.
3. El SELECT Anweisung in Klammern wird eine Liste mit allen id s, die in Tabelle 2 aufgeführt sind.

Answer 4

Nehmen wir an, wir haben die folgenden 2 Tabellen (Gehalt und Mitarbeiter)

Jetzt möchte ich die Datensätze aus der Angestelltentabelle, die nicht im Gehalt enthalten sind. Wir können dies auf 3 Arten tun:

1. Inner Join verwenden

   select * from employee where id not in(select e.id from employee e inner join salary s on e.id=s.id)

2. Linke äußere Verknüpfung verwenden

   select * from employee e left outer join salary s on e.id=s.id where s.id is null

3. Full Join verwenden

   select * from employee e full outer join salary s on e.id=s.id where e.id not in(select id from salary)

Answer 5

Wo T2 ist die Tabelle, zu der Sie die Einschränkung hinzufügen:

SELECT *
FROM T2
WHERE constrained_field NOT
IN (
    SELECT DISTINCT t.constrained_field
    FROM T2 
    INNER JOIN T1 t
    USING ( constrained_field )
)

Und löschen Sie die Ergebnisse.