Delen via

Subquery's (SQL Server)

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-database in Microsoft Fabric Preview

Een subquery is een query die is genest in een SELECT, INSERT, UPDATE of DELETE instructie, of in een andere subquery.

De codevoorbeelden in dit artikel gebruiken de AdventureWorks2022 of AdventureWorksDW2022 voorbeelddatabase die u kunt downloaden van de startpagina van Microsoft SQL Server Samples en Community Projects .

Een subquery kan overal worden gebruikt waar een expressie is toegestaan. In dit voorbeeld wordt een subquery als kolomexpressie met de naam MaxUnitPrice in een SELECT instructie gebruikt.

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

Grondbeginselen van subquery

Een subquery wordt ook wel een interne query of interne selectie genoemd, terwijl de instructie met een subquery ook wel een externe query of externe selectie wordt genoemd.

Veel Transact-SQL-instructies die subquery's bevatten, kunnen ook worden geformuleerd als joins. Andere vragen kunnen alleen worden gesteld met subquery's. In Transact-SQL is er meestal geen prestatieverschil tussen een instructie die een subquery en een semantisch equivalente versie bevat die dat niet doet. Zie sql-instructieverwerking voor architectuurinformatie over hoe SQL Server query's verwerkt. In sommige gevallen waarin het bestaan moet worden gecontroleerd, levert een join echter betere prestaties op. Anders moet de geneste query worden verwerkt voor elk resultaat van de buitenste query om te zorgen voor het verwijderen van duplicaten. In dergelijke gevallen zou een joinbenadering betere resultaten opleveren.

In het volgende voorbeeld ziet u zowel een subquery SELECT als een join SELECT die dezelfde resultatenset en hetzelfde uitvoeringsplan retourneert:

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

Een subquery die in de buitenste SELECT instructie is genest, heeft de volgende onderdelen:

  • Een reguliere SELECT query met de normale selectielijstonderdelen.
  • Een reguliere FROM component met een of meer tabel- of weergavenamen.
  • Een optionele WHERE clausule.
  • Een optionele GROUP BY clausule.
  • Een optionele HAVING clausule.

De SELECT query van een subquery staat altijd tussen haakjes. Het kan geen of FOR BROWSE component bevatten COMPUTE en kan alleen een ORDER BY component bevatten wanneer ook een TOP component wordt opgegeven.

Een subquery kan worden genest binnen de WHERE of HAVING clausule van een buitenste SELECT, INSERT, UPDATE of instructie DELETE of in een andere subquery. Er zijn maximaal 32 nestniveaus mogelijk, hoewel de limiet varieert op basis van het beschikbare geheugen en de complexiteit van andere expressies in de query. Afzonderlijke query's bieden geen ondersteuning voor het nesten van maximaal 32 niveaus. Een subquery kan overal worden weergegeven waar een expressie kan worden gebruikt, als deze één waarde retourneert.

Als een tabel alleen wordt weergegeven in een subquery en niet in de buitenste query, kunnen kolommen uit die tabel niet worden opgenomen in de uitvoer (de selectielijst van de buitenste query).

Instructies met een subquery hebben meestal een van deze indelingen:

  • WHERE expression [NOT] IN (subquery)
  • WHERE expression comparison_operator [ANY | ALL] (subquery)
  • WHERE [NOT] EXISTS (subquery)

