wake-up-neo.com

SQL-Abfrage, um einen Datensatz mit einer ID zu finden, der nicht in einer anderen Tabelle enthalten ist

Ich habe zwei Tabellen mit verbindlichem Primärschlüssel in der Datenbank und möchte einen disjunkten Satz zwischen ihnen finden. Beispielsweise,

  • Table1 Enthält Spalten (ID, Name) Und Beispieldaten: (1 ,John), (2, Peter), (3, Mary)
  • Table2 Enthält Spalten (ID, Address) Und Beispieldaten: (1, address2), (2, address2)

Wie erstelle ich eine SQL-Abfrage, um die Zeile mit der ID aus table1 Abzurufen, die nicht in table2 Enthalten ist? In diesem Fall sollte (3, Mary) Zurückgegeben werden?

Ps. Die ID ist der Primärschlüssel für diese beiden Tabellen.

Danke im Voraus.

102
johnklee

Versuche dies

SELECT ID, Name 
FROM   Table1 
WHERE  ID NOT IN (SELECT ID FROM Table2)
179
Prince Jea

Verwenden LEFT JOIN

SELECT  a.*
FROM    table1 a
            LEFT JOIN table2 b
                on a.ID = b.ID
WHERE   b.id IS NULL
79
John Woo

Schnelle Alternative

Ich habe einige Tests (auf Postgres 9.5) mit zwei Tabellen mit jeweils ~ 2 Millionen Zeilen durchgeführt. Diese folgende Abfrage erbrachte mindestens 5 * mehr Leistung als die anderen vorgeschlagenen Abfragen:

-- Count
SELECT count(*) FROM (
    (SELECT id FROM table1) EXCEPT (SELECT id FROM table2)
) t1_not_in_t2;

-- Get full row
SELECT table1.* FROM (
    (SELECT id FROM table1) EXCEPT (SELECT id FROM table2)
) t1_not_in_t2 JOIN table1 ON t1_not_in_t2.id=table1.id;
9
polvoazul

In Anbetracht der Punkte, die in @ John Woos Kommentar/Link oben gemacht wurden, würde ich normalerweise so damit umgehen:

SELECT t1.ID, t1.Name 
FROM   Table1 t1
WHERE  NOT EXISTS (
    SELECT TOP 1 NULL
    FROM Table2 t2
    WHERE t1.ID = t2.ID
)
5
CaseyR

Grundsätzlich gibt es drei Ansätze: not exists, not in Und left join / is null.

LEFT JOIN mit IS NULL

SELECT  l.*
FROM    t_left l
LEFT JOIN
        t_right r
ON      r.value = l.value
WHERE   r.value IS NULL

NICHT IN

SELECT  l.*
FROM    t_left l
WHERE   l.value NOT IN
        (
        SELECT  value
        FROM    t_right r
        )

EXISTIERT NICHT

SELECT  l.*
FROM    t_left l
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    t_right r
        WHERE   r.value = l.value
        )

Welches ist besser? Die Antwort auf diese Frage ist möglicherweise besser, wenn Sie sie auf wichtige spezifische RDBMS-Anbieter aufteilen. Generell sollte man die Verwendung von select ... where ... in (select...) vermeiden, wenn die Größe der Anzahl der Datensätze in der Unterabfrage unbekannt ist. Einige Anbieter beschränken möglicherweise die Größe. Oracle hat zum Beispiel ein Limit von 1.0 . Probieren Sie am besten alle drei aus und zeigen Sie den Ausführungsplan.

Insbesondere bei PostgreSQL sind die Ausführungspläne von NOT EXISTS Und LEFT JOIN / IS NULL Identisch. Ich persönlich bevorzuge die Option NOT EXISTS, Da sie die Absicht besser zeigt. Immerhin ist die Semantik, dass Sie wollen Datensätze in A finden, dass sein pk nicht existiert in B.

Alt, aber immer noch Gold, speziell für PostgreSQL: https://explainextended.com/2009/09/16/not-in-vs-not-exists-vs-left-join-is-null-postgresql/

4
L. Holanda
SELECT COUNT(ID) FROM tblA a
WHERE a.ID NOT IN (SELECT b.ID FROM tblB b)    --For count


SELECT ID FROM tblA a
WHERE a.ID NOT IN (SELECT b.ID FROM tblB b)    --For results
2
JoshYates1980