Mit ANY, SOME oder ALL modifizierte Vergleichsoperatoren
Vergleichsoperatoren, die eine Unterabfrage einleiten, können mit den Schlüsselwörtern ALL oder ANY geändert werden. SOME ist eine ISO-Standard-Entsprechung für ANY.
Mit einem geänderten Vergleichsoperator eingeleitete Unterabfragen geben eine Liste aus null 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 >. Der Vergleichsoperator >ALL bedeutet "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", das heißt, "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 AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ListPrice >= ANY
(SELECT MAX (ListPrice)
FROM Production.Product
GROUP BY ProductSubcategoryID)
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 AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID =ANY
(SELECT ProductSubcategoryID
FROM Production.ProductSubcategory
WHERE Name = 'Wheels')
--Using IN
USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID IN
(SELECT ProductSubcategoryID
FROM Production.ProductSubcategory
WHERE Name = 'Wheels')
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 bedeutet dasselbe wie NOT IN.
Die folgende Abfrage findet z. B. die Kunden, die sich in einem Gebiet befinden, das nicht von Vertriebsmitarbeitern abgedeckt ist.
Use AdventureWorks;
GO
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID <> ANY
(SELECT TerritoryID
FROM Sales.SalesPerson)
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 äquivalent zu NOT IN ist.
Siehe auch