In sommige Transact-SQL instructies kan de subquery worden geëvalueerd alsof deze een onafhankelijke query is. Conceptueel gezien worden de resultaten van de subquery vervangen door de buitenste query (hoewel dit niet noodzakelijkerwijs is hoe SQL Server daadwerkelijk Transact-SQL instructies met subquery's verwerkt).

Er zijn drie basistypen subquery's. Degenen die:

  • Werken op lijsten die worden geïntroduceerd met IN of lijsten die door een vergelijkingsoperator zijn gewijzigd met ANY of ALL.
  • Worden geïntroduceerd met een ongewijzigde vergelijkingsoperator en moeten één waarde retourneren.
  • Zijn bestaanstests geïntroduceerd met EXISTS.

Subqueryregels

Een subquery is onderworpen aan de volgende beperkingen:

  • De selectielijst van een subquery die is geïntroduceerd met een vergelijkingsoperator kan slechts één expressie of kolomnaam bevatten (behalve die EXISTS en IN werken op SELECT * of een lijst).
  • Als de WHERE component van een buitenste query een kolomnaam bevat, moet deze compatibel zijn met de kolom in de selectielijst voor subquery's.
  • De gegevenstypen ntext, tekst en afbeelding kunnen niet worden gebruikt in de selectielijst met subquery's.
  • Omdat ze één waarde moeten retourneren, kunnen subquery's die zijn geïntroduceerd door een ongewijzigde vergelijkingsoperator (één die niet wordt gevolgd door het trefwoord ANY of ALL) geen GROUP BY en HAVING clausules bevatten.
  • Het DISTINCT trefwoord kan niet worden gebruikt met subquery's die GROUP BY bevatten.
  • De COMPUTE en INTO componenten kunnen niet worden opgegeven.
  • ORDER BY kan alleen worden opgegeven wanneer TOP ook wordt opgegeven.
  • Een weergave die is gemaakt met behulp van een subquery, kan niet worden bijgewerkt.
  • De selectielijst van een subquery die is geïntroduceerd met EXISTS, volgens conventie, heeft een sterretje (*) in plaats van één kolomnaam. De regels voor een subquery die geïntroduceerd worden met EXISTS zijn hetzelfde als die voor een standaard selectie, omdat een subquery die geïntroduceerd is met EXISTS een bestaanstest uitvoert en WAAR of ONWAAR teruggeeft, in plaats van gegevens.

Kwalificeren van kolomnamen in subqueries

In het volgende voorbeeld wordt de BusinessEntityID kolom in de WHERE component van de buitenste query impliciet gekwalificeerd door de tabelnaam in de buitenste querycomponent FROM (Sales.Store). De verwijzing naar CustomerID in de selectielijst van de subquery wordt gekwalificeerd door de subquery FROM-clause, namelijk door de Sales.Customer-tabel.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Sales.Store
WHERE BusinessEntityID NOT IN
    (SELECT CustomerID
     FROM Sales.Customer
     WHERE TerritoryID = 5);
GO

De algemene regel is dat kolomnamen in een instructie impliciet worden gekwalificeerd door de tabel waarnaar in de FROM component op hetzelfde niveau wordt verwezen. Als er geen kolom bestaat in de tabel waarnaar wordt verwezen in de FROM component van een subquery, wordt deze impliciet gekwalificeerd door de tabel waarnaar wordt verwezen in de FROM component van de buitenste query.

De query ziet er als volgt uit met deze impliciete aannames die zijn opgegeven:

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

Het is nooit verkeerd om de tabelnaam expliciet op te geven en het is altijd mogelijk om impliciete veronderstellingen over tabelnamen met expliciete kwalificaties te overschrijven.

Important

Als een kolom wordt verwezen in een subquery die niet bestaat in de tabel waarnaar wordt verwezen door de component van de subquery FROM , maar bestaat in een tabel waarnaar wordt verwezen door de component van FROM de buitenste query, wordt de query zonder fouten uitgevoerd. SQL Server kwalificeert de kolom in de subquery impliciet met de tabelnaam in de buitenste query.

Meerdere niveaus van nesten

Een subquery kan zelf een of meer subquery's bevatten. Een willekeurig aantal subquery's kan worden genest in een instructie.

Met de volgende query worden de namen gevonden van werknemers die ook verkoopmedewerkers zijn.

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 is het resultatenoverzicht.

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)

De binnenste query geeft de ID's van de verkoopsmedewerkers terug. De query op het volgende hogere niveau wordt geëvalueerd met deze verkoopmedewerker-id's en retourneert de id-nummers van de contactpersonen van de werknemers. Ten slotte gebruikt de buitenste query de contact-id's om de namen van de werknemers te vinden.

U kunt deze query ook als een join uitdrukken.

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

Gecorreleerde subquery's

Veel query's kunnen worden geëvalueerd door de subquery eenmaal uit te voeren en de resulterende waarde of waarden te vervangen door de WHERE component van de buitenste query. In query's met een gecorreleerde subquery (ook wel een herhalende subquery genoemd) is de subquery afhankelijk van de buitenste query voor de waarden. Dit betekent dat de subquery herhaaldelijk wordt uitgevoerd, één keer voor elke rij die door de buitenste query kan worden geselecteerd.

