Dies ist der zweite Teil einer Reihe von Artikeln über Unterabfragen. In diesem Artikel werden Unterabfragen in der Spaltenliste der SELECT-Anweisung erläutert. In anderen Artikeln wird ihre Verwendung in anderen Abschnitten erläutert.
Alle Beispiele für diese Lektion basieren auf Microsoft SQL Server Management Studio und der AdventureWorks2012-Datenbank. Sie können mit diesen kostenlosen Tools in meinem Handbuch Erste Schritte mit SQL Server beginnen.
Verwenden von Unterabfragen in der Select-Anweisung
Wenn eine Unterabfrage in die Spaltenliste eingefügt wird, wird sie verwendet Einzelwerte zurückgeben. In diesem Fall können Sie sich die Unterabfrage als einen Einzelwertausdruck vorstellen. Das zurückgegebene Ergebnis unterscheidet sich nicht vom Ausdruck „2 + 2“. Natürlich können Unterabfragen auch Text zurückgeben, aber Sie haben es verstanden!
Wenn Sie mit Unterabfragen arbeiten, wird die Hauptanweisung manchmal als äußere Abfrage bezeichnet. Unterabfragen sind in Klammern eingeschlossen, wodurch sie leichter zu erkennen sind
Seien Sie vorsichtig, wenn Sie Unterabfragen verwenden. Die Verwendung kann Spaß machen. Wenn Sie jedoch mehr zu Ihrer Abfrage hinzufügen, können sie Ihre Abfrage verlangsamen.
Einfache Unterabfrage zur Berechnung des Durchschnitts
Beginnen wir mit einer einfachen Abfrage, um SalesOrderDetail anzuzeigen und diese mit dem durchschnittlichen SalesOrderDetail LineTotal zu vergleichen. Die von uns verwendete SELECT-Anweisung lautet:
SELECT SalesOrderID,LineTotal,(SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotalFROM Sales.SalesOrderDetail;
Diese Abfrage gibt folgende Ergebnisse zurück:
Die oben rot dargestellte Unterabfrage wird zuerst ausgeführt um das durchschnittliche LineTotal zu erhalten.
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetail
Dieses Ergebnis wird dann wieder in die Spaltenliste eingefügt und die Abfrage wird fortgesetzt. Es gibt mehrere Dinge, auf die ich hinweisen möchte :
- Unterabfragen sind in Klammern eingeschlossen
- Wenn Unterabfragen in einer SELECT-Anweisung verwendet werden, können sie nur einen Wert zurückgeben. Dies sollte sinnvoll sein. Wenn Sie einfach eine Spalte auswählen, wird ein Wert für eine Zeile zurückgegeben, und wir müssen demselben Muster folgen.
- Im Allgemeinen wird die Unterabfrage nur einmal für die gesamte Abfrage ausgeführt und das Ergebnis erneut verwendet . Dies liegt daran, dass das Abfrageergebnis nicht für jede zurückgegebene Zeile variiert.
- Es ist wichtig, Aliase für die Spaltennamen zu verwenden, um die Lesbarkeit zu verbessern.
Einfache Unterabfrage in Ausdruck
Wie zu erwarten ist, kann das Ergebnis für eine Unterabfrage in anderen Ausdrücken verwendet werden. Aufbauend auf dem vorherigen Beispiel verwenden wir die Unterabfrage, um zu bestimmen, wie stark unser LineTotal vom Durchschnitt abweicht.
Die Varianz ist einfach das LineTotal abzüglich der durchschnittlichen Zeilensumme. In der folgenden Unterabfrage habe ich sie blau gefärbt. Hier ist die Formel für die Varianz:
LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail)
Die in Klammern eingeschlossene SELECT-Anweisung ist die Unterabfrage. Wie im vorherigen Beispiel wird diese Abfrage einmal ausgeführt und gibt einen numerischen Wert zurück, der dann von jedem LineTotal-Wert subtrahiert wird.
Hier ist die Abfrage in endgültiger Form:
SELECT SalesOrderID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotal, LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS VarianceFROM Sales.SalesOrderDetail
Hier ist das Ergebnis:
Wenn ich mit Unterabfragen in ausgewählten Anweisungen arbeite, die ich normalerweise erstelle und testen Sie zuerst die Unterabfrage. SELECT-Anweisungen können sehr schnell kompliziert werden. Es ist am besten, sie nach und nach aufzubauen. Wenn Sie die verschiedenen Teile separat erstellen und testen, hilft dies beim Debuggen.
Korrelierte Abfragen
Es gibt Möglichkeiten, die Werte der äußeren Abfrage in die Klauseln der Unterabfrage aufzunehmen. Diese Arten von Abfragen werden als korrelierte Unterabfragen bezeichnet, da die Ergebnisse der Unterabfrage in irgendeiner Form mit Werten in der äußeren Abfrage verknüpft sind. Korrelierte Abfragen werden manchmal als synchronisierte Abfragen bezeichnet.
Wenn Sie nicht wissen, was Korrelieren bedeutet, lesen Sie diese Definition von Google:
Korrelieren: „eine gegenseitige Beziehung oder Verbindung haben, in dem eine Sache eine andere beeinflusst oder von einer anderen abhängt. “
Eine typische Verwendung für eine korrelierte Unterabfrage wird in einer der Spalten der äußeren Abfrage in der WHERE-Klausel der inneren Abfrage verwendet. Dies ist in vielen Fällen der gesunde Menschenverstand Beschränken Sie die innere Abfrage auf eine Teilmenge von Daten.
Beispiel für eine korrelierte Unterabfrage
Wir stellen ein Beispiel für eine korrelierte Unterabfrage bereit, indem wir jedes SalesOrderDetail LineTotal und die durchschnittlichen LineTotals für den Gesamtumsatz zurückmelden Auftrag.
Diese Anforderung unterscheidet sich erheblich von unseren früheren Beispielen, da der von uns berechnete Durchschnitt für jeden Kundenauftrag unterschiedlich ist.
Hier kommen korrelierte Unterabfragen ins Spiel. Wir können a verwenden Wert aus der äußeren Abfrage und fügen Sie ihn in die Filterkriterien der Unterabfrage ein.
Nehmen wir a Schauen Sie sich an, wie wir die durchschnittliche Liniensumme berechnen. Zu diesem Zweck habe ich eine Abbildung zusammengestellt, die die SELECT-Anweisung mit Unterabfrage zeigt.
Um das näher zu erläutern Diagramm. Die SELECT-Anweisung besteht aus zwei Teilen, der äußeren Abfrage und der Unterabfrage. Die äußere Abfrage wird verwendet, um alle SalesOrderDetail-Zeilen abzurufen.Die Unterabfrage wird verwendet, um Kundenauftragsdetailzeilen für eine bestimmte SalesOrderID zu finden und zusammenzufassen.
Wenn ich die Schritte verbalisieren wollte Wir werden sie zusammenfassen, ich würde sie wie folgt zusammenfassen:
- Die SalesOrderID abrufen.
- Das durchschnittliche LineTotal von allen SalesOrderDetail-Elementen zurückgeben, bei denen die SalesOrderID übereinstimmt.
- Fahren Sie mit der nächsten SalesOrderID in der äußeren Abfrage fort und wiederholen Sie die Schritte 1 und 2.
Die Abfrage, die Sie in der AdventureWork2012-Datenbank ausführen können, lautet:
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail SOD
Hier sind die Ergebnisse der Abfrage:
Es gibt einige Zu beachtende Elemente.
- Sie können sehen, dass ich Spaltenaliasnamen verwendet habe, um das Ablesen der Abfrageergebnisse zu erleichtern.
- Ich habe auch einen Tabellenalias, SOD, für das verwendet äußere Abfrage. Auf diese Weise können die Werte der äußeren Abfrage in der Unterabfrage verwendet werden. Andernfalls ist die Abfrage nicht korreliert!
- Durch die Verwendung der Tabellenaliasnamen wird eindeutig, welche Spalten aus jeder Tabelle stammen.
Aufschlüsselung der korrelierten Unterabfrage
Versuchen wir nun, dies mithilfe von SQL aufzuschlüsseln.
Nehmen wir zunächst an, wir erhalten nur unser Beispiel für SalesOrderDetailID 20. Die entsprechende SalesOrderID lautet 43661.
Das Ermitteln des durchschnittlichen LineTotal für dieses Element ist einfach.
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetailWHERE SalesOrderID = 43661
Dies gibt den Wert 2181.765240 zurück.
Jetzt haben wir den Durchschnitt, den wir können Schließen Sie es an unsere Abfrage an.
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, 2181.765240 AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
Bei Verwendung von Unterabfragen wird dies zu
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43661) AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
Die endgültige Abfrage lautet :
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail AS SOD
Korrelierte Unterabfrage mit einer anderen Tabelle
Eine korrelierte Unterabfrage oder für jede Unterabfrage kann eine andere Tabelle als verwenden die äußere Abfrage. Dies kann nützlich sein, wenn Sie mit einer „übergeordneten“ Tabelle wie SalesOrderHeader arbeiten und eine Zusammenfassung der untergeordneten Zeilen, wie z. B. die von SalesOrderDetail, in das Ergebnis aufnehmen möchten.
Lassen Sie uns die zurückgeben OrderDate, TotalDue und Anzahl der Detailzeilen für Kundenaufträge. Dazu können wir uns anhand des folgenden Diagramms orientieren:
Dazu fügen wir eine korrelierte Unterabfrage in unsere SELECT-Anweisung ein, um die Anzahl der SalesOrderDetail-Zeilen zurückzugeben. Wir stellen sicher, dass wir das richtige SalesOrderDetail-Element zählen, indem wir nach der SalesOrderID der äußeren Abfrage filtern.
Hier ist die letzte SELECT-Anweisung:
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
Die Ergebnisse sind:
Bei diesem Beispiel sind folgende Punkte zu beachten:
- Die Unterabfrage wählt Daten aus einer anderen Tabelle als die äußere Abfrage aus.
- Ich habe Tabelle und verwendet Spalten-Aliase, um das Lesen von SQL und Ergebnissen zu vereinfachen.
- Stellen Sie sicher, dass Sie doppelt prüfen k Ihre where-Klausel! Wenn Sie vergessen, den Tabellennamen oder die Aliase in die WHERE-Klausel der Unterabfrage aufzunehmen, wird die Abfrage nicht korreliert.
Korrelierte Unterabfragen im Vergleich zu inneren Verknüpfungen
Dies ist wichtig um zu verstehen, dass Sie dieselben Ergebnisse entweder mit einer Unterabfrage oder mit Join erzielen können. Obwohl beide die gleichen Ergebnisse liefern, hat jede Methode Vor- und Nachteile!
Betrachten Sie das letzte Beispiel, in dem wir Werbebuchungen für SalesHeader-Positionen zählen.
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
Dieselbe Abfrage kann mit einem INNER JOIN zusammen mit GROUP BY als
SELECT SO.SalesOrderID, OrderDate, TotalDue, COUNT(SOD.SalesOrderDetailID) as LineCountFROM Sales.SalesOrderHeader SO INNER JOIN Sales.SalesOrderDetail SOD ON SOD.SalesOrderID = SO.SalesOrderIDGROUP BY SO.SalesOrderID, OrderDate, TotalDue
durchgeführt werden. Welches ist schneller?
Sie werden feststellen, dass viele Leute sagen, Unterabfragen zu vermeiden, da sie langsamer sind. Sie werden argumentieren, dass die korrelierte Unterabfrage für jede in der äußeren Abfrage zurückgegebene Zeile einmal „ausgeführt“ werden muss, während der INNER JOIN nur einen Durchgang durch die Daten machen muss.
Ich selbst? Ich sage Auschecken Ich habe meinen eigenen Rat für beide oben genannten Beispiele befolgt und festgestellt, dass die Pläne gleich sind!
Das heißt nicht, dass sich die Pläne ändern würden, wenn mehr Daten vorhanden wären, aber mein Punkt ist, dass Sie nicht nur Annahmen treffen sollten. Die meisten SQL DBMS-Optimierer sind wirklich gut darin, herauszufinden, wie Sie Ihre Abfrage am besten ausführen können. Sie verwenden Ihre Syntaxen wie eine Unterabfrage oder INNER JOIN und verwenden sie, um eine zu erstellen tatsächlicher Ausführungsplan.
Welcher ist einfacher zu lesen?
Je nachdem, was Sie möchten, ist das INNER JOIN-Beispiel möglicherweise leichter zu lesen als die korrelierte Abfrage. In diesem Beispiel gefällt mir die korrelierte Unterabfrage, da sie direkter erscheint. Für mich ist es einfacher zu sehen, was gezählt wird.
In meinen Augen ist der INNER JOIN weniger direkt. Zuerst müssen Sie sehen, dass alle Verkaufsdetailzeilen von Hand zurückgegeben und dann zusammengefasst werden. Sie bekommen das erst wirklich, wenn Sie die gesamte Aussage gelesen haben.
Welches ist besser?
Lassen Sie mich wissen, was Sie denken. Ich würde gerne hören, ob Sie lieber die korrelierte Unterabfrage oder das Beispiel INNER JOIN verwenden möchten.