Unterabfragen (SQL Server)

  • Artikel

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 durch ANY oder ALL 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 und IN, die sich auf Anweisungen mit SELECT * 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 oder ALL folgt) eingeleitet werden, keine GROUP BY- und HAVING-Klauseln enthalten.
  • Das DISTINCT-Schlüsselwort darf nicht bei Unterabfragen verwendet werden, die GROUP BY einschließen.
  • Die COMPUTE- und INTO-Klauseln können nicht angegeben werden.
  • ORDER BY kann nur angegeben werden, wenn auch TOP 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 mit EXISTS eingeleitete Unterabfrage entsprechen den Regeln für eine standardmäßige Auswahlliste, da eine mit EXISTS 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 sehen Sie das Ergebnis.

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 sehen Sie das Ergebnis.

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:

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 sehen Sie das Ergebnis.

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 sehen Sie das Ergebnis.

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 sehen Sie das Ergebnis.

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 sehen Sie das Ergebnis.

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 wie NOT 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 sehen Sie das Ergebnis.

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