webentwicklung-frage-antwort-db.com.de

Wie wähle ich Zeilen ohne passenden Eintrag in einer anderen Tabelle aus?

Ich mache einige Wartungsarbeiten an einer Datenbankanwendung, und ich habe entdeckt, dass Freude an Freude, auch wenn Werte aus einer Tabelle im Stil von Fremdschlüsseln verwendet werden, keine Fremdschlüsseleinschränkungen in den Tabellen vorhanden sind.

Ich versuche, FK-Einschränkungen für diese Spalten hinzuzufügen, aber ich stelle fest, dass in den Tabellen bereits eine ganze Menge fehlerhafter Daten aus vorherigen Fehlern vorhanden sind, die auf naive Weise korrigiert wurden. Ich muss die Zeilen finden, die dies nicht tun stimmen Sie mit der anderen Tabelle überein und löschen Sie sie.

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

Kann mir jemand erklären, wie man eine Abfrage erstellt, die alle Zeilen ohne Übereinstimmungen in einer anderen Tabelle zurückgibt, und was sie tut, damit ich diese Abfragen selbst vornehmen kann, anstatt für jede Tabelle in SO zu laufen Dieses Chaos das hat keine FK-Einschränkungen?

sqlforeign-keys
238
Frosty840

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 wird verwendet; Dadurch werden ALLE Zeilen von Table1 zurückgegeben, unabhängig davon, ob in Table2 eine übereinstimmende Zeile vorhanden ist.

  2. Die WHERE t2.ID IS NULL-Klausel; Dadurch werden die zurückgegebenen Ergebnisse nur auf die Zeilen beschränkt, in denen die von Table2 zurückgegebene ID null ist. Mit anderen Worten, es gibt NO für Table2 für diese bestimmte ID aus Table1. Table2.ID wird für alle Datensätze von Table1, bei denen die ID in Table2 nicht übereinstimmt, als NULL zurückgegeben.

465
AdaTheDev

Ich würde EXISTS expression verwenden, da er mächtiger ist. Sie können also die Zeilen, die Sie verbinden möchten, genauer auswählen. Im Falle von LEFT JOIN müssen Sie alles mitnehmen, was in der verbundenen Tabelle steht. Die Effizienz ist wahrscheinlich die gleiche wie bei LEFT JOIN mit Nulltest.

SELECT t1.ID
FROM Table1 t1
WHERE NOT EXISTS (SELECT t2.ID FROM Table2 t2 WHERE t1.ID = t2.ID)
45
Ondrej Bozek
SELECT id FROM table1 WHERE foreign_key_id_column NOT IN (SELECT id FROM table2)

Tabelle 1 enthält eine Spalte, zu der Sie die Fremdschlüsseleinschränkung hinzufügen möchten. Die Werte in foreign_key_id_column stimmen jedoch nicht alle mit einer id in Tabelle 2 überein.

  1. Die anfängliche Auswahl listet die ids von table1 auf. Dies sind die Zeilen, die wir löschen möchten. 
  2. Die NOT IN-Klausel in der where-Anweisung beschränkt die Abfrage nur auf Zeilen, in denen der Wert in foreign_key_id_column nicht in der Liste der Tabelle 2 ids enthalten ist. 
  3. Die SELECT-Anweisung in Klammern erhält eine Liste aller ids, die sich in Tabelle 2 befinden.
9
Theo Voss

Dabei ist T2 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ösche die Ergebnisse.

7
Karel

Wir haben die folgenden 2 Tabellen (Gehalt und Mitarbeiter)  enter image description here

Nun möchte ich die Datensätze aus Employee-Tabelle, die nicht im Gehalt sind. Wir können dies auf drei Arten tun-

  1. Inner Join verwenden

    wählen Sie * aus dem Mitarbeiter aus, bei dem die ID nicht enthalten ist (wählen Sie e.id aus dem Mitarbeiter e-Gehalt für inneres Beitritt bei e.id = s.id aus)

 enter image description here

  1. Left äußere Verbindung verwenden 

    wählen Sie * aus dem Angestellten e aus, der das äußere Join-Gehalt auf e.id = s.id bezieht, wobei s.id null ist

 enter image description here

  1. Full Join verwenden

    wählen Sie * aus dem Mitarbeiter und dem vollen äußeren Join-Gehalt s auf e.id = s.id aus, in dem sich e.id nicht befindet (wählen Sie die ID aus dem Gehalt aus)

 enter image description here

2
Debendra Dash

Von einer ähnlichen Frage hier MySQL Inner Join-Abfrage, um Datensätze zu erhalten, die nicht in anderen Tabellen vorhanden sind Ich habe dies zur Arbeit gebracht

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

In smalltable fehlen Ihnen Datensätze, in bigtable sind alle Datensätze vorhanden. Die Abfrage listet alle Datensätze auf, die nicht in smalltable vorhanden sind, aber in der bigtable vorhanden sind. Sie können id durch andere Übereinstimmungskriterien ersetzen.

1
Luis H Cabrejo

Ich wusste nicht, welches optimiert ist (im Vergleich zu @AdaTheDev), aber dieses scheint schneller zu sein, wenn ich es benutze (zumindest für mich)

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

Wenn Sie ein anderes bestimmtes Attribut erhalten möchten, können Sie Folgendes verwenden:

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

0
Jerin K. John

Sie können sich für Views entscheiden, wie unten gezeigt:

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 arbeiten Sie dann an der Ansicht zum Auswählen oder Aktualisieren:

select * from AuthorizedUserProjectView where projectid = 49

was zu dem Ergebnis führt, wie in der nachstehenden Abbildung gezeigt, d. h. für eine nicht übereinstimmende Spalte wurde Null eingegeben.

[Result of select on the view][1]
0
Nitesh singh