Scalaire of subquery's met meerdere waarden gebruiken

  • 3 minuten

Een scalaire subquery is een interne SELECT-instructie binnen een buitenste query die is geschreven om één waarde te retourneren. Scalaire subquery's kunnen overal in een buitenste T-SQL-instructie worden gebruikt waarbij een expressie met één waarde is toegestaan, zoals in een SELECT-component, een WHERE-component, een HAVING-component of zelfs een FROM-component. Ze kunnen ook worden gebruikt in instructies voor gegevenswijziging, zoals UPDATE of DELETE.

Subquery's met meerdere waarden kunnen, zoals de naam al doet vermoeden, meer dan één rij retourneren. Ze retourneren echter nog steeds één kolom.

Scalaire subquery's

Stel dat u de details wilt ophalen van de laatste bestelling die is geplaatst, op basis van de aanname dat deze de waarde is met de hoogste SalesOrderID-waarde .

Als u de hoogste waarde van SalesOrderID wilt vinden, gebruikt u mogelijk de volgende query:

SELECT MAX(SalesOrderID)
FROM Sales.SalesOrderHeader

Deze query retourneert één waarde die de hoogste waarde aangeeft voor een OrderID in de tabel SalesOrderHeader .

Als u de details voor deze order wilt ophalen, moet u mogelijk de tabel SalesOrderDetails filteren op basis van de waarde die wordt geretourneerd door de bovenstaande query. U kunt deze taak uitvoeren door de query te nesten om de maximale SalesOrderID op te halen in de WHERE-component van een query waarmee de ordergegevens worden opgehaald.

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

Als u een scalaire subquery wilt schrijven, moet u rekening houden met de volgende richtlijnen:

  • Als u een query als subquery wilt aanduffen, plaatst u deze tussen haakjes.
  • Meerdere niveaus van subquery's worden ondersteund in Transact-SQL. In deze module wordt alleen rekening gehouden met query's op twee niveaus (één interne query binnen één buitenste query), maar maximaal 32 niveaus worden ondersteund.
  • Als de subquery geen rijen (een lege set) retourneert, is het resultaat van de subquery een NULL. Als het in uw scenario mogelijk is dat er geen rijen worden geretourneerd, moet u ervoor zorgen dat uw buitenste query een NULL correct kan verwerken, naast andere verwachte resultaten.
  • De binnenste query moet over het algemeen één kolom retourneren. Het selecteren van meerdere kolommen in een subquery is bijna altijd een fout. De enige uitzondering is als de subquery wordt geïntroduceerd met het trefwoord EXISTS.

Een scalaire subquery kan overal in een query worden gebruikt waar een waarde wordt verwacht, inclusief de SELECT-lijst. We kunnen bijvoorbeeld de query uitbreiden die details voor de meest recente order heeft opgehaald, zodat de gemiddelde hoeveelheid artikelen die is besteld, worden opgenomen, zodat we de bestelde hoeveelheid in de meest recente order kunnen vergelijken met het gemiddelde voor alle orders.

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

Subquery's met meerdere waarden

Een subquery met meerdere waarden is geschikt voor het retourneren van resultaten met behulp van de IN-operator. Het volgende hypothetische voorbeeld retourneert de CustomerID, SalesOrderID-waarden voor alle orders die door klanten in Canada zijn geplaatst.

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

Als u in dit voorbeeld alleen de interne query zou uitvoeren, wordt er een kolom met CustomerID-waarden geretourneerd, met een rij voor elke klant in Canada.

In veel gevallen kunnen subquery's met meerdere waarden eenvoudig worden geschreven met behulp van joins. Hier volgt bijvoorbeeld een query die gebruikmaakt van een join om dezelfde resultaten te retourneren als in het vorige voorbeeld:

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

Hoe bepaalt u of u een query met meerdere tabellen wilt schrijven als JOIN of met een subquery? Soms is het alleen afhankelijk van wat u prettiger vindt. De meeste geneste query's die eenvoudig naar JOIN's worden geconverteerd, worden intern geconverteerd naar een JOIN. Voor dergelijke query's is er dan geen echt verschil in het schrijven van de query op één manier versus een andere.

Een beperking waarmee u rekening moet houden, is dat wanneer u een geneste query gebruikt, de resultaten die naar de client worden geretourneerd, alleen kolommen uit de buitenste query kunnen bevatten. Dus als u kolommen uit beide tabellen wilt retourneren, moet u de query schrijven met behulp van een JOIN.

Ten slotte zijn er situaties waarin de interne query veel gecompliceerdere bewerkingen moet uitvoeren dan de eenvoudige ophaalbewerkingen in onze voorbeelden. Het herschrijven van complexe subquery's met behulp van een JOIN kan lastig zijn. Veel SQL-ontwikkelaars vinden subquery's het meest geschikt voor complexe verwerking, omdat u hiermee de verwerking in kleinere stappen kunt opsplitsen.