Korrelierte Unterabfrage in SQL: Ein Leitfaden für Anfänger

Manchmal ist die Verwendung einer korrelierten SQL-Subquery die einzige Möglichkeit, eine Anweisung zu lösen. Aber diese Unterabfragen können sehr langsam sein. In diesem Beitrag erfahren Sie, wann, warum und wie Sie eine korrelierte Unterabfrage verwenden sollten.

Unterabfragen sind ein wichtiges Hilfsmittel, um die Ausdruckskraft von SQL zu erhöhen. Wenn Sie unseren vorherigen Artikel nicht gelesen haben, sind Unterabfragen einfach eine SELECT -Anweisung innerhalb einer anderen SELECT. Sie können an verschiedenen Stellen innerhalb einer SELECT verwendet werden, z. B. in den Klauseln WHERE, HAVING oder FROM. Sie können sogar in anderen Anweisungen wie UPDATE oder DELETE verwendet werden.

Wie einfache Unterabfragen enthält eine korrelierte SQL-Unterabfrage eine Abfrage innerhalb einer Abfrage. Sie heißt so, weil die beiden Abfragen miteinander verbunden sind; die innere Abfrage verwendet Informationen, die sie von der äußeren Abfrage erhält (z. B. von einer Tabelle, auf die in der äußeren Abfrage verwiesen wird). Für die Programmierer unter Ihnen ist dies vergleichbar mit einer verschachtelten Schleifenstruktur.

Bevor wir mit einem Beispiel für eine korrelierte Unterabfrage in SQL fortfahren, lassen Sie uns eine kurze Auffrischung über einfache (nicht korrelierte) Unterabfragen vornehmen.

Eine einfache Unterabfrage

Das folgende SQL-Beispiel verwendet eine einfache Unterabfrage, um eine Liste der Mitarbeiter zu erhalten, die mehr als das durchschnittliche Unternehmensgehalt verdienen. In diesem Fall wird die Unterabfrage in fettgedrucktem Text angezeigt, und ihr Ergebnis ist das Durchschnittsgehalt des Unternehmens. Die äußere Abfrage vergleicht die Gehälter der einzelnen Mitarbeiter mit dem Unternehmensdurchschnitt. Ganz einfach!

SELECT 
  lastname, 
  firstname, 
  salary 
FROM employee 
WHERE salary > (SELECT avg(salary) 
                FROM employee)

Beispiel 1: Eine einfache nicht korrelierte SQL-Unterabfrage

Als nächstes kommen die korrelierten Unterabfragen. Diese sind etwas ganz Besonderes, denn manchmal sind sie die einzige Möglichkeit, eine Abfrage zu lösen. Denken Sie jedoch zweimal nach, bevor Sie eine korrelierte Unterabfrage in SQL verwenden. Sie können langsam sein, wie wir später erklären werden.

Verwendung einer korrelierten Unterabfrage in SQL

Beginnen wir mit einem Beispiel für eine korrelierte Unterabfrage in SQL. Angenommen, wir wollen alle Mitarbeiter finden, deren Gehalt höher ist als das durchschnittliche Abteilungsgehalt. Wir würden die folgende Abfrage verwenden. Auch hier habe ich die Unterabfrage fett gedruckt:

SELECT 
  lastname, 
  firstname, 
  salary
FROM employee e1
WHERE e1.salary > (SELECT avg(salary)
                   FROM employee e2 
                   WHERE e2.dept_id = e1.dept_id)

Beispiel 2: Eine korrelierte Unterabfrage in SQL

Der Hauptunterschied zwischen einer korrelierten SQL-Unterabfrage und einer einfachen Unterabfrage besteht darin, dass korrelierte Unterabfragen auf Spalten der äußeren Tabelle verweisen. Im obigen Beispiel ist e1.dept_id i ein Verweis auf die Tabelle der äußeren Unterabfrage. Um eine korrelierte Abfrage zu identifizieren, suchen Sie einfach nach dieser Art von Verweisen. Wenn Sie mindestens eine finden, handelt es sich um eine korrelierte SQL-Subquery!

Betrachten wir ein anderes Beispiel. Angenommen, wir möchten die Namen der Abteilungen mit mehr als 10 Mitarbeitern ermitteln. Wir können die folgende SQL-Subabfrage verwenden:

SELECT deptname
FROM department d1
WHERE 10 < (SELECT count(*)
            FROM employee e
            WHERE e.dept_id = d1.dept_id)

Beispiel 3: Eine weitere korrelierte Unterabfrage in SQL

Zeit für ein letztes Beispiel. Wir haben erwähnt, dass Unterabfragen Teil der Klauseln WHERE, FROM, HAVING und SELECT sein können. In diesem Beispiel verwenden wir eine korrelierte SQL-Unterabfrage in der Liste SELECT, um den Namen jedes Mitarbeiters, sein Gehalt und das Durchschnittsgehalt seiner Abteilung zu ermitteln. Das Durchschnittsgehalt erhalten wir mit einer korrelierten Unterabfrage in der SELECT.

Hier ist der Code:

SELECT 	
  lastname,
  firstname, 
  salary,
  (SELECT avg(salary) 
    FROM employee e2
    WHERE e2.dep_id = e1.dep_id) AS avg_dept_salary
FROM employee e1

Beispiel 4: Eine korrelierte SQL-Unterabfrage in der SELECT-Liste

Wie oft wird eine korrelierte SQL-Subabfrage ausgeführt?

Angenommen, wir haben eine Tabelle namens "assigned_to_project", in der die Namen der Mitarbeiter gespeichert sind, die Projekten zugewiesen sind. Wir wollen alle Mitarbeiter finden, die keinem Projekt zugewiesen sind. Die Lösung ist die folgende Abfrage:

