Unterabfragen (SQL Server)
Gilt für: SQL Server
Azure SQL-Datenbank
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
Eine Unterabfrage ist eine Abfrage, die in einer SELECT
-, INSERT
-, UPDATE
- oder DELETE
-Anweisung bzw. in einer anderen Unterabfrage geschachtelt ist.
Die Transact-SQL-Codebeispiele in diesem Artikel verwenden die AdventureWorks2022
- oder AdventureWorksDW2022
-Beispieldatenbank, die Sie von der Homepage Microsoft SQL Server Samples and Community Projects herunterladen können.
Eine Unterabfrage kann überall dort verwendet werden, wo ein Ausdruck zulässig ist. In diesem Beispiel wird eine Unterabfrage als Spaltenausdruck mit dem Namen „MaxUnitPrice“ in einer SELECT
-Anweisung verwendet.
USE AdventureWorks2022;
GO
SELECT Ord.SalesOrderID, Ord.OrderDate,
(SELECT MAX(OrdDet.UnitPrice)
FROM Sales.SalesOrderDetail AS OrdDet
WHERE Ord.SalesOrderID = OrdDet.SalesOrderID) AS MaxUnitPrice
FROM Sales.SalesOrderHeader AS Ord;
GO
Grundlegende Informationen zu Unterabfragen
Eine Unterabfrage wird auch innere Abfrage oder innere SELECT-Anweisung genannt, während die Anweisung mit einer Unterabfrage als äußere Abfrage oder äußere SELECT-Anweisung bezeichnet wird.
Viele Transact-SQL-Anweisungen, die Unterabfragen einschließen, können auch als Joins formuliert werden. Andere Fragestellungen können nur mithilfe von Unterabfragen formuliert werden. In Transact-SQL gibt es normalerweise keinen Leistungsunterschied zwischen einer Anweisung, die eine Unterabfrage enthält, und einer semantisch gleichbedeutenden Version ohne Unterabfrage. Architekturinformationen zur Verarbeitung von Abfragen durch SQL Server- finden Sie unter SQL-Anweisungsverarbeitung. In manchen Fällen, in denen das Vorhandensein bestimmter Daten überprüft werden muss, wird mit einem Join jedoch eine bessere Leistung erzielt. Denn ansonsten muss die geschachtelte Abfrage für jedes einzelne Ergebnis der äußeren Abfrage verarbeitet werden, damit die Entfernung von Duplikaten sichergestellt ist. In solchen Fällen erzielt ein Join bessere Ergebnisse.
Das folgende Beispiel zeigt sowohl eine Unterabfrage SELECT
und einen Join SELECT
. Beide geben dasselbe Resultset und denselben Ausführungsplan zurück:
USE AdventureWorks2022;
GO
/* SELECT statement built using a subquery. */
SELECT [Name]
FROM Production.Product
WHERE ListPrice =
(SELECT ListPrice
FROM Production.Product
WHERE [Name] = 'Chainring Bolts' );
GO
/* SELECT statement built using a join that returns
the same result set. */
SELECT Prd1.[Name]
FROM Production.Product AS Prd1
JOIN Production.Product AS Prd2
ON (Prd1.ListPrice = Prd2.ListPrice)
WHERE Prd2.[Name] = 'Chainring Bolts';
GO
Eine Unterabfrage, die in einer äußeren SELECT-Anweisung geschachtelt ist, besitzt folgende Komponenten:
- Eine reguläre
SELECT
-Abfrage mit den normalen Auswahllistenkomponenten. - Eine reguläre
FROM
-Klausel mit einem oder mehreren Tabellen- oder Sichtnamen. - Einer optionalen
WHERE
-Klausel. - Einer optionalen
GROUP BY
-Klausel. - Einer optionalen
HAVING
-Klausel.
Die SELECT-Abfrage einer Unterabfrage wird immer in Klammern eingeschlossen. Sie kann keine COMPUTE
- oder FOR BROWSE
-Klausel enthalten und darf nur dann eine ORDER BY
-Klausel einschließen, wenn auch eine TOP-Klausel angegeben ist.
Eine Unterabfrage kann in der WHERE
- oder HAVING
-Klausel einer äußeren SELECT
-, INSERT
-, UPDATE
- oder DELETE
-Anweisung oder in einer anderen Unterabfrage geschachtelt sein. Bis zu 32 Schachtelungsebenen sind möglich. Allerdings variiert das Limit in Abhängigkeit vom verfügbaren Arbeitsspeicher und der Komplexität anderer Ausdrücke in der Abfrage. Einzelne Abfragen unterstützen keine Schachtelung bis zu 32 Ebenen. Sofern eine Unterabfrage einen einzelnen Wert zurückgibt, kann sie in allen Fällen auftreten, in denen auch ein Ausdruck verwendet werden kann.
Wenn eine Tabelle nur in einer Unterabfrage, jedoch nicht in der äußeren Abfrage verwendet wird, können Spalten aus dieser Tabelle nicht in die Ausgabe (die Auswahlliste der äußeren Abfrage) eingeschlossen werden.
Anweisungen, die eine Unterabfrage einschließen, besitzen in der Regel eines der folgenden Formate:
WHERE expression [NOT] IN (subquery)
WHERE expression comparison_operator [ANY | ALL] (subquery)
WHERE [NOT] EXISTS (subquery)
In manchen Transact-SQL-Anweisungen kann die Unterabfrage wie eine unabhängige Abfrage ausgewertet werden. Grundsätzlich werden die Ergebnisse der Unterabfrage in die äußere Abfrage eingesetzt (auch wenn Transact-SQL-Anweisungen mit Unterabfragen von SQL Server nicht unbedingt auf diese Weise verarbeitet werden).
Es gibt drei grundlegende Arten von Unterabfragen. Arten:
- Unterabfragen, die Listen bearbeiten und mit
IN
eingeleitet werden, oder Unterabfragen, die mit einem durchANY
oderALL
geänderten Vergleichsoperator eingeleitet werden. - Unterabfragen, die mit einem nicht geänderten Vergleichsoperator eingeleitet werden und einen einzelnen Wert zurückgeben müssen.
- Unterabfragen, die mit
EXISTS
eingeleitete Tests auf Vorhandensein bestimmter Daten darstellen.
Regeln für Unterabfragen
Eine Unterabfrage unterliegt den folgenden Beschränkungen:
- Die Auswahlliste einer mit einem Vergleichsoperator eingeleiteten Unterabfrage darf nur einen einzigen Ausdruck oder Spaltennamen einschließen (außer bei
EXISTS
undIN
, die sich auf Anweisungen mitSELECT *
bzw. auf eine Liste beziehen). - Wenn die
WHERE
-Klausel einer äußeren Abfrage einen Spaltennamen einschließt, muss sie mit der Spalte in der Auswahlliste der Unterabfrage verknüpfbar sein (kompatible Datentypen). - Die Datentypen ntext, text und image können nicht in der Auswahlliste von Unterabfragen verwendet werden.
- Da sie einen einzelnen Wert zurückgeben müssen, können Unterabfragen, die mit einem nicht geänderten Vergleichsoperator (dem nicht das Schlüsselwort
ANY
oderALL
folgt) eingeleitet werden, keineGROUP BY
- undHAVING
-Klauseln enthalten. - Das
DISTINCT
-Schlüsselwort darf nicht bei Unterabfragen verwendet werden, dieGROUP BY
einschließen. - Die
COMPUTE
- undINTO
-Klauseln können nicht angegeben werden. ORDER BY
kann nur angegeben werden, wenn auchTOP
angegeben wird.- Eine mit einer Unterabfrage erstellte Anzeige kann nicht aktualisiert werden.
- Die Auswahlliste einer mit
EXISTS
eingeleiteten Unterabfrage besitzt laut Vereinbarung ein Sternchen (*
) statt eines einzelnen Spaltennamens. Die Regeln für eine mitEXISTS
eingeleitete Unterabfrage entsprechen den Regeln für eine standardmäßige Auswahlliste, da eine mitEXISTS
eingeleitete Unterabfrage einen Test auf das Vorhandensein bestimmter Daten erstellt und keine Daten, sondern TRUE oder FALSE zurückgibt.
Qualifizieren von Spaltennamen in Unterabfragen
Im folgenden Beispiel wird die BusinessEntityID
-Spalte in der WHERE
-Klausel der äußeren Abfrage implizit durch den Tabellennamen (FROM
) in der Sales.Store
-Klausel der äußeren Abfrage qualifiziert. Der Verweis auf CustomerID
in der Auswahlliste der Unterabfrage wird durch die FROM
-Klausel der Unterabfrage qualifiziert, also durch die Sales.Customer
-Tabelle.
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Sales.Store
WHERE BusinessEntityID NOT IN
(SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID = 5);
GO
Allgemein gilt die Regel, dass Spaltennamen in einer Anweisung implizit durch die Tabelle qualifiziert werden, auf die in der FROM
-Klausel derselben Ebene verwiesen wird. Wenn eine Spalte in der Tabelle nicht vorhanden ist, auf die in einer FROM
-Klausel einer Unterabfrage verwiesen wird, wird sie implizit durch die Tabelle qualifiziert, auf die in der FROM
-Klausel der äußeren Abfrage verwiesen wird.
Werden diese impliziten Annahmen angegeben, lautet die Abfrage folgendermaßen:
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Sales.Store
WHERE Sales.Store.BusinessEntityID NOT IN
(SELECT Sales.Customer.CustomerID
FROM Sales.Customer
WHERE TerritoryID = 5);
GO
Es empfiehlt sich immer, den Tabellennamen explizit anzugeben, und es ist immer möglich, implizite Annahmen zu Tabellennamen durch explizite Qualifizierungen zu überschreiben.
Wichtig
Wenn in einer Unterabfrage auf eine Spalte verwiesen wird, die nicht in der Tabelle vorhanden ist, auf die in der FROM
-Klausel der Unterabfrage verwiesen wird, die jedoch in einer Tabelle vorhanden ist, auf die durch die FROM
-Klausel der äußeren Abfrage verwiesen wird, wird die Abfrage ohne Fehler ausgeführt. SQL Server qualifiziert die Spalte in der Unterabfrage implizit mit dem Tabellennamen in der äußeren Abfrage.
Mehrere Schachtelungsebenen
Eine Unterabfrage kann selbst wiederum eine oder mehrere Unterabfragen beinhalten. In einer Anweisung können beliebig viele Unterabfragen geschachtelt sein.
Die folgende Abfrage sucht die Namen aller Mitarbeiter, die im Vertrieb arbeiten.
USE AdventureWorks2022;
GO
SELECT LastName, FirstName
FROM Person.Person
WHERE BusinessEntityID IN
(SELECT BusinessEntityID
FROM HumanResources.Employee
WHERE BusinessEntityID IN
(SELECT BusinessEntityID
FROM Sales.SalesPerson)
);
GO
Hier ist das Resultset.
LastName FirstName
-------------------------------------------------- -----------------------
Jiang Stephen
Abbas Syed
Alberts Amy
Ansman-Wolfe Pamela
Campbell David
Carson Jillian
Ito Shu
Mitchell Linda
Reiter Tsvi
Saraiva Jos
Vargas Garrett
Varkey Chudukatil Ranjit
Valdez Rachel
Tsoflias Lynn
Pak Jae
Blythe Michael
Mensa-Annan Tete
(17 row(s) affected)
Die innerste Abfrage gibt die IDs der Vertriebsmitarbeiter zurück. Die Abfrage auf der nächsthöheren Ebene wird mit diesen Vertriebsmitarbeiter-IDs ausgewertet und gibt die Kontakt-ID-Nummern der Mitarbeiter zurück. Zuletzt ermittelt die äußerste Abfrage anhand der Kontakt-IDs die Namen der Mitarbeiter.
Sie können diese Abfrage auch als Join ausdrücken:
USE AdventureWorks2022;
GO
SELECT LastName, FirstName
FROM Person.Person c
INNER JOIN HumanResources.Employee e
ON c.BusinessEntityID = e.BusinessEntityID
JOIN Sales.SalesPerson s
ON e.BusinessEntityID = s.BusinessEntityID;
GO
Correlated subqueries (Korrelierte Unterabfragen)
Viele Abfragen können ausgewertet werden, indem die Unterabfrage einmal ausgeführt wird und der Ergebniswert oder die -werte in die WHERE
-Klausel der äußeren Abfrage eingesetzt werden. In Abfragen mit einer korrelierten Unterabfrage (auch wiederholte Unterabfrage genannt) hängt die Unterabfrage für ihre Werte von der äußeren Abfrage ab. Das bedeutet, dass die Unterabfrage wiederholt ausgeführt wird, und zwar einmal für jede Zeile, die von der äußeren Abfrage ausgewählt werden könnte.
Diese Abfrage ruft eine Instanz des Vor- und Nachnamens jedes Mitarbeiters ab, für den der Bonus in der SalesPerson
-Tabelle 5000 ist und für den die Mitarbeiteridentifikationsnummern in den Tabellen Employee
und SalesPerson
übereinstimmen.
USE AdventureWorks2022;
GO
SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID
WHERE 5000.00 IN
(SELECT Bonus
FROM Sales.SalesPerson sp
WHERE e.BusinessEntityID = sp.BusinessEntityID) ;
GO
Hier ist das Resultset.
LastName FirstName BusinessEntityID
-------------------------- ---------- ------------
Ansman-Wolfe Pamela 280
Saraiva José 282
(2 row(s) affected)
Die vorherige Unterabfrage in dieser Anweisung kann nicht unabhängig von der äußeren Abfrage ausgewertet werden. Sie benötigt einen Wert für Employee.BusinessEntityID
, wobei sich dieser Wert ändert, wenn SQL Server unterschiedliche Zeilen in Employee
untersucht.
Auf die gleiche Weise wird diese Abfrage ausgewertet: SQL Server untersucht für jede Zeile der Employee
-Tabelle die Aufnahme in die Ergebnisse, indem in der inneren Abfrage der Wert jeder Zeile ersetzt wird.
Wenn SQL Server beispielsweise zunächst die Zeile für Syed Abbas
überprüft, nimmt die Variable Employee.BusinessEntityID
den Wert 285
an, den SQL Server in die innere Abfrage einsetzt. Diese beiden Abfragebeispiele stellen eine Zerlegung des vorherigen Beispiels mit der korrelierten Unterabfrage dar.
USE AdventureWorks2022;
GO
SELECT Bonus
FROM Sales.SalesPerson
WHERE BusinessEntityID = 285;
GO
Das Ergebnis ist 0.00 (Syed Abbas
erhielt keine Prämie, weil er kein Vertriebsmitarbeiter ist), sodass die äußere Abfrage ausgewertet wird zu:
USE AdventureWorks2022;
GO
SELECT LastName, FirstName
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID
WHERE 5000 IN (0.00);
GO
Da dies FALSE ergibt, ist die Zeile für Syed Abbas
nicht in den Ergebnissen der vorherigen Beispielabfrage mit der korrelierten Unterabfrage enthalten. Verfahren Sie mit der Zeile zu Pamela Ansman-Wolfe
auf die gleiche Weise. Sie werden feststellen, dass diese Zeile in den Ergebnissen enthalten ist, da WHERE 5000 IN (5000)
Ergebnisse enthält.
Korrelierte Unterabfragen können auch Tabellenwertfunktionen in die FROM
-Klausel einschließen, indem ein Verweis auf Spalten aus einer Tabelle in der äußeren Abfrage als ein Argument der Tabellenwertfunktion erfolgt. In diesem Fall wird die Tabellenwertfunktion für jede Zeile in der äußeren Abfrage entsprechend der Unterabfrage bewertet.
Subquery types (Arten von Unterabfragen)
Unterabfragen können an vielen Stellen angegeben werden:
- Mit Aliasnamen. Weitere Informationen finden Sie unter Unterabfragen mit Tabellenaliasen.
- Mit
IN
oderNOT IN
. Weitere Informationen finden Sie unter Unterabfragen mit IN und Unterabfragen mit NOT IN. - In
UPDATE
-,DELETE
- undINSERT
-Anweisungen. Weitere Informationen finden Sie unter Unterabfragen in den Anweisungen UPDATE, DELETE und INSERT. - Mit Vergleichsoperatoren. Weitere Informationen finden Sie unter Unterabfragen mit Vergleichsoperatoren.
- Mit
ANY
,SOME
oderALL
. Weitere Informationen finden Sie unter Mit ANY, SOME oder ALL modifizierte Vergleichsoperatoren. - Mit
IS [NOT] DISTINCT FROM
: Weitere Informationen finden Sie unter IS [NOT] DISTINCT FROM (Transact-SQL). - Mit
EXISTS
oderNOT EXISTS
. Weitere Informationen finden Sie unter Unterabfragen mit EXISTS und Unterabfragen mit NOT EXISTS. - Anstelle von Ausdrücken. Weitere Informationen finden Sie unter Anstelle von Ausdrücken verwendete Unterabfragen.
Unterabfragen mit Tabellenaliasen
Viele Anweisungen, in denen die Unterabfrage und die äußere Abfrage auf dieselbe Tabelle verweisen, können als Selbstjoin (Verknüpfungen einer Tabelle mit sich selbst) ausgedrückt werden. Beispielsweise können die Adressen von Mitarbeitern aus einem bestimmten Bundesstaat mit einer Unterabfrage gesucht werden:
USE AdventureWorks2022;
GO
SELECT StateProvinceID, AddressID
FROM Person.Address
WHERE AddressID IN
(SELECT AddressID
FROM Person.Address
WHERE StateProvinceID = 39);
GO
Hier ist das Resultset.
StateProvinceID AddressID
----------- -----------
39 942
39 955
39 972
39 22660
(4 row(s) affected)
Sie können auch einen Selbstjoin verwenden:
USE AdventureWorks2022;
GO
SELECT e1.StateProvinceID, e1.AddressID
FROM Person.Address AS e1
INNER JOIN Person.Address AS e2
ON e1.AddressID = e2.AddressID
AND e2.StateProvinceID = 39;
GO
Tabellenaliase e1
und e2
sind erforderlich, weil die mit sich selbst verknüpfte Tabelle zwei verschiedene Funktionen erfüllt. Aliasnamen können auch in geschachtelten Abfragen verwendet werden, in denen sowohl die innere als auch die äußere Abfrage auf dieselbe Tabelle verweisen.
USE AdventureWorks2022;
GO
SELECT e1.StateProvinceID, e1.AddressID
FROM Person.Address AS e1
WHERE e1.AddressID IN
(SELECT e2.AddressID
FROM Person.Address AS e2
WHERE e2.StateProvinceID = 39);
GO
Explizite Tabellenaliase machen deutlich, dass ein Verweis auf Person.Address
in der Unterabfrage eine andere Bedeutung als der Verweis in der äußeren Abfrage hat.
Unterabfragen mit IN
Das Ergebnis einer mit IN
(oder mit NOT IN
) eingeleiteten Unterabfrage entspricht einer Liste aus 0 oder mehr Werten. Sobald die Unterabfrage Ergebnisse zurückgibt, werden diese von der äußeren Abfrage verwendet.
Die folgende Abfrage sucht die Namen aller Wheel-Produkte, die Adventure Works Cycles herstellt.
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN
(SELECT ProductSubcategoryID
FROM Production.ProductSubcategory
WHERE [Name] = 'Wheels');
GO
Hier ist das Resultset.
Name
----------------------------
LL Mountain Front Wheel
ML Mountain Front Wheel
HL Mountain Front Wheel
LL Road Front Wheel
ML Road Front Wheel
HL Road Front Wheel
Touring Front Wheel
LL Mountain Rear Wheel
ML Mountain Rear Wheel
HL Mountain Rear Wheel
LL Road Rear Wheel
ML Road Rear Wheel
HL Road Rear Wheel
Touring Rear Wheel
(14 row(s) affected)
Diese Anweisung wird in zwei Schritten ausgewertet. Die innere Abfrage gibt zunächst die Unterkategorie-ID zurück, die dem Namen Wheel
(17
) entspricht. Danach wird dieser Wert in die äußere Abfrage eingesetzt, die die zu den Unterkategorie-IDs gehörenden Produktnamen in Production.Product
findet.
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN ('17');
GO
Beim Verwenden eines Joins statt einer Unterabfrage zeigt sich u. a. folgender Unterschied: Wenn Sie für dieses und ähnliche Probleme einen Join statt einer Unterabfrage verwenden, können Sie im Ergebnis die Spalten aus mehreren Tabellen anzeigen. Wenn Sie beispielsweise den Namen der Produktunterkategorie in die Ergebnisse einschließen möchten, müssen Sie die Variante mit dem Join verwenden.
USE AdventureWorks2022;
GO
SELECT p.[Name], s.[Name]
FROM Production.Product p
INNER JOIN Production.ProductSubcategory s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
AND s.[Name] = 'Wheels';
GO
Hier ist das Resultset.
Name
LL Mountain Front Wheel Wheels
ML Mountain Front Wheel Wheels
HL Mountain Front Wheel Wheels
LL Road Front Wheel Wheels
ML Road Front Wheel Wheels
HL Road Front Wheel Wheels
Touring Front Wheel Wheels
LL Mountain Rear Wheel Wheels
ML Mountain Rear Wheel Wheels
HL Mountain Rear Wheel Wheels
LL Road Rear Wheel Wheels
ML Road Rear Wheel Wheels
HL Road Rear Wheel Wheels
Touring Rear Wheel Wheels
(14 row(s) affected)
Die folgende Abfrage sucht die Namen aller Hersteller, deren Bonität gut ist, bei denen Adventure Works Cycles mindestens 20 Artikel bestellt und deren durchschnittliche Vorlaufzeit bei Lieferungen 16 Tage beträgt.
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Purchasing.Vendor
WHERE CreditRating = 1
AND BusinessEntityID IN
(SELECT BusinessEntityID
FROM Purchasing.ProductVendor
WHERE MinOrderQty >= 20
AND AverageLeadTime < 16);
GO
Hier ist das Resultset.
Name
--------------------------------------------------
Compete Enterprises, Inc
International Trek Center
First National Sport Co.
Comfort Road Bicycles
Circuit Cycles
First Rate Bicycles
Jeff's Sporting Goods
Competition Bike Training Systems
Electronic Bike Repair & Supplies
Crowley Sport
Expert Bike Co
Team Athletic Co.
Compete, Inc.
(13 row(s) affected)
Die innere Abfrage wird ausgewertet und gibt die IDs der Hersteller zurück, die den Bedingungen der Unterabfrage entsprechen. Danach wird die äußere Abfrage ausgewertet. Sie können in der WHERE
-Klausel der inneren und äußeren Abfrage mehrere Bedingungen einschließen.
Mit einem Join kann dieselbe Abfrage folgendermaßen ausgedrückt werden:
USE AdventureWorks2022;
GO
SELECT DISTINCT [Name]
FROM Purchasing.Vendor v
INNER JOIN Purchasing.ProductVendor p
ON v.BusinessEntityID = p.BusinessEntityID
WHERE CreditRating = 1
AND MinOrderQty >= 20
AND AverageLeadTime < 16;
GO
Ein Join kann immer als Unterabfrage ausgedrückt werden. Demgegenüber kann eine Unterabfrage zwar häufig, jedoch nicht immer als Join ausgedrückt werden. Die Ursache hierfür liegt in der Symmetrie von Joins: Sie können die Tabellen A
bis B
in beliebiger Reihenfolge verknüpfen und erhalten immer dieselben Ergebnisse. Dies gilt nicht, wenn eine Unterabfrage verwendet wird.
Unterabfragen mit NOT IN
Auch Unterabfragen, die mit dem NOT IN
-Schlüsselwort eingeleitet werden, geben eine Liste aus null oder mehr Werten zurück.
Die folgende Abfrage sucht die Namen aller Produkte, die keine fertigen Fahrräder sind.
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID NOT IN
(SELECT ProductSubcategoryID
FROM Production.ProductSubcategory
WHERE [Name] = 'Mountain Bikes'
OR [Name] = 'Road Bikes'
OR [Name] = 'Touring Bikes');
GO
Diese Anweisung kann nicht in einen Join konvertiert werden. Der analoge Join mit Ungleich hat eine andere Bedeutung: Sie sucht die Namen von Produkten, die sich in einer Unterkategorie befinden, die nicht fertige Fahrräder sind.
Unterabfragen in den Anweisungen UPDATE, DELETE und INSERT
Unterabfragen können in den Anweisungen UPDATE
, DELETE
, INSERT
und SELECT
der Datenbearbeitungssprache (Data Manipulation Language, DML) geschachtelt werden.
Das folgende Beispiel verdoppelt den Wert in der ListPrice
-Spalte in der Production.Product
-Tabelle. Die Unterabfrage in der WHERE
-Klausel verweist auf die Purchasing.ProductVendor
-Tabelle, um die in der Product-Tabelle aktualisierten Zeilen auf die zu beschränken, die von BusinessEntity
1540
angegeben wurden.
USE AdventureWorks2022;
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 2
WHERE ProductID IN
(SELECT ProductID
FROM Purchasing.ProductVendor
WHERE BusinessEntityID = 1540);
GO
Nachfolgend ist eine gleichwertige UPDATE
-Anweisung aufgeführt, die einen Join verwendet:
USE AdventureWorks2022;
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 2
FROM Production.Product AS p
INNER JOIN Purchasing.ProductVendor AS pv
ON p.ProductID = pv.ProductID AND BusinessEntityID = 1540;
GO
Verwenden Sie aus Gründen der Übersichtlichkeit den Alias der Zieltabelle, falls in anderen Unterabfragen auf dieselbe Tabelle verwiesen wird:
USE AdventureWorks2022;
GO
UPDATE p
SET ListPrice = ListPrice * 2
FROM Production.Product AS p
INNER JOIN Purchasing.ProductVendor AS pv
ON p.ProductID = pv.ProductID AND BusinessEntityID = 1540;
GO
Unterabfragen mit Vergleichsoperatoren
Unterabfragen können mit einem der folgenden Vergleichsoperatoren eingeleitet werden: =
, < >
, >
, > =
, <
, ! >
, ! <
oder < =
.
Eine Unterabfrage, die mit einem unveränderten Vergleichsoperator (dem nicht ANY
oder ALL
folgt) eingeleitet wird, darf keine Werteliste zurückgeben, wie Unterabfragen mit IN
, sondern muss einen einzelnen Wert zurückgeben. Wenn eine solche Unterabfrage mehrere Werte zurückgibt, wird von SQL Server eine Fehlermeldung angezeigt.
Sie sollten mit nicht geänderten Vergleichsoperatoren eingeleitete Unterabfragen nur verwenden, wenn Sie bei den Daten und dem vorliegenden Problem sicher sein können, dass die Unterabfrage genau einen Wert zurückgibt.
Unter der Annahme, dass jeder Vertriebsmitarbeiter nur für eine Vertriebsregion zuständig ist, möchten Sie beispielsweise die Namen aller Kunden finden, die in der Region ansässig sind, die Linda Mitchell
betreut. Hierzu können Sie eine Anweisung mit einer Unterabfrage schreiben, die mit dem einfachen Vergleichsoperator =
eingeleitet wird.
USE AdventureWorks2022;
GO
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID =
(SELECT TerritoryID
FROM Sales.SalesPerson
WHERE BusinessEntityID = 276);
GO
Wenn Linda Mitchell
jedoch mehrere Vertriebsregionen betreut, wird eine Fehlermeldung ausgegeben. Statt des Vergleichsoperators =
kann die Abfrage mit IN
formuliert werden (=ANY
ist ebenfalls möglich).
Mit nicht geänderten Vergleichsoperatoren eingeleitete Unterabfragen schließen häufig Aggregatfunktionen ein, da diese einen einzelnen Wert zurückgeben. Die folgende Anweisung ermittelt z. B. die Namen aller Produkte, deren Listenpreis höher als der durchschnittliche Listenpreis ist.
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ListPrice >
(SELECT AVG (ListPrice)
FROM Production.Product);
GO
Da mit einem nicht geänderten Vergleichsoperator eingeleitete Unterabfragen einen einzelnen Wert zurückgeben müssen, dürfen sie GROUP BY
- oder HAVING
-Klauseln nur dann einschließen, wenn sichergestellt ist, dass die GROUP BY
- oder HAVING
-Klausel selbst nur einen einzelnen Wert zurückgibt. Die folgende Abfrage findet z. B. die Produkte, deren Preis über dem des Produkts mit dem niedrigsten Preis in der ProductSubcategoryID
14
liegt.
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ListPrice >
(SELECT MIN (ListPrice)
FROM Production.Product
GROUP BY ProductSubcategoryID
HAVING ProductSubcategoryID = 14);
GO
SOME
Vergleichsoperatoren, die mit ANY
, ALL
, oder geändert werden
Vergleichsoperatoren, die eine Unterabfrage einleiten, können mit dem Schlüsselwort ALL
oder ANY
geändert werden. SOME
ist eine ISO-Standard-Entsprechung für ANY
. Weitere Informationen zu diesen Vergleichsoperatoren finden Sie unter SOME | ANY.
Mit einem geänderten Vergleichsoperator eingeleitete Unterabfragen geben eine Liste aus 0 oder mehr Werten zurück und können eine GROUP BY
- oder HAVING
-Klausel einschließen. Diese Unterabfragen können auch mit EXISTS
ausgedrückt werden.
Verwenden wir als Beispiel den Vergleichsoperator >, bedeutet > ALL
„größer als jeder Wert“. Mit anderen Worten: "größer als der Maximalwert". > ALL (1, 2, 3)
bedeutet beispielsweise „größer als 3“. > ANY
bedeutet „größer als mindestens ein Wert“, d.h. „größer als das Minimum“. > ANY (1, 2, 3)
bedeutet demnach „größer als 1“.
Eine Zeile in einer Unterabfrage mit > ALL
muss die in der äußeren Abfrage angegebene Bedingung nur erfüllen, wenn der Wert in der Spalte, die die Unterabfrage einleitet, größer als jeder Wert aus der Werteliste ist, die von der Unterabfrage zurückgegeben wird.
Dementsprechend bedeutet > ANY
, dass eine Zeile die Bedingung in der äußeren Abfrage nur erfüllt, wenn der Wert in der Spalte, die die Unterabfrage einleitet, größer als mindestens einer der Werte in der Werteliste ist, die von der Unterabfrage zurückgegeben wird.
Die folgende Abfrage stellt ein Beispiel für eine Unterabfrage dar, die mit einem durch ANY
geänderten Vergleichsoperator eingeleitet wird. Sie findet die Produkte, deren Listenpreise größer oder gleich dem maximalen Listenpreis aller Produkt-Unterkategorien sind.
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ListPrice >= ANY
(SELECT MAX (ListPrice)
FROM Production.Product
GROUP BY ProductSubcategoryID);
GO
Für jede Produkt-Unterkategorie findet die innere Abfrage den maximalen Listenpreis. Die äußere Abfrage betrachtet all diese Werte und ermittelt, welche Listenpreise einzelner Produkte größer oder gleich dem maximalen Listenpreis in allen Produkt-Unterkategorien sind. Wenn ANY
zu ALL
geändert wird, gibt die Abfrage nur solche Produkte zurück, deren Listenpreis größer oder gleich allen Listenpreisen ist, die in der inneren Abfrage zurückgegeben wurden.
Wenn die Unterabfrage keine Werte zurückgibt, gibt auch die Gesamtabfrage keine Werte zurück.
Der = ANY
-Operator entspricht IN
. Um beispielsweise die Namen aller Wheel-Produkte zu ermitteln, die von Adventure Works Cycles hergestellt werden, können Sie entweder IN
oder = ANY
verwenden.
--Using = ANY
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID = ANY
(SELECT ProductSubcategoryID
FROM Production.ProductSubcategory
WHERE Name = 'Wheels');
GO
--Using IN
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN
(SELECT ProductSubcategoryID
FROM Production.ProductSubcategory
WHERE Name = 'Wheels');
GO
Im Folgenden wird das Resultset der beiden Abfragen aufgeführt.
Name
--------------------------------------------------
LL Mountain Front Wheel
ML Mountain Front Wheel
HL Mountain Front Wheel
LL Road Front Wheel
ML Road Front Wheel
HL Road Front Wheel
Touring Front Wheel
LL Mountain Rear Wheel
ML Mountain Rear Wheel
HL Mountain Rear Wheel
LL Road Rear Wheel
ML Road Rear Wheel
HL Road Rear Wheel
Touring Rear Wheel
(14 row(s) affected)
Der <> ANY
-Operator unterscheidet sich jedoch von NOT IN
:
<> ANY
bedeutet „ungleich a oder ungleich b oder ungleich c“.NOT IN
bedeutet „ungleich a und ungleich b und ungleich c“.<> ALL
hat die gleiche Bedeutung wieNOT IN
.
Die folgende Abfrage findet z. B. die Kunden, die sich in einem Gebiet befinden, das nicht von Vertriebsmitarbeitern abgedeckt ist.
USE AdventureWorks2022;
GO
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID <> ANY
(SELECT TerritoryID
FROM Sales.SalesPerson);
GO
Die Ergebnisse schließen alle Kunden ein, mit Ausnahme der Kunden, deren Vertriebsregionen NULL sind, da jede Region, die einem Kunden zugeordnet ist, von einem Vertriebsmitarbeiter betreut wird. Die innere Abfrage findet alle Vertriebsregionen, die von Vertriebsmitarbeitern betreut werden. Dann findet die äußere Abfrage für jede Region die Kunden, die sich nicht in einer dieser Regionen befinden.
Aus demselben Grund enthalten die Ergebnisse keinen der Kunden, wenn Sie in dieser Abfrage NOT IN
verwenden.
Sie erhalten dieselben Ergebnisse mit dem Operator <> ALL
, der mit NOT IN
identisch ist.
Unterabfragen mit EXISTS
Unterabfragen, die mit dem EXISTS
-Schlüsselwort eingeleitet werden, dienen als Test auf das Vorhandensein bestimmter Daten. Die WHERE
-Klausel der äußeren Abfrage testet, ob die von der Unterabfrage zurückgegebenen Zeilen vorhanden sind. Die Unterabfrage gibt keine tatsächlichen Daten zurück, sondern lediglich den Wert TRUE
oder FALSE
.
Die Syntax einer mit EXISTS eingeleiteten Unterabfrage lautet wie folgt: WHERE [NOT] EXISTS (subquery)
Die folgende Abfrage sucht die Namen aller Produkte, die sich in der Wheels-Unterkategorie befinden:
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE EXISTS
(SELECT *
FROM Production.ProductSubcategory
WHERE ProductSubcategoryID =
Production.Product.ProductSubcategoryID
AND [Name] = 'Wheels');
GO
Hier ist das Resultset.
Name
--------------------------------------------------
LL Mountain Front Wheel
ML Mountain Front Wheel
HL Mountain Front Wheel
LL Road Front Wheel
ML Road Front Wheel
HL Road Front Wheel
Touring Front Wheel
LL Mountain Rear Wheel
ML Mountain Rear Wheel
HL Mountain Rear Wheel
LL Road Rear Wheel
ML Road Rear Wheel
HL Road Rear Wheel
Touring Rear Wheel
(14 row(s) affected)
Sehen Sie sich die Namen der einzelnen Produkte der Reihe nach an, um die Ergebnisse dieser Abfrage zu verstehen. Veranlasst dieser Wert die Unterabfrage zur Rückgabe mindestens einer Zeile? Bewirkt die Abfrage also, dass der Test auf Vorhandensein zu TRUE
ausgewertet wird?
EXISTS eingeleitete Unterabfragen unterscheiden sich in folgender Hinsicht geringfügig von anderen Unterabfragen:
- Vor dem
EXISTS
-Schlüsselwort befindet sich weder ein Spaltenname noch eine Konstante oder ein anderer Ausdruck. - Die Auswahlliste einer mit
EXISTS
eingeleiteten Unterabfrage besteht fast immer aus einem Sternchen (*). Spaltennamen müssen nicht aufgelistet werden, da lediglich getestet wird, ob Zeilen vorhanden sind, die die in der Unterabfrage angegebenen Bedingungen erfüllen.
Das EXISTS
-Schlüsselwort ist wichtig, da es häufig keine alternative Formulierung ohne Unterabfragen gibt. Manche mit EXISTS
erstellten Abfragen können nicht auf andere Weise ausgedrückt werden. Viele Abfragen können jedoch mithilfe von IN
oder einem durch ANY
oder ALL
geänderten Vergleichsoperator ähnliche Ergebnisse erzielen.
Die oben gezeigte Abfrage kann z. B. mithilfe von IN
ausgedrückt werden:
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN
(SELECT ProductSubcategoryID
FROM Production.ProductSubcategory
WHERE [Name] = 'Wheels');
GO
Unterabfragen mit NOT EXISTS
NOT EXISTS
funktioniert auf dieselbe Weise wie EXISTS
, mit der Ausnahme, dass die WHERE
-Klausel erfüllt wird, wenn von der Unterabfrage keine Zeilen zurückgegeben werden.
Um beispielsweise die Namen von Produkten zu finden, die sich nicht in der Unterkategorie Wheels befinden:
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE NOT EXISTS
(SELECT *
FROM Production.ProductSubcategory
WHERE ProductSubcategoryID =
Production.Product.ProductSubcategoryID
AND [Name] = 'Wheels');
GO
Anstelle von Ausdrücken verwendete Unterabfragen
In Transact-SQL kann eine Unterabfrage überall dort in SELECT
-, UPDATE
, INSERT
- und DELETE
-Anweisungen verwendet werden, wo auch ein Ausdruck verwendet werden kann. Eine Ausnahme stellen ORDER BY
-Listen dar.
Das folgende Beispiel veranschaulicht, wie Sie diese Erweiterung verwenden können. Diese Abfrage ermittelt die Preise aller Mountainbike-Produkte, ihren Durchschnittspreis sowie die Differenz zwischen dem Preis jedes einzelnen Mountainbikes und dem Durchschnittspreis.
USE AdventureWorks2022;
GO
SELECT [Name], ListPrice,
(SELECT AVG(ListPrice) FROM Production.Product) AS Average,
ListPrice - (SELECT AVG(ListPrice) FROM Production.Product)
AS Difference
FROM Production.Product
WHERE ProductSubcategoryID = 1;
GO
Zugehöriger Inhalt
- IN (Transact-SQL)
- EXISTS (Transact-SQL)
- ALL (Transact-SQL)
- SOME | ANY (Transact-SQL)
- Joins
- Vergleichsoperatoren (Transact-SQL)
- Handbuch zur Architektur der Abfrageverarbeitung
- Bewährte Methoden für den Abfragespeicher
- Intelligente Abfrageverarbeitung in SQL-Datenbanken
- Kardinalitätsschätzung (SQL Server)