Använda skalär eller flervärdesunderfrågor

Slutförd

En skalär underfråga är en inre SELECT-instruktion i en yttre fråga som skrivs för att returnera ett enda värde. Skalära underfrågor kan användas var som helst i en yttre T-SQL-instruktion där ett uttryck med en enda värde tillåts, till exempel i en SELECT-sats, en WHERE-sats, en HAVING-sats eller till och med en FROM-sats. De kan också användas i datamodifieringsinstruktioner, till exempel UPDATE eller DELETE.

Flervärdesunderfrågor kan, som namnet antyder, returnera mer än en rad. Men de returnerar fortfarande en enda kolumn.

Skalära underfrågor

Anta att du vill hämta information om den senaste beställningen som placerades, med antagandet att det är den som har det högsta SalesOrderID-värdet .

Om du vill hitta det högsta SalesOrderID-värdet kan du använda följande fråga:

SELECT MAX(SalesOrderID)
FROM Sales.SalesOrderHeader

Den här frågan returnerar ett enda värde som anger det högsta värdet för ett OrderID i tabellen SalesOrderHeader .

För att få information om den här ordern kan du behöva filtrera tabellen SalesOrderDetails baserat på vilket värde som returneras av frågan ovan. Du kan utföra den här uppgiften genom att kapsla frågan för att hämta maximalt SalesOrderID i WHERE-satsen för en fråga som hämtar orderinformationen.

SELECT SalesOrderID, ProductID, OrderQty
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 
   (SELECT MAX(SalesOrderID)
    FROM Sales.SalesOrderHeader);

Om du vill skriva en skalär underfråga bör du tänka på följande riktlinjer:

• Om du vill ange en fråga som en underfråga omger du den i parenteser.
• Flera nivåer av underfrågor stöds i Transact-SQL. I den här modulen tar vi bara hänsyn till tvånivåfrågor (en inre fråga i en yttre fråga), men upp till 32 nivåer stöds.
• Om underfrågan inte returnerar några rader (en tom uppsättning) är resultatet av underfrågan null. Om det är möjligt att inga rader returneras i scenariot bör du se till att din yttre fråga kan hantera en NULL korrekt, förutom andra förväntade resultat.
• Den inre frågan bör vanligtvis returnera en enda kolumn. Att välja flera kolumner i en underfråga är nästan alltid ett fel. Det enda undantaget är om underfrågan introduceras med nyckelordet EXISTS.

En skalär underfråga kan användas var som helst i en fråga där ett värde förväntas, inklusive SELECT-listan. Vi kan till exempel utöka frågan som hämtade information för den senaste ordningen så att den inkluderar den genomsnittliga kvantiteten artiklar som sorteras, så att vi kan jämföra antalet sorterade i den senaste ordningen med genomsnittet för alla beställningar.

SELECT SalesOrderID, ProductID, OrderQty,
    (SELECT AVG(OrderQty)
     FROM SalesLT.SalesOrderDetail) AS AvgQty
FROM SalesLT.SalesOrderDetail
WHERE SalesOrderID = 
    (SELECT MAX(SalesOrderID)
     FROM SalesLT.SalesOrderHeader);

Underfrågor med flera värden

En underfråga med flera värden passar bra för att returnera resultat med hjälp av IN-operatorn. Följande hypotetiska exempel returnerar CustomerID, SalesOrderID-värden för alla beställningar som görs av kunder i Kanada.

SELECT CustomerID, SalesOrderID
FROM Sales.SalesOrderHeader
WHERE CustomerID IN (
    SELECT CustomerID
    FROM Sales.Customer
    WHERE CountryRegion = 'Canada');

Om du i det här exemplet bara skulle köra den inre frågan returneras en kolumn med CustomerID-värden med en rad för varje kund i Kanada.

I många fall kan underfrågor med flera värden enkelt skrivas med hjälp av kopplingar. Här är till exempel en fråga som använder en koppling för att returnera samma resultat som i föregående exempel:

SELECT c.CustomerID, o.SalesOrderID
FROM Sales.Customer AS c
JOIN Sales.SalesOrderHeader AS o
    ON c.CustomerID = o.CustomerID
WHERE c.CountryRegion = 'Canada';

Så hur bestämmer du om du vill skriva en fråga som involverar flera tabeller som en JOIN eller med en underfråga? Ibland beror det bara på vad du är mer bekväm med. De flesta kapslade frågor som enkelt konverteras till JOIN konverteras faktiskt till en JOIN internt. För sådana frågor finns det då ingen verklig skillnad i att skriva frågan på ett sätt jämfört med ett annat.

En begränsning som du bör tänka på är att när du använder en kapslad fråga kan resultaten som returneras till klienten endast inkludera kolumner från den yttre frågan. Så om du behöver returnera kolumner från båda tabellerna bör du skriva frågan med hjälp av en JOIN.

Slutligen finns det situationer där den inre frågan behöver utföra mycket mer komplicerade åtgärder än de enkla hämtningarna i våra exempel. Det kan vara svårt att skriva om komplexa underfrågor med hjälp av en JOIN. Många SQL-utvecklare tycker att underfrågor fungerar bäst för komplicerad bearbetning eftersom du kan dela upp bearbetningen i mindre steg.