Met deze query wordt één exemplaar opgehaald van de voor- en achternaam van elke werknemer waarvoor de bonus in de SalesPerson tabel 5000 is en waarvoor de werknemersidentificatienummers in de Employee tabel en SalesPerson tabellen overeenkomen.

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 is het resultatenoverzicht.

LastName FirstName BusinessEntityID
-------------------------- ---------- ------------
Ansman-Wolfe Pamela 280
Saraiva José 282

(2 row(s) affected)

De vorige subquery in deze instructie kan niet onafhankelijk van de buitenste query worden geëvalueerd. Er is een waarde voor Employee.BusinessEntityIDnodig, maar deze waarde verandert als SQL Server verschillende rijen in Employeeonderzoekt. Dat is precies hoe deze query wordt geëvalueerd: SQL Server beschouwt elke rij van de Employee tabel voor opname in de resultaten door de waarde in elke rij in de binnenste query te vervangen. Als SQL Server bijvoorbeeld eerst de rij voor Syed Abbas onderzoekt, neemt de variabele Employee.BusinessEntityID de waarde 285 aan, die door SQL Server in de binnenste query wordt toegepast. Deze twee queryvoorbeelden vertegenwoordigen een ontleding van de vorige steekproef met de gecorreleerde subquery.

USE AdventureWorks2022;
GO
SELECT Bonus
FROM Sales.SalesPerson
WHERE BusinessEntityID = 285;
GO

Het resultaat is 0,00 (Syed Abbas ontving geen bonus omdat ze geen verkoper zijn), dus de buitenste query evalueert tot:

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

Omdat dit onwaar is, wordt de rij voor Syed Abbas niet opgenomen in de resultaten van de vorige voorbeeldquery met de gecorreleerde subquery. Volg dezelfde procedure met betrekking tot de rij voor Pamela Ansman-Wolfe. U ziet dat deze rij is opgenomen in de resultaten, omdat WHERE 5000 IN (5000) dit resultaten bevat.

Gecorreleerde subquery's kunnen ook tabelwaardefuncties in de FROM component bevatten door te verwijzen naar kolommen uit een tabel in de buitenste query als argument van de functie met tabelwaarde. In dit geval wordt voor elke rij van de buitenste query de tabelwaardefunctie geëvalueerd op basis van de subquery.

Subquerytypen

Subquery's kunnen op veel plaatsen worden opgegeven:

Subqueries met tabel aliassen

Veel instructies waarin de subquery en de outer query verwijzen naar dezelfde tabel, kunnen worden vermeld als self-joins (een tabel samenvoegen met zichzelf). U kunt bijvoorbeeld adressen van werknemers uit een bepaalde staat vinden met behulp van een subquery:

USE AdventureWorks2022;
GO
SELECT StateProvinceID, AddressID
FROM Person.Address
WHERE AddressID IN
    (SELECT AddressID
     FROM Person.Address
     WHERE StateProvinceID = 39);
GO

Hier is het resultatenoverzicht.

StateProvinceID AddressID
----------- -----------
39 942
39 955
39 972
39 22660

(4 row(s) affected)

U kunt ook een self-join gebruiken:

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

Tabelaliassen e1 en e2 zijn vereist omdat de tabel die aan zichzelf wordt toegevoegd, in twee verschillende rollen wordt weergegeven. Aliassen kunnen ook worden gebruikt in geneste query's die verwijzen naar dezelfde tabel in een interne en externe query.

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

Met expliciete tabelaliassen wordt duidelijk dat een verwijzing naar Person.Address in de subquery niet hetzelfde betekent als de verwijzing in de buitenste query.

Subqueries met IN

Het resultaat van een subquery die is geïntroduceerd met IN (of met NOT IN) is een lijst met nul of meer waarden. Nadat de subquery resultaten retourneert, maakt de buitenste query hiervan gebruik. Met de volgende query worden de namen gevonden van alle wielproducten die Adventure Works Cycles maakt.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE [Name] = 'Wheels');
GO

Hier is het resultatenoverzicht.

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)

Deze verklaring wordt in twee stappen geëvalueerd. Eerst retourneert de binnenste query het identificatienummer van de subcategorie dat overeenkomt met de naam Wheel (17). Ten tweede wordt deze waarde in de buitenste query geplaatst, die de productnamen vindt die bij de subcategorie-identificatienummers in Production.Product horen.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN ('17');
GO

