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

Aus einer ähnlichen Frage hier MySQL Inner Join-Abfrage zum Abrufen von Datensätzen, die in der anderen Tabelle nicht vorhanden sind Ich habe dies zum Laufen gebracht

SELECT * FROM bigtable 
LEFT JOIN smalltable ON bigtable.id = smalltable.id 
WHERE smalltable.id IS NULL

smalltable ist die Stelle, an der Sie fehlende Datensätze haben, bigtable ist der Ort, an dem Sie alle Aufzeichnungen haben. Die Abfrage listet alle Datensätze auf, die nicht in smalltable sondern existiert auf der bigtable . Sie könnten ersetzen id durch ein anderes übereinstimmendes Kriterium.

Answer 2

Ich weiß nicht, welche davon optimiert ist (im Vergleich zu @AdaTheDev ), aber diese scheint schneller zu sein, wenn ich sie benutze (zumindest für mich)

SELECT id  FROM  table_1 EXCEPT SELECT DISTINCT (table1_id) table1_id FROM table_2

Wenn Sie ein anderes spezifisches Attribut erhalten möchten, können Sie es verwenden:

SELECT COUNT(*) FROM table_1 where id in (SELECT id  FROM  table_1 EXCEPT SELECT DISTINCT (table1_id) table1_id FROM table_2);

Answer 3

Sie können sich entscheiden für Ansichten wie unten dargestellt:

CREATE VIEW AuthorizedUserProjectView AS select t1.username as username, t1.email as useremail, p.id as projectid, 
(select m.role from userproject m where m.projectid = p.id and m.userid = t1.id) as role 
FROM authorizeduser as t1, project as p

und bearbeiten Sie dann die Ansicht zum Auswählen oder Aktualisieren:

select * from AuthorizedUserProjectView where projectid = 49

was zu dem in der nachstehenden Abbildung gezeigten Ergebnis führt, d. h. für nicht übereinstimmende Spalten wurde die Null eingegeben.

[Result of select on the view][1]

Answer 4

Sie können etwa so vorgehen

   SELECT IFNULL(`price`.`fPrice`,100) as fPrice,product.ProductId,ProductName 
          FROM `products` left join `price` ON 
          price.ProductId=product.ProductId AND (GeoFancingId=1 OR GeoFancingId 
          IS NULL) WHERE Status="Active" AND Delete="No"

Answer 5

SELECT * FROM Erste_Tabelle MINUS SELECT * FROM andere