SELECT 
  lastname, 
  firstname, 
  salary
FROM employee e1
WHERE NOT EXISTS (SELECT project_id
                  FROM assigned_to_project 
                  WHERE employee_id = e1.employee_id)

Beispiel 5: Eine korrelierte Unterabfrage in SQL

Zunächst einmal ist diese Abfrage leicht zu analysieren. Die NOT EXISTS Klausel ist TRUE, wenn die Subquery eine leere Ergebnismenge liefert. Dies geschieht nur für die Mitarbeiter, die keinem Projekt zugewiesen sind. Auch das ist ganz einfach!

In diesem Abschnitt geht es jedoch darum, zu analysieren, wie oft die mit SQL korrelierte Unterabfrage ausgeführt wird. Versuchen Sie herauszufinden, welche der folgenden Aussagen richtig ist:

1. Sie wird nur einmal ausgeführt.
2. Sie wird für jedes Projekt einmal ausgeführt.
3. Sie wird für jeden Mitarbeiter, der keinem Projekt zugeordnet ist, einmal ausgeführt.
4. Sie wird für jeden Mitarbeiter im Unternehmen einmal ausgeführt.

Lassen Sie uns diese Optionen analysieren. Angenommen, wir haben 1.000 Mitarbeiter und 20 Projekte. Außerdem haben wir 800 Mitarbeiter, die bereits einem Projekt zugewiesen sind. In diesem Fall hat die erste Option eine Ausführung, die zweite hat 20 Ausführungen, die dritte 200 und die letzte Option 1.000.

Wenn Sie sagen, dass die richtige Antwort "einmal für jeden Mitarbeiter des Unternehmens" lautet, dann haben Sie recht. Wenn wir die Anweisung durchgehen, wird diese Antwort offensichtlich; wir müssen prüfen, ob jeder Mitarbeiter Projekte hat oder nicht. Gemäß dem obigen Beispiel bedeutet dies jedoch, dass die Subquery 1.000 Mal ausgeführt wird! In Bezug auf die Leistung ist dies das schlechteste der vier Szenarien.

Da korrelierte Unterabfragen in der Regel viele Ausführungen erfordern, sind sie auch eher langsam. Aus diesem Grund versuchen wir in der Regel immer, die Verwendung einer korrelierten Unterabfrage in SQL zu vermeiden. Aber wie wir bereits erwähnt haben, ist die Korrelation manchmal die einzige Möglichkeit, eine Abfrage zu lösen.

Wann sollte man eine korrelierte Unterabfrage in SQL verwenden?

An dieser Stelle des Artikels sollte sich der Leser Gedanken machen: Wann sollte man eine korrelierte Unterabfrage in SQL verwenden? Es gibt tatsächlich einige Fälle, in denen dies notwendig ist. Dies gilt insbesondere für Abfragen, bei denen wir nach so genannten Negativen suchen.

Hier ist ein Beispiel für eine "negative" Abfrage. Nehmen wir an, wir haben eine Tabelle mit einer Zahlungshistorie mit einer Spalte namens payment_type, die angibt, ob es sich bei einer Zahlung um ein reguläres Gehalt, einen Bonus oder eine Prämie handelt. Wenn wir eine Abfrage wünschen, die die Mitarbeiter zurückgibt, die nie eine Prämie erhalten haben, würden wir diese Abfrage verwenden:

SELECT 
  lastname, 
  firstname
FROM employees e1
WHERE NOT EXISTS (SELECT ph.lastname 
                  FROM payment_history ph 
                  WHERE ph.emp_id = e1.employee_id 
                  AND ph.payment_type =’award’)

Beispiel 6: Eine SQL korrelierte Unterabfrage mit NOT EXISTS

EXISTS ist ein unärer Operator. Er hat nur einen Operanden, der eine Unterabfrage ist (korreliert oder nicht). Wenn die Unterabfrage mindestens einen Datensatz liefert, gibt EXISTS TRUE zurück. Wenn die Unterabfrage keine Datensätze zurückgibt, gibt EXISTS FALSE zurück. In diesem Fall müssen Sie eine korrelierte Unterabfrage verwenden, um Ihre Ergebnisse zu erhalten.

Verwendung korrelierter Unterabfragen in UPDATE- oder DELETE-Anweisungen

Manchmal finden wir korrelierte Unterabfragen in UPDATE- oder DELETE-Anweisungen. Die folgende UPDATE enthält eine SQL Correlated Subquery, die den neuen Wert der Spalte all_money_made ermittelt:

UPDATE employee emp
SET all_money_made = (SELECT SUM(payment)
                      FROM payment_history 
                      WHERE employee_id = emp.emp_id)

Beispiel 7: Eine SQL korrelierte Unterabfrage in einem UPDATE

Dieser Artikel hat uns gezeigt, wann eine korrelierte Unterabfrage in SQL verwendet werden sollte. Abfragen, die nach Negativen suchen, sind gute Kandidaten, obwohl es auch andere Gelegenheiten gibt, bei denen eine Korrelation die einzige echte Option ist. Wir haben auch gesehen, wie oft eine korrelierte Unterabfrage ausgeführt wird - in der Regel viele, viele Male. Dies ist ihr größter Nachteil.

Probieren Sie es selbst aus

Korrelierte Unterabfragen sind ein wichtiges Hilfsmittel für SQL-Entwickler. Um mehr darüber zu erfahren und Ihre Kenntnisse über Subqueries zu verbessern, besuchen Sie den Kurs LearnSQL.de's SQL für Anfänger. Dort gibt es einen speziellen Abschnitt für Subqueries sowie zahlreiche Übungen und Beispiele!/p>