Een verschil in het gebruik van een join in plaats van een subquery voor deze en vergelijkbare problemen is dat u met de join kolommen uit meer dan één tabel in het resultaat kunt weergeven. Als u bijvoorbeeld de naam van de productsubcategorie in het resultaat wilt opnemen, moet u een joinversie gebruiken.

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 is het resultatenoverzicht.

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)

Met de volgende query wordt de naam gevonden van alle leveranciers waarvan de waardering goed is, van wie Adventure Works Cycles ten minste 20 items bestelt en waarvan de gemiddelde levertijd minder dan 16 dagen is.

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 is het resultatenoverzicht.

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)

De interne query wordt geëvalueerd, waarbij de ID-nummers van de leveranciers die aan de kwalificaties van de subquery voldoen, worden geproduceerd. De buitenste query wordt vervolgens geëvalueerd. U kunt meer dan één voorwaarde opnemen in de WHERE component van zowel de binnenste als de buitenste query.

Met behulp van een join wordt dezelfde query als volgt uitgedrukt:

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

Een join kan altijd worden uitgedrukt als een subquery. Een subquery kan vaak, maar niet altijd, worden uitgedrukt als een join. Dit komt doordat joins symmetrisch zijn: u kunt tabel AB in beide volgorde samenvoegen en hetzelfde antwoord krijgen. Hetzelfde geldt niet als een subquery wordt gebruikt.

Subqueries met NOT IN

Subquery's die worden geïntroduceerd met het trefwoord NOT IN , retourneren ook een lijst met nul of meer waarden. Met de volgende query worden de namen gevonden van de producten die geen voltooide fietsen zijn.

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

Deze uitdrukking kan niet worden geconverteerd naar een join. De analoge ongelijk-samenvoeging heeft een andere betekenis: Het zoekt de namen van producten in een subcategorie die geen afgewerkte fiets is.

Subquery's in de instructies UPDATE, DELETE en INSERT

Subquery's kunnen worden genest in de UPDATE, DELETE, INSERT en SELECT data manipulatie (DML) instructies.

In het volgende voorbeeld wordt de waarde in de ListPrice kolom in de Production.Product tabel verdubbeld. De subquery in de WHERE component verwijst naar de Purchasing.ProductVendor tabel om de rijen die in de tabel Product zijn bijgewerkt te beperken tot alleen de rijen die zijn opgegeven door BusinessEntity1540.

USE AdventureWorks2022;
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 2
WHERE ProductID IN
    (SELECT ProductID
     FROM Purchasing.ProductVendor
     WHERE BusinessEntityID = 1540);
GO

Hier volgt een equivalente UPDATE instructie met behulp van een join:

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

Gebruik de alias van de doeltabel voor meer duidelijkheid als in andere subquery's naar dezelfde tabel wordt verwezen:

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

Subquery's met vergelijkingsoperatoren

Subquery's kunnen worden geïntroduceerd met een van de vergelijkingsoperators (, , =, , < >, >, , of > =<). ! >! << =

Een subquery die is geïntroduceerd met een ongewijzigde vergelijkingsoperator (een vergelijkingsoperator die niet wordt gevolgd door ANY of ALL) moet één waarde retourneren in plaats van een lijst met waarden, zoals subquery's die met IN. Als een dergelijke subquery meer dan één waarde retourneert, wordt in SQL Server een foutbericht weergegeven.

Als u een subquery wilt gebruiken die is geïntroduceerd met een ongewijzigde vergelijkingsoperator, moet u voldoende bekend zijn met uw gegevens en met de aard van het probleem om te weten dat de subquery precies één waarde retourneert.

Als u bijvoorbeeld ervan uitgaat dat elke verkoper slechts één verkoopgebied omvat en u de klanten wilt vinden die zich in het grondgebied bevinden waarop het gebied valt Linda Mitchell, kunt u een instructie schrijven met een subquery die is geïntroduceerd met de eenvoudige = vergelijkingsoperator.

USE AdventureWorks2022;
GO
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID =
    (SELECT TerritoryID
     FROM Sales.SalesPerson
     WHERE BusinessEntityID = 276);
GO

