Underfrågor (SQL Server)

Gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalysplattformssystem (PDW)SQL-databas i Förhandsversion av Microsoft Fabric

En underfråga är en fråga som är kapslad i en SELECT, INSERT, UPDATEeller DELETE -instruktion eller inuti en annan underfråga.

Kodexemplen i den här artikeln använder AdventureWorks2022- eller AdventureWorksDW2022-exempeldatabasen, som du kan ladda ned från startsidan Microsoft SQL Server Samples och Community Projects.

En underfråga kan användas var som helst där ett uttryck tillåts. I det här exemplet används en underfråga som ett kolumnuttryck med namnet MaxUnitPrice i en SELECT -instruktion.

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

Grunderna i underfrågor

En underfråga kallas även för en inre fråga eller inre markering, medan -instruktionen som innehåller en underfråga även kallas för en yttre fråga eller yttre markering.

Många Transact-SQL-instruktioner som innehåller underfrågor kan alternativt formuleras som kopplingar. Andra frågor kan endast ställas med underfrågor. I Transact-SQL finns det vanligtvis ingen prestandaskillnad mellan en -instruktion som innehåller en underfråga och en semantiskt likvärdig version som inte gör det. Arkitekturinformation om hur SQL Server bearbetar frågor finns i SQL-instruktionsbearbetning. Men i vissa fall där existensen måste kontrolleras ger en koppling bättre prestanda. Annars måste den kapslade frågan bearbetas för varje resultat av den yttre frågan för att säkerställa eliminering av dubbletter. I sådana fall skulle en kopplingsmetod ge bättre resultat.

I följande exempel visas både en underfråga SELECT och en koppling SELECT som returnerar samma resultatuppsättning och körningsplan:

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

En underfråga kapslad i den yttre SELECT instruktionen har följande komponenter:

• En vanlig SELECT förfrågan, inklusive de vanliga komponenterna i den valda listan.
• En vanlig FROM sats inklusive ett eller flera tabell- eller visningsnamn.
• En valfri WHERE-sats.
• En valfri GROUP BY-sats.
• En valfri HAVING-sats.

Frågan SELECT för en underfråga omges alltid av parenteser. Det kan inte innehålla en COMPUTE eller FOR BROWSE -sats och kan bara innehålla en ORDER BY -sats när en TOP sats också anges.

En underfråga kan kapslas inuti en WHERE- eller HAVING-sats i en yttre SELECT, INSERT, UPDATE eller DELETE-instruktion, eller inuti en annan underfråga. Det går att kapsla upp till 32 nivåer, även om gränsen varierar beroende på tillgängligt minne och komplexiteten i andra uttryck i frågan. Enskilda frågor stöder inte kapsling ned till 32 nivåer. En underfråga kan visas var som helst där ett uttryck kan användas, om det returnerar ett enda värde.

Om en tabell endast visas i en underfråga och inte i den yttre frågan kan kolumner från den tabellen inte tas med i utdata (den yttre frågans urvalslista).

Instruktioner som innehåller en underfråga har vanligtvis något av följande format:

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

I vissa Transact-SQL-instruktioner kan underfrågan utvärderas som om det vore en oberoende fråga. Konceptuellt ersätts underfrågans resultat i den yttre frågan (även om det inte nödvändigtvis är så SQL Server faktiskt bearbetar Transact-SQL-instruktioner med underfrågor).

Det finns tre grundläggande typer av underfrågor. De som:

• Arbeta med listor som introduceras med IN, eller de som en jämförelseoperator har ändrat av ANY eller ALL.
• Introduceras med en oförändrad jämförelseoperator och måste returnera ett enda värde.
• Introduceras det existenskontroller med EXISTS?

Regler för underfrågor

En underfråga omfattas av följande begränsningar:

