Antwort.

Hintergrund des Themas:

Die Operatoren EXISTS und IN werden zur Filterung von Datensätzen basierend auf Unterabfragen verwendet. Seit der Einführung von SQL standen Entwickler vor der Wahl zwischen ihnen und versuchten zu verstehen, welche Methode schneller arbeitet und in welchen Fällen ihre Anwendung vorzuziehen ist.

Problem:

Die Hauptaufgabe besteht darin, nur die Zeilen zu erhalten, die eine Entsprechung in der externen oder internen Tabelle haben, was bei großen Datensätzen immer entscheidend für die Leistung ist. Die Wahl zwischen EXISTS und IN hängt von der Struktur der Unterabfrage, der Anzahl und Einzigartigkeit der zurückgegebenen Werte sowie vom verwendeten DBMS ab.

Lösung:

IN ist normalerweise effizienter, wenn die Unterabfrage eine kleine Anzahl einzigartiger Werte zurückgibt.
EXISTS ist vorzuziehen, wenn nur die Tatsache der Existenz entsprechender Zeilen wichtig ist; es eignet sich für große Unterabfragen, die Tausende und Millionen von Zeilen zurückgeben.
Man muss auch das spezifische Verhalten bei NULL und die Unterschiede in der Optimierung verschiedener Datenbanksysteme beachten.

Beispielcode:

-- Verwendung von IN
SELECT name FROM students WHERE id IN (SELECT student_id FROM enrollments WHERE course = 'SQL');

-- Verwendung von EXISTS
SELECT name FROM students WHERE EXISTS (SELECT 1 FROM enrollments WHERE enrollments.student_id = students.id AND enrollments.course = 'SQL');

Wesentliche Merkmale:

EXISTS beendet die Suche beim ersten Treffer (die untergeordnete Abfrage kann beendet werden).
IN ist normalerweise effizient bei kurzen Listen, kann jedoch bei langen, nicht eindeutigen Unterabfragen langsam sein.
EXISTS funktioniert korrekt mit NULL, IN kann sich unerwartet verhalten, wenn NULL in der Unterabfrage vorhanden ist.

Fangfragen.

Was passiert, wenn NULL in der IN-Unterabfrage vorkommt?

Viele glauben, dass IN NULL einfach ignoriert, aber wenn NULL vorhanden ist, kann das Ergebnis unvorhersehbar werden. Zum Beispiel wird die Abfrage:

SELECT id FROM orders WHERE client_id IN (1, NULL, 2);

technisch keine Zeilen zurückgeben, bei denen client_id nicht 1 oder 2 ist, aber wenn nur NULL in der Unterabfrage auftaucht, wird das Ergebnis leer sein.

Sind EXISTS und IN vollständig austauschbare Konstrukte?

Nein. Die Verwendung von EXISTS ist oft schneller, da nicht die gesamte Unterabfrage analysiert werden muss. Darüber hinaus funktioniert IN nicht mit Unterabfragen, die mehrere Spalten zurückgeben, EXISTS jedoch schon, da der Vergleich in der WHERE-Klausel erfolgt. Zum Beispiel:

SELECT col1 FROM t1 WHERE (col1, col2) IN (SELECT col3, col4 FROM t2);

Diese Variante wird oft nicht unterstützt, die entsprechende EXISTS-Version jedoch schon.

Kann IN schneller sein als EXISTS, wenn mit indizierten Feldern gearbeitet wird?

Ja, wenn die Unterabfrage klein ist und auf dem zu vergleichenden Feld ein Index vorhanden ist, kann IN schneller sein. Bei großen Auswahlmengen oder fehlenden Indizes gilt jedoch das Gegenteil.

Typische Fehler und Anti-Patterns

Verwendung von IN mit einer Unterabfrage, die eine große Anzahl von Zeilen zurückgibt.
Vernachlässigung der Konsequenzen von NULL in der Unterabfrage.
EXISTS standardmäßig durch IN in allen Fällen ersetzen, ohne den Abfrageplan zu analysieren.

Beispiel aus dem Leben

Negativer Fall

Ein Analyst erstellte einen Bericht mit der Methode IN, ohne zu berücksichtigen, dass die Unterabfrage Hunderttausende von Zeilen mit NULL zurückgibt. Der Bericht benötigte Minuten und verlor manchmal Daten.

Vorteile:

Logik ist einfach zu verstehen Nachteile:
Verlust an Leistung
Risiko ungenauer Daten

Positiver Fall

Die gleiche Abfrage wurde auf EXISTS umgeschrieben mit einer zusätzlichen Bedingung, die Indizes wurden neu gezählt.