Als Linda Mitchell echter meer dan één verkoopgebied zou dekken, zou er een foutmelding optreden. In plaats van de = vergelijkingsoperator kan een IN formulering worden gebruikt (=ANY werkt ook).

Subquery's die met ongewijzigde vergelijkingsoperatoren worden geïntroduceerd, bevatten vaak statistische functies, omdat deze één waarde retourneren. Met de volgende instructie worden bijvoorbeeld de namen gevonden van alle producten waarvan de catalogusprijs hoger is dan de gemiddelde catalogusprijs.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ListPrice >
    (SELECT AVG (ListPrice)
     FROM Production.Product);
GO

Omdat subquery's die zijn geïntroduceerd met ongewijzigde vergelijkingsoperatoren een enkele waarde moeten retourneren, kunnen ze geen GROUP BY- of HAVING-voorwaarden bevatten, tenzij u weet dat de GROUP BY- of HAVING-voorwaarde zelf een enkele waarde retourneert. Met de volgende query worden bijvoorbeeld de producten gevonden die hoger zijn geprijsd dan het laagste product dat zich in ProductSubcategoryID14bevindt.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ListPrice >
    (SELECT MIN (ListPrice)
     FROM Production.Product
     GROUP BY ProductSubcategoryID
     HAVING ProductSubcategoryID = 14);
GO

Vergelijkingsoperatoren gewijzigd door ANY, SOMEof ALL

Vergelijkingsoperatoren die een subquery introduceren, kunnen worden gewijzigd door de trefwoorden ALL of ANY. SOME is een ISO-standaardequivalent voor ANY. Zie SOME | ANY voor meer informatie over deze vergelijkingsoperatoren.

Subquery's die worden geïntroduceerd met een gewijzigde vergelijkingsoperator, retourneren een lijst met nul of meer waarden en kunnen een GROUP BY of HAVING component bevatten. Deze subqueries kunnen worden herformuleerd met EXISTS.

Als u de > vergelijkingsoperator als voorbeeld gebruikt, > ALL betekent dit groter dan elke waarde. Met andere woorden, het betekent groter dan de maximumwaarde. Betekent bijvoorbeeld > ALL (1, 2, 3) groter dan 3. > ANY betekent groter dan ten minste één waarde, dat wil zeggen, groter dan het minimum. Betekent dus > ANY (1, 2, 3) groter dan 1.

Voor een rij in een subquery die > ALL voldoet aan de voorwaarde die is opgegeven in de buitenste query, moet de waarde in de kolom met de subquery groter zijn dan elke waarde in de lijst met waarden die door de subquery worden geretourneerd.

Op dezelfde manier betekent > ANY dat voor een rij om aan de voorwaarde te voldoen die is opgegeven in de buitenste query, de waarde in de kolom die de subquery introduceert groter moet zijn dan minstens één van de waarden in de lijst die door de subquery wordt geretourneerd.

De volgende query bevat een voorbeeld van een subquery die is geïntroduceerd met een vergelijkingsoperator die is gewijzigd door ANY. Hiermee worden de producten gevonden waarvan de lijstprijzen groter zijn dan of gelijk zijn aan de maximumlijstprijs van een productsubcategorie.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ListPrice >= ANY
    (SELECT MAX (ListPrice)
     FROM Production.Product
     GROUP BY ProductSubcategoryID);
GO

Voor elke subcategorie Product vindt de binnenste query de maximale catalogusprijs. De buitenste query bekijkt al deze waarden en bepaalt welke prijzen voor de lijst van afzonderlijke producten groter zijn dan of gelijk zijn aan de maximumprijs van een productsubcategorie. Als ANY naar ALL wordt gewijzigd, retourneert de query alleen die producten waarvan de lijstprijs groter dan of gelijk is aan alle lijstprijzen die door de binnenste query worden geretourneerd.

Als de subquery geen waarden retourneert, kan de hele query geen waarden retourneren.

De = ANY operator is gelijk aan IN. Als u bijvoorbeeld de namen wilt vinden van alle wielproducten die Adventure Works Cycles maakt, kunt u deze IN gebruiken of = ANY.

--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

Dit is de resultatenset voor een van beide query's:

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)

De <> ANY operator verschilt echter van NOT IN:

  • <> ANY betekent niet = a, of niet = b, of niet = c
  • NOT IN betekent niet = a en niet = b en niet = c
  • <> ALL betekent hetzelfde als NOT IN