• Den valda listan över en underfråga som introduceras med en jämförelseoperator kan bara innehålla ett uttryck eller kolumnnamn (förutom att EXISTS och IN fungerar på SELECT * eller en lista).
• Om satsen i WHERE en yttre fråga innehåller ett kolumnnamn måste den vara kopplingskompatibel med kolumnen i listan med underfrågor.
• Datatyperna ntext, text och bild kan inte användas i listan med underfrågor.
• Eftersom de måste returnera ett enda värde kan underfrågor som introduceras av en oförändrad jämförelseoperator (en som inte följs av nyckelordet ANY eller ALL) inte innehålla GROUP BY och HAVING -satser.
• Nyckelordet DISTINCT kan inte användas med underfrågor som innehåller GROUP BY.
• COMPUTE-satserna och INTO-satserna kan inte anges.
• ORDER BY kan endast anges när TOP anges.
• Det går inte att uppdatera en vy som skapats med hjälp av en underfråga.
• Den valda listan över en underfråga som introducerades med EXISTS, efter konvention, har en asterisk (*) i stället för ett enda kolumnnamn. Reglerna för en underfråga som introduceras med EXISTS är desamma som för en standardvalslista, eftersom en underfråga som introduceras med EXISTS skapar ett existenstest och returnerar TRUE eller FALSE, i stället för data.

Kvalificera kolumnnamn i underfrågor

I följande exempel, kvalificeras kolumnen BusinessEntityID i satsen WHERE av den yttre frågeställningen implicit med tabellnamnet i den yttre frågesatsen FROM (Sales.Store). Referensen till CustomerID i underfrågans urvalslista kvalificeras av underfrågans FROM sats, d.v.s. av Sales.Customer tabellen.

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

Den allmänna regeln är att kolumnnamn i ett uttryck implicit kvalificeras av tabellen som refereras till i FROM-satsen på samma nivå. Om det inte finns någon kolumn i tabellen som refereras till i -satsen i FROM en underfråga, kvalificeras den implicit av tabellen som refereras i satsen i FROM den yttre frågan.

Så här ser frågan ut med dessa implicita antaganden angivna:

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

Det är aldrig fel att uttryckligen ange tabellnamnet och det är alltid möjligt att åsidosätta implicita antaganden om tabellnamn med explicita kvalifikationer.

Flera kapslingsnivåer

En underfråga kan innehålla en eller flera underfrågor. Vilket antal som helst av underfrågor kan kapslas i en instruktion.

Följande fråga hittar namnen på anställda som också är säljare.

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

Här är resultatet.

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)

Den innersta frågan returnerar försäljningsperson-ID:t. Frågan på nästa högre nivå utvärderas med dessa försäljningsperson-ID:t och returnerar kontakt-ID-nummer för de anställda. Slutligen använder den yttre frågan kontakt-ID:t för att hitta namnen på de anställda.

Du kan också uttrycka det här frågeuttrycket som en sammanfogning:

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

Korrelerade underfrågor

Många frågor kan utvärderas genom att köra underfrågan en gång och ersätta det resulterande värdet eller värdena i WHERE satsen för den yttre frågan. I frågor som innehåller en korrelerad underfråga (kallas även för en upprepad underfråga) beror underfrågan på den yttre frågan för dess värden. Det innebär att underfrågan körs upprepade gånger, en gång för varje rad som kan väljas av den yttre frågan.

Den här frågan hämtar en instans av varje medarbetares för- och efternamn som bonusen SalesPerson i tabellen är 5 000 för och som de anställdas ID-nummer matchar i tabellerna Employee och SalesPerson för.

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

Här är resultatet.

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

(2 row(s) affected)

Den tidigare underfrågan i det här satsen kan inte utvärderas oberoende av den yttre frågan. Det behöver ett värde för Employee.BusinessEntityID, men det här värdet ändras när SQL Server undersöker olika rader i Employee. Det är precis så den här frågan utvärderas: SQL Server tar hänsyn till varje rad Employee i tabellen för inkludering i resultaten genom att ersätta värdet i varje rad i den inre frågan. Om SQL Server till exempel först undersöker raden för Syed Abbastar variabeln Employee.BusinessEntityID värdet 285, som SQL Server ersätter i den inre frågan. Dessa två frågeexempel representerar en nedbrytning av föregående exempel med den korrelerade underfrågan.

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

Resultatet är 0,00 (Syed Abbas fick ingen bonus eftersom de inte är säljare), så den yttre frågan utvärderas till:

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

Eftersom det här är falskt inkluderas inte raden för Syed Abbas i resultatet av den tidigare exempelfrågan med den korrelerade underfrågan. Gå igenom samma procedur med raden för Pamela Ansman-Wolfe. Du ser att den här raden ingår i resultatet, eftersom WHERE 5000 IN (5000) den innehåller resultat.