Met de volgende query worden bijvoorbeeld klanten gevonden die zich in een gebied bevinden dat niet wordt gedekt door verkoopmedewerkers.

USE AdventureWorks2022;
GO
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID <> ANY
    (SELECT TerritoryID
     FROM Sales.SalesPerson);
GO

De resultaten omvatten alle klanten, met uitzondering van klanten waarvan de verkoopregio's zijn NULL, omdat elk gebied dat aan een klant is toegewezen, wordt gedekt door een verkoper. Met de binnenste query worden alle verkoopregio's gevonden die worden gedekt door verkoopmedewerkers. Vervolgens vindt de buitenste query de klanten die zich niet in één gebied bevinden.

Om dezelfde reden worden geen van de klanten in de resultaten opgenomen wanneer u NOT IN in deze query gebruikt.

U kunt dezelfde resultaten krijgen met de <> ALL operator, die gelijk is aan NOT IN.

Subqueries met EXISTS

Wanneer een subquery wordt geïntroduceerd met het trefwoord EXISTS, fungeert de subquery als een bestaanstest. De WHERE clausule van de buitenste query test of de rijen die worden geretourneerd door de subquery bestaan. De subquery produceert geen gegevens; retourneert een waarde van TRUE of FALSE.

Een subquery die is geïntroduceerd, EXISTS heeft de volgende syntaxis: WHERE [NOT] EXISTS (subquery)

Met de volgende query worden de namen gevonden van alle producten die zich in de subcategorie Wheels bevinden:

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE EXISTS
    (SELECT *
     FROM Production.ProductSubcategory
     WHERE ProductSubcategoryID =
            Production.Product.ProductSubcategoryID
        AND [Name] = 'Wheels');
GO

Hier is het resultatenoverzicht.

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)

Als u de resultaten van deze query wilt begrijpen, moet u rekening houden met de naam van elk product. Zorgt deze waarde ervoor dat de subquery ten minste één rij retourneert? Met andere woorden, veroorzaakt de query dat de bestaanstest wordt geëvalueerd tot TRUE?

Subquery's die worden geïntroduceerd EXISTS , verschillen enigszins van andere subquery's op de volgende manieren:

  • Het trefwoord EXISTS wordt niet voorafgegaan door een kolomnaam, constante of andere expressie.
  • De selectielijst van een subquery die wordt geïntroduceerd door EXISTS bijna altijd bestaat uit een sterretje (*). Er is geen reden om kolomnamen weer te geven, omdat u alleen test of rijen die voldoen aan de voorwaarden die zijn opgegeven in de subquery bestaan.

Het EXISTS trefwoord is belangrijk omdat er vaak geen alternatieve formulering is zonder subquery's. Hoewel sommige query's die zijn gemaakt met EXISTS niet op een andere manier kunnen worden uitgedrukt, kunnen veel query's IN of een vergelijkingsoperator die is gewijzigd met ANY of ALL gebruiken om vergelijkbare resultaten te bereiken.

De voorgaande query kan bijvoorbeeld worden uitgedrukt met behulp van IN:

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE [Name] = 'Wheels');
GO

Subqueries met NOT EXISTS

NOT EXISTS werkt als EXISTS, behalve dat aan de WHERE component wordt voldaan als er geen rijen worden geretourneerd door de subquery.

Als u bijvoorbeeld de namen wilt vinden van producten die zich niet in de subcategorie van de wielen bevinden:

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE NOT EXISTS
    (SELECT *
     FROM Production.ProductSubcategory
     WHERE ProductSubcategoryID =
            Production.Product.ProductSubcategoryID
        AND [Name] = 'Wheels');
GO

Subquery's die worden gebruikt in plaats van een expressie

In Transact-SQL kan een subquery overal worden gebruikt waar een expressie kan worden gebruikt in SELECT, UPDATE, INSERT, en DELETE instructies, behalve in een ORDER BY lijst.

In het volgende voorbeeld ziet u hoe u deze uitbreiding kunt gebruiken. Deze query zoekt de prijzen van alle mountainbikeproducten, hun gemiddelde prijs en het verschil tussen de prijs van elke mountainbike en de gemiddelde prijs.

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

Verwante inhoud