Korrelerade underfrågor kan också innehålla tabellvärdesfunktioner i FROM -satsen genom att referera till kolumner från en tabell i den yttre frågan som ett argument för funktionen table-valued. I det här fallet utvärderas funktionen table-valued enligt underfrågan för varje rad i den yttre frågan.

Typer av underfrågor

Underfrågor kan anges på många platser:

• Med alias. Mer information finns i Underfrågor med tabellalias.
• Med IN eller NOT IN. Mer information finns i Underfrågor med IN och underfrågor med NOT IN.
• I UPDATE, DELETE, och INSERT-instruktioner. Mer information finns i Underfrågor i UPDATE-, DELETE- och INSERT-instruktioner.
• Med jämförelseoperatorer. Mer information finns i Underfrågor med jämförelseoperatorer.
• Med ANY, SOMEeller ALL. Mer information finns i Jämförelseoperatorer som ändrats av ANY, SOME eller ALL.
• Med IS [NOT] DISTINCT FROM. Mer information finns i ÄR [INTE] DISTINKT FRÅN (Transact-SQL).
• Med EXISTS eller NOT EXISTS. Mer information finns i Underfrågor med EXISTS och Underfrågor med NOT EXISTS.
• I stället för ett uttryck. Mer information finns i Underfrågor som används i stället för ett uttryck.

Underfrågor med tabellalias

Många satser där underfrågan och den yttre frågan refererar till samma tabell kan anges som självanslutningar (att koppla en tabell till sig själv). Du kan till exempel hitta adresser till anställda från ett visst tillstånd med hjälp av en underfråga:

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

Här är resultatet.

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

(4 row(s) affected)

Eller så kan du använda en självkoppling:

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

Tabellalias e1 och e2 krävs eftersom tabellen som är ansluten till sig själv visas i två olika roller. Alias kan också användas i kapslade frågor som refererar till samma tabell i en inre och yttre fråga.

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

Explicita tabellalias gör det tydligt att en referens till Person.Address i underfrågan inte betyder samma sak som referensen i den yttre frågan.

Underfrågor med IN

Resultatet av en underfråga som introduceras med IN (eller med NOT IN) är en lista med noll eller fler värden. När underfrågan returnerar resultat använder den yttre frågan dem. Följande fråga hittar namnen på alla hjulprodukter som Adventure Works Cycles gör.

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

Här är resultatet.

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)

Den här påståendet bedöms i två steg. Först returnerar den inre frågan det underkategoriidentifieringsnummer som matchar namnet Wheel (17). För det andra ersätts det här värdet i den yttre frågan, som hittar produktnamnen som följer med underkategoriidentifieringsnumren i Production.Product.

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

En skillnad i att använda en koppling i stället för en underfråga för detta och liknande problem är att kopplingen låter dig visa kolumner från mer än en tabell i resultatet. Om du till exempel vill ta med namnet på produktunderkategorin i resultatet måste du använda en kopplingsversion.

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

Här är resultatet.

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)

Följande fråga hittar namnet på alla leverantörer vars kreditvärdighet är bra, från vilka Adventure Works Cycles beställer minst 20 objekt och vars genomsnittliga ledtid att leverera är mindre än 16 dagar.

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

Här är resultatet.

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)

Den inre frågan utvärderas och skapar ID-nummer för de leverantörer som uppfyller underfrågornas kvalifikationer. Den yttre frågan utvärderas sedan. Du kan inkludera mer än ett villkor i WHERE satsen för både den inre och den yttre frågan.

Med hjälp av en koppling uttrycks samma fråga så här:

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

En koppling kan alltid uttryckas som en underfråga. En underfråga kan ofta, men inte alltid, uttryckas som en koppling. Det beror på att kopplingar är symmetriska: du kan koppla tabellen A till B i båda ordningarna och få samma svar. Detsamma gäller inte om en underfråga är inblandad.

Underfrågor med NOT IN

Underfrågor som introduceras med nyckelordet NOT IN returnerar också en lista med noll eller fler värden. Följande fråga identifierar namnen på de produkter som inte är färdiga cyklar.

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

Det går inte att konvertera det här påståendet till en sammanfogning. Den analoga inte lika kopplingen har en annan betydelse: Den hittar namnen på produkter som finns i vissa underkategorier som inte är en färdig cykel.

Underfrågor i update-, DELETE- och INSERT-instruktioner

Underfrågor kan kapslas in i UPDATE, DELETE, INSERT och SELECT DML-uttalanden för datamanipulering.

I följande exempel fördubblas värdet i ListPrice kolumnen i Production.Product tabellen. Underfrågan i WHERE -satsen refererar Purchasing.ProductVendor till tabellen för att begränsa raderna som uppdateras i tabellen Produkt till bara de som tillhandahålls av BusinessEntity1540.

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

Här är en motsvarande UPDATE instruktion med hjälp av en koppling:

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

För tydlighetens skull om samma tabell refereras i andra underfrågor använder du måltabellens alias:

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

Underfrågor med jämförelseoperatorer

Underfrågor kan introduceras med en av jämförelseoperatorerna (=, , < >>, > =, <, ! >, ! <eller < =).

En underfråga som introduceras med en oförändrad jämförelseoperator (en jämförelseoperator som inte följs av ANY eller ALL) måste returnera ett enda värde i stället för en lista med värden, till exempel underfrågor som introduceras med IN. Om en sådan underfråga returnerar mer än ett värde visas ett felmeddelande i SQL Server.

Om du vill använda en underfråga som introduceras med en oförändrad jämförelseoperator måste du vara tillräckligt bekant med dina data och med problemets art för att veta att underfrågan returnerar exakt ett värde.

Om du till exempel antar att varje säljare bara täcker ett försäljningsområde och du vill hitta de kunder som finns i det område som omfattas av Linda Mitchellkan du skriva en instruktion med en underfråga som introduceras med den enkla = jämförelseoperatorn.

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

Linda Mitchell Men om mer än ett försäljningsområde omfattas skulle ett felmeddelande visas. I stället = för jämförelseoperatorn kan en IN formulering användas (=ANY fungerar också).

Underfrågor som introduceras med oförändrade jämförelseoperatorer innehåller ofta aggregerade funktioner, eftersom dessa returnerar ett enda värde. Följande instruktion hittar till exempel namnen på alla produkter vars listpris är större än det genomsnittliga listpriset.

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

Eftersom underfrågor som introduceras med oförändrade jämförelseoperatorer måste returnera ett enda värde kan de inte inkludera GROUP BY eller HAVING satser såvida du inte vet att GROUP BY eller HAVING satsen själv returnerar ett enda värde. Följande fråga hittar till exempel produkter som är dyrare än den billigaste produkten som finns i ProductSubcategoryID14.

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

Jämförelseoperatorer som ändrats av ANY, SOME eller ALL

Jämförelseoperatorer som introducerar en underfråga kan ändras av nyckelorden ALL eller ANY. SOME är en ISO-standardekvivalent för ANY. Mer information om dessa jämförelseoperatorer finns i SOME | ALLA.

Underfrågor som introduceras med en modifierad jämförelseoperator returnerar en lista med noll eller fler värden och kan innehålla en GROUP BY eller HAVING-sats. Dessa underfrågor kan omräknas med EXISTS.

Att använda > jämförelseoperatorn som ett exempel innebär att > ALL är större än varje värde. Med andra ord betyder det större än det maximala värdet. Till exempel > ALL (1, 2, 3) innebär större än 3. > ANY innebär större än minst ett värde, det vill säga större än minimivärdet. Så > ANY (1, 2, 3) betyder större än 1.

För en rad i en underfråga med > ALL för att uppfylla villkoret som anges i den yttre frågan måste värdet i kolumnen som introducerar underfrågan vara större än varje värde i listan över värden som returneras av underfrågan.

> ANY På samma sätt innebär det att för att en rad ska uppfylla villkoret som anges i den yttre frågan måste värdet i kolumnen som introducerar underfrågan vara större än minst ett av värdena i listan över värden som returneras av underfrågan.

Följande fråga innehåller ett exempel på en underfråga som introduceras med en jämförelseoperator som ändrats av ANY. Den hittar de produkter vars listpriser är större än eller lika med det högsta listpriset för alla produktunderkategorier.

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

För varje produktunderkategori hittar den inre frågan det maximala listpriset. Den yttre frågan tittar på alla dessa värden och avgör vilka enskilda produkters listpriser som är större än eller lika med någon produkts högsta listpris. Om ANY ändras till ALLreturnerar frågan endast de produkter vars listpris är större än eller lika med alla listpriser som returneras i den inre frågan.

Om underfrågan inte returnerar några värden returnerar inte hela frågan några värden.

Operatorn = ANY motsvarar IN. Om du till exempel vill hitta namnen på alla hjulprodukter som Adventure Works Cycles tillverkar kan du använda antingen IN eller = 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

Här är resultatuppsättningen för någon av frågorna:

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)

Operatorn <> ANY skiljer sig dock från NOT IN:

• <> ANY betyder inte = a, eller inte = b, eller inte = c
• NOT IN betyder inte = a, och inte = b, och inte = c
• <> ALL innebär samma sak som NOT IN

Följande fråga hittar till exempel kunder som finns i ett område som inte omfattas av några säljare.

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

Resultatet inkluderar alla kunder, förutom de vars försäljningsterritorier är NULL, eftersom varje område som tilldelas en kund omfattas av en säljare. Den inre frågan hittar alla försäljningsterritorier som omfattas av säljare och för varje område hittar den yttre frågan sedan de kunder som inte finns i ett.

Av samma anledning inkluderar resultaten ingen av kunderna när du använder NOT IN i den här frågan.

Du kan få samma resultat med operatorn <> ALL , vilket motsvarar NOT IN.

Underfrågor med EXISTS

När en underfråga introduceras med nyckelordet EXISTSfungerar underfrågan som ett existenstest. WHERE Satsen i den yttre frågan testar om de rader som returneras av underfrågan finns. Underfrågan producerar faktiskt inga data. den returnerar värdet TRUE eller FALSE.

En underfråga som introduceras med EXISTS har följande syntax: WHERE [NOT] EXISTS (subquery)

Följande fråga hittar namnen på alla produkter som finns i underkategorin Hjul:

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

Här är resultatet.

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)

Om du vill förstå resultatet av den här frågan bör du överväga namnet på varje produkt i tur och ordning. Gör det här värdet att underfrågan returnerar minst en rad? Med andra ord, gör frågan att existenstestet utvärderas till TRUE?

Underfrågor som introduceras med EXISTS skiljer sig lite från andra underfrågor på följande sätt:

• Nyckelordet EXISTS föregås inte av ett kolumnnamn, en konstant eller ett annat uttryck.
• Den valda listan över en underfråga som introduceras av EXISTS består nästan alltid av en asterisk (*). Det finns ingen anledning att lista kolumnnamn eftersom du bara testar om rader som uppfyller de villkor som anges i underfrågan finns.

Nyckelordet EXISTS är viktigt eftersom det ofta inte finns någon alternativ formulering utan underfrågor. Även om vissa frågor som skapas med EXISTS inte kan uttryckas på något annat sätt, kan många frågor använda IN eller en jämförelseoperator som ändrats av ANY eller ALL för att uppnå liknande resultat.

Till exempel kan föregående fråga uttryckas med hjälp INav :

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

Underfrågor med NOT EXISTS

NOT EXISTS fungerar som EXISTS, förutom att WHERE -satsen uppfylls om inga rader returneras av underfrågan.

Om du till exempel vill hitta namnen på produkter som inte finns i hjulunderkategorin:

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

Underfrågor som används i stället för ett uttryck

I Transact-SQL kan en underfråga ersättas var som helst där ett uttryck kan användas i SELECT, UPDATE, INSERToch DELETE -instruktioner, förutom i en ORDER BY lista.

I följande exempel visas hur du kan använda den här förbättringen. Den här frågan hittar priserna på alla mountainbikeprodukter, deras genomsnittliga pris och skillnaden mellan priset för varje mountainbike och det genomsnittliga priset.

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

Relaterat innehåll

• IN (Transact-SQL)
• EXISTS (Transact-SQL)
• ALLA (Transact-SQL)
• NÅGRA | ANY (Transact-SQL)
• Kopplingar (SQL Server)
• Jämförelseoperatorer (Transact-SQL)
• Arkitekturguide för frågebearbetning
• Metodtips för övervakning av arbetsbelastningar med Query Store
• Intelligent frågebearbetning i SQL-databaser
• Kardinalitetsuppskattning (SQL Server)