Udostępnij za pośrednictwem

Podzapytania (SQL Server)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Baza danych SQL w usłudze Microsoft Fabric (wersja zapoznawcza)

Podzapytanie to zapytanie zagnieżdżone wewnątrz SELECTinstrukcji , INSERT, UPDATElub DELETE wewnątrz innego podzapytania.

Przykłady kodu w tym artykule korzystają z przykładowej bazy danych AdventureWorks2022 lub AdventureWorksDW2022, którą można pobrać ze strony głównej Przykładów programu Microsoft SQL Server i projektów społeczności.

Podzapytywanie może być używane w dowolnym miejscu, w jakim jest dozwolone wyrażenie. W tym przykładzie podzapytywanie jest używane jako wyrażenie kolumny o nazwie MaxUnitPrice w instrukcji SELECT .

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

Subquery fundamentals

Podzapytanie jest również nazywane zapytaniem wewnętrznym lub zaznaczeniem wewnętrznym, podczas gdy instrukcja zawierająca podzapytywanie jest również nazywana zapytaniem zewnętrznym lub zaznaczeniem zewnętrznym.

Wiele Transact-SQL instrukcji, które zawierają podzapytania, można alternatywnie sformułować jako sprzężenia. Inne pytania mogą być zadawane tylko w przypadku podzapytania. W języku Transact-SQL zwykle nie ma różnicy między wydajnością instrukcji, która zawiera podzapytanie i semantycznie równoważną wersję, która nie. Aby uzyskać informacje o architekturze dotyczące przetwarzania zapytań przez program SQL Server, zobacz Przetwarzanie instrukcji SQL. Jednak w niektórych przypadkach, w których należy sprawdzić istnienie, sprzężenia dają lepszą wydajność. W przeciwnym razie zapytanie zagnieżdżone musi być przetwarzane dla każdego wyniku zapytania zewnętrznego, aby zapewnić eliminację duplikatów. W takich przypadkach podejście sprzężenia przyniesie lepsze wyniki.

W poniższym przykładzie przedstawiono zarówno podzapytywanie, jak i sprzężenia SELECTSELECT , które zwracają ten sam zestaw wyników i plan wykonania:

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

Podzapytanie zagnieżdżone w zewnętrznej instrukcji SELECT ma następujące składniki:

  • Regularne SELECT zapytanie, w tym regularne składniki listy wyboru.
  • Klauzula regularna FROM zawierająca co najmniej jedną tabelę lub nazwy widoków.
  • Klauzula opcjonalna WHERE .
  • Klauzula opcjonalna GROUP BY .
  • Klauzula opcjonalna HAVING .

Zapytanie SELECT podzapytania jest zawsze ujęte w nawiasy. Nie może zawierać klauzuli COMPUTE lub FOR BROWSE i może zawierać klauzulę tylko wtedy, gdy określono również klauzulę ORDER BY TOP.

Podzapytanie może być zagnieżdżone wewnątrz WHERE klauzuli or HAVING zewnętrznej SELECT, INSERT, UPDATElub DELETE wewnątrz innego podzapytania. Maksymalnie 32 poziomy zagnieżdżania jest możliwe, chociaż limit różni się w zależności od dostępnej pamięci i złożoności innych wyrażeń w zapytaniu. Pojedyncze zapytania nie obsługują zagnieżdżania do 32 poziomów. Podzapytywanie może pojawić się w dowolnym miejscu, w jakim można użyć wyrażenia, jeśli zwraca pojedynczą wartość.

Jeśli tabela jest wyświetlana tylko w podzapytaniu, a nie w zapytaniu zewnętrznym, kolumny z tej tabeli nie mogą być uwzględnione w danych wyjściowych (lista wyboru zapytania zewnętrznego).

Instrukcje zawierające podzapytywanie zwykle przyjmują jeden z następujących formatów:

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

W niektórych instrukcjach Transact-SQL podzapytywanie można ocenić tak, jakby było to niezależne zapytanie. Koncepcyjnie wyniki podzapytania są zastępowane zapytaniem zewnętrznym (chociaż niekoniecznie tak naprawdę program SQL Server przetwarza instrukcje Transact-SQL z podzapytaniami).

Istnieją trzy podstawowe typy podzapytania. Those that:

  • Operuj na listach wprowadzonych za pomocą INelementu lub tych, które operator porównania zmodyfikował przez ANY lub ALL.
  • Są wprowadzane z niezmodyfikowanym operatorem porównania i muszą zwracać pojedynczą wartość.
  • Czy testy istnienia są wprowadzane za pomocą EXISTSpolecenia .

Subquery rules

Podzapytywanie podlega następującym ograniczeniom:

  • Lista wyboru podzapytania wprowadzona z operatorem porównania może zawierać tylko jedno wyrażenie lub nazwę kolumny (z tą różnicą, że EXISTS i IN działać odpowiednio na SELECT * lub na liście).
  • Jeśli klauzula WHERE zapytania zewnętrznego zawiera nazwę kolumny, musi być zgodna z kolumną na liście wyboru podzapytania.
  • Nie można używać typów danych ntext, text i image na liście wybranych podzapytania.
  • Ponieważ muszą zwrócić pojedynczą wartość, podzapytania wprowadzone przez niezmodyfikowany operator porównania (jeden, po którym nie następuje słowo kluczowe ANY lub ALL), nie może zawierać GROUP BY ani HAVING klauzul.
  • Słowa DISTINCT kluczowego nie można używać z podzapytaniami, które zawierają GROUP BYelement .
  • COMPUTE Nie można określić klauzul iINTO.
  • ORDER BY można określić tylko wtedy, gdy TOP jest również określony.
  • Nie można zaktualizować widoku utworzonego przy użyciu podzapytania.
  • Lista wyboru podzapytania wprowadzona z ciągiem , zgodnie z EXISTSkonwencją, ma gwiazdkę (*) zamiast pojedynczej nazwy kolumny. Reguły dla podzapytania wprowadzonego EXISTS przy użyciu są takie same jak reguły dla standardowej listy wyboru, ponieważ podzapytywanie wprowadzone za pomocą EXISTS tworzy test istnienia i zwraca wartość TRUE lub FALSE, a nie dane.

Kwalifikowanie nazw kolumn w podzapytaniu

W poniższym przykładzie kolumna BusinessEntityID w WHERE klauzuli zapytania zewnętrznego jest niejawnie kwalifikowana przez nazwę tabeli w zewnętrznej klauzuli zapytania FROM (Sales.Store). Odwołanie do CustomerID elementu na liście wyboru podzapytania jest kwalifikowane przez klauzulę Sales.Customer podzapytaniaFROM, czyli według tabeli.

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

Ogólna reguła polega na tym, że nazwy kolumn w instrukcji są niejawnie kwalifikowane przez tabelę przywołyną w FROM klauzuli na tym samym poziomie. Jeśli kolumna nie istnieje w tabeli, do których odwołuje się FROM klauzula podzapytania, jest niejawnie kwalifikowana przez tabelę przywołyną w FROM klauzuli zapytania zewnętrznego.

Poniżej przedstawiono wygląd zapytania z określonymi niejawnymi założeniami:

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

Nigdy nie można jawnie podać nazwy tabeli i zawsze można zastąpić niejawne założenia dotyczące nazw tabel z jawnymi kwalifikacjami.

Important

Jeśli kolumna jest przywołynięta w podzapytaniu, która nie istnieje w tabeli, do którego odwołuje się klauzula podzapytania FROM , ale istnieje w tabeli, do którego odwołuje się klauzula FROM zapytania zewnętrznego, zapytanie wykonuje bez błędu. Program SQL Server niejawnie kwalifikuje kolumnę w podzapytaniu przy użyciu nazwy tabeli w zapytaniu zewnętrznym.

Wiele poziomów zagnieżdżania

Podzapytywanie może zawierać co najmniej jedną podzapytanię. W instrukcji można zagnieżdżać dowolną liczbę podzapytań.

Poniższe zapytanie znajduje nazwiska pracowników, którzy są również osobami sprzedaży.

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

Oto zestaw wyników.

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)

Zapytanie najbardziej wewnętrzne zwraca identyfikatory osób sprzedaży. Zapytanie na następnym wyższym poziomie jest oceniane przy użyciu tych identyfikatorów osób sprzedaży i zwraca numery identyfikatorów kontaktów pracowników. Na koniec zapytanie zewnętrzne używa identyfikatorów kontaktów do znalezienia nazw pracowników.

To zapytanie można również wyrazić jako sprzężenia:

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

Correlated subqueries

Wiele zapytań można ocenić, wykonując podzapytywanie raz i podstawiając wynikowej wartości lub wartości w WHERE klauzuli zapytania zewnętrznego. W zapytaniach, które zawierają skorelowane podzapytanie (znane również jako powtarzające się podzapytanie), podzapytywanie zależy od zapytania zewnętrznego dla jego wartości. Oznacza to, że podzapytywanie jest wykonywane wielokrotnie, raz dla każdego wiersza, który może zostać wybrany przez zapytanie zewnętrzne.

To zapytanie pobiera jedno wystąpienie pierwszego i nazwiska każdego pracownika, dla którego bonus w SalesPerson tabeli wynosi 5000 i dla którego numery identyfikacyjne pracowników są zgodne z tabelami Employee i SalesPerson .

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

Oto zestaw wyników.

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

(2 row(s) affected)

Poprzednie podzapytywanie w tej instrukcji nie może być oceniane niezależnie od zapytania zewnętrznego. Potrzebuje wartości , Employee.BusinessEntityIDale ta wartość zmienia się w miarę badania różnych wierszy w programie SQL Server.Employee Dokładnie tak jest oceniane to zapytanie: program SQL Server uwzględnia każdy wiersz Employee tabeli w celu uwzględnienia w wynikach przez zastąpienie wartości w każdym wierszu do zapytania wewnętrznego. Jeśli na przykład program SQL Server najpierw zbada wiersz polecenia Syed Abbas, zmienna Employee.BusinessEntityID przyjmuje wartość 285, która program SQL Server zastępuje zapytanie wewnętrzne. Te dwa przykłady zapytań reprezentują dekompozycję poprzedniego przykładu z skorelowanym podzapytaniem.

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

Wynik wynosi 0,00 (Syed Abbas nie otrzymał premii, ponieważ nie jest sprzedawcą), więc zapytanie zewnętrzne daje następujące wyniki:

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

Ponieważ jest to fałsz, wiersz dla Syed Abbas elementu nie jest uwzględniony w wynikach poprzedniego przykładowego zapytania z skorelowanym podzapytaniem. Wykonaj tę samą procedurę z wierszem dla Pamela Ansman-Wolfeelementu . Zobaczysz, że ten wiersz jest uwzględniony w wynikach, ponieważ WHERE 5000 IN (5000) zawiera wyniki.

Skorelowane podzapytania mogą również zawierać funkcje wartości tabeli w FROM klauzuli, odwołując się do kolumn z tabeli w zapytaniu zewnętrznym jako argument funkcji wartości tabeli. W takim przypadku dla każdego wiersza zapytania zewnętrznego funkcja wartości tabeli jest obliczana zgodnie z podzapytaniem.

Subquery types

Podzapytania można określić w wielu miejscach:

Podzapytania z aliasami tabel

Wiele instrukcji, w których podzapytywanie i zapytanie zewnętrzne odnoszą się do tej samej tabeli, można określić jako sprzężenia własne (łączenie tabeli z samą sobą). Można na przykład znaleźć adresy pracowników z określonego stanu przy użyciu podzapytania:

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

Oto zestaw wyników.

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

(4 row(s) affected)

Możesz też użyć samosprzężenia:

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

Aliasy e1 tabel i e2 są wymagane, ponieważ tabela jest przyłączona do siebie w dwóch różnych rolach. Aliasy mogą być również używane w zapytaniach zagnieżdżonych odwołujących się do tej samej tabeli w zapytaniu wewnętrznym i zewnętrznym.

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

Jawne aliasy tabeli jasno wyjaśniają, że odwołanie do Person.Address w podzapytaniu nie oznacza tego samego, co odwołanie w zapytaniu zewnętrznym.

Podzapytania z funkcją IN

Wynik podzapytania wprowadzonego z IN (lub z NOT IN) jest listą wartości zero lub więcej. Gdy podzapytywanie zwróci wyniki, zapytanie zewnętrzne używa ich. Poniższe zapytanie znajduje nazwy wszystkich produktów wheel, które tworzy Adventure Works Cycles.

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

Oto zestaw wyników.

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)

Ta instrukcja jest oceniana w dwóch krokach. Najpierw zapytanie wewnętrzne zwraca numer identyfikacyjny podkategorii, który odpowiada nazwie Wheel (17). Po drugie, ta wartość jest zastępowana w zapytaniu zewnętrznym, które znajduje nazwy produktów, które idą za pomocą numerów identyfikacyjnych podkategorii w pliku Production.Product.

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

Jedną z różnic w używaniu sprzężenia, a nie podzapytania dla tego i podobnych problemów jest to, że sprzężenia umożliwiają wyświetlanie kolumn z więcej niż jednej tabeli w wyniku. Jeśli na przykład chcesz uwzględnić nazwę podkategorii produktu w wyniku, musisz użyć wersji sprzężenia.

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

Oto zestaw wyników.

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)

Poniższe zapytanie znajduje nazwę wszystkich dostawców, których ocena kredytowa jest dobra, z których Adventure Works Cycles zamawia co najmniej 20 elementów i których średni czas realizacji do dostarczenia wynosi mniej niż 16 dni.

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

Oto zestaw wyników.

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)

Zapytanie wewnętrzne jest oceniane, generując numery identyfikatorów dostawców, którzy spełniają kwalifikacje podzapytania. Zapytanie zewnętrzne jest następnie oceniane. W klauzuli WHERE wewnętrznego i zewnętrznego zapytania można uwzględnić więcej niż jeden warunek.

Używając sprzężenia, to samo zapytanie jest wyrażane w następujący sposób:

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

Sprzężenia można zawsze wyrazić jako podzapytywanie. Podzapytywanie może często, ale nie zawsze, być wyrażone jako sprzężenia. Dzieje się tak, ponieważ sprzężenia są symetryczne: tabelę można połączyć AB w jednej kolejności i uzyskać tę samą odpowiedź. To samo nie jest prawdziwe, jeśli jest zaangażowany podzapytywanie.

Podzapytania z NOT IN

Podzapytania wprowadzone za pomocą słowa kluczowego NOT IN zwracają również listę wartości zero lub więcej. Poniższe zapytanie znajduje nazwy produktów, które nie są gotowe rowery.

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

Nie można przekonwertować tej instrukcji na sprzężenie. Analogiczne sprzężenia niezrównowate mają inne znaczenie: znajduje nazwy produktów, które znajdują się w podkategorii, która nie jest gotowym rowerem.

Podzapytania w instrukcjach UPDATE, DELETE i INSERT

Podzapytania można zagnieżdżać w instrukcjach UPDATE, DELETE, INSERTi SELECT manipulowania danymi (DML).

Poniższy przykład podwaja wartość w ListPrice kolumnie Production.Product w tabeli. Podzapytywanie w klauzuli WHERE odwołuje się Purchasing.ProductVendor do tabeli w celu ograniczenia wierszy zaktualizowanych w tabeli Product do tylko tych dostarczonych przez BusinessEntity1540program .

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

Oto równoważna UPDATE instrukcja używająca sprzężenia:

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

Aby uzyskać jasność w przypadku, gdy ta sama tabela jest przywołynięta w innych podzapytaniach, użyj aliasu tabeli docelowej:

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

Podzapytania z operatorami porównania

Podzapytania można wprowadzać za pomocą jednego z operatorów porównania (=, < >, , >> =, <, ! >! <lub < =).

Podzapytywanie wprowadzone z niezmodyfikowanym operatorem porównania (operator porównania, po ANY którym nie następuje lub ALL) musi zwracać pojedynczą wartość zamiast listy wartości, takich jak podzapytania wprowadzone za pomocą INpolecenia . Jeśli taka podzapytywanie zwróci więcej niż jedną wartość, program SQL Server wyświetli komunikat o błędzie.

Aby użyć podzapytania wprowadzonego z niezmodyfikowanym operatorem porównania, musisz być wystarczająco zaznajomiony z danymi i charakterem problemu, aby wiedzieć, że podzapytywanie zwróci dokładnie jedną wartość.

Jeśli na przykład zakładasz, że każda osoba sprzedaży obejmuje tylko jedno terytorium sprzedaży i chcesz znaleźć klientów znajdujących się na terytorium objętym Linda Mitchellusługą , możesz napisać instrukcję z podzapytaniem wprowadzonym za pomocą prostego = operatora porównania.

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

Jeśli jednak Linda Mitchell objęto więcej niż jedno terytorium sprzedaży, zostanie wyświetlony komunikat o błędzie. = Zamiast operatora porównania można użyć formuły IN (=ANY również działa).

Podzapytania wprowadzone z niezmodyfikowanymi operatorami porównania często obejmują funkcje agregujące, ponieważ zwracają one jedną wartość. Na przykład poniższa instrukcja znajduje nazwy wszystkich produktów, których cena katalogowa jest większa niż średnia cena katalogowa.

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

Ponieważ podzapytania wprowadzone z niezmodyfikowanymi operatorami porównania muszą zwracać pojedynczą wartość, nie mogą zawierać GROUP BY ani HAVING klauzul, chyba że wiesz, że GROUP BY sama klauzula or HAVING zwraca pojedynczą wartość. Na przykład następujące zapytanie znajduje produkty wyceniane wyżej niż produkt o najniższej cenie, który znajduje się w katalogu ProductSubcategoryID14.

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

Operatory porównania zmodyfikowane przez ANY, SOMElub ALL

Operatory porównania, które wprowadzają podzapytywanie, mogą być modyfikowane przez słowa kluczowe ALL lub ANY. SOME jest standardowym odpowiednikiem standardu ISO dla elementu ANY. Aby uzyskać więcej informacji na temat tych operatorów porównania, zobacz SOME | DOWOLNY.

Podzapytania wprowadzone za pomocą zmodyfikowanego operatora porównania zwracają listę zer lub więcej wartości i mogą zawierać klauzulę GROUP BY or HAVING . Te podzapytania można odtworzyć za pomocą polecenia EXISTS.

> Użycie operatora porównania jako przykładu > ALL oznacza więcej niż każdą wartość. Innymi słowy, oznacza to więcej niż wartość maksymalną. Na przykład oznacza wartość > ALL (1, 2, 3) większą niż 3. > ANY oznacza większą niż co najmniej jedną wartość, czyli większą niż wartość minimalną. Oznacza to > ANY (1, 2, 3) więcej niż 1.

Aby wiersz w podzapytaniu > ALL spełniał warunek określony w zapytaniu zewnętrznym, wartość w kolumnie wprowadzającej podzapytywanie musi być większa niż każda wartość na liście wartości zwracanych przez podzapytywanie.

Podobnie oznacza, > ANY że aby wiersz spełniał warunek określony w zapytaniu zewnętrznym, wartość w kolumnie, która wprowadza podzapytywanie musi być większa niż co najmniej jedna z wartości na liście wartości zwracanych przez podzapytywanie.

Poniższe zapytanie zawiera przykład podzapytania wprowadzonego za pomocą operatora porównania zmodyfikowanego przez ANY. Znajduje produkty, których ceny list są większe lub równe maksymalnej cenie katalogowej dowolnej podkategorii produktów.

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

Dla każdej podkategorii Product zapytanie wewnętrzne znajduje maksymalną cenę cennika. Zapytanie zewnętrzne analizuje wszystkie te wartości i określa, które ceny list poszczególnych produktów są większe lub równe maksymalnej cenie katalogowej dowolnego podkategorii produktu. Jeśli ANY parametr zostanie zmieniony na ALL, zapytanie zwraca tylko te produkty, których cena katalogowa jest większa lub równa wszystkim cenom list zwracanym w zapytaniu wewnętrznym.

Jeśli podzapytywanie nie zwraca żadnych wartości, całe zapytanie nie zwróci żadnych wartości.

Operator = ANY jest odpowiednikiem IN. Aby na przykład znaleźć nazwy wszystkich produktów wheel, które tworzy Adventure Works Cycles, możesz użyć IN polecenia lub = 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

Oto zestaw wyników dla dowolnego zapytania:

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)

Jednak <> ANY operator różni się od NOT IN:

  • <> ANY oznacza nie = a, lub nie = b, lub nie = c
  • NOT IN oznacza nie = a, a nie = b, a nie = c
  • <> ALL oznacza to samo co NOT IN

Na przykład poniższe zapytanie znajduje klientów znajdujących się na terytorium, które nie jest objęte żadnymi osobami sprzedaży.

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

Wyniki obejmują wszystkich klientów, z wyjątkiem tych, których terytoria sprzedaży mają wartość NULL, ponieważ każde terytorium przypisane do klienta jest objęte przez osobę sprzedażową. Zapytanie wewnętrzne znajduje wszystkie terytoria sprzedaży objęte osobami sprzedaży, a następnie, dla każdego terytorium, zapytanie zewnętrzne znajduje klientów, którzy nie są w jednym.

Z tego samego powodu, jeśli używasz NOT IN w tym zapytaniu, wyniki nie obejmują żadnego z klientów.

Możesz uzyskać te same wyniki za pomocą <> ALL operatora , który jest odpowiednikiem NOT IN.

Podzapytania za pomocą polecenia EXISTS

Gdy podzapytywanie jest wprowadzane ze słowem kluczowym EXISTS, podzapytywanie działa jako test istnienia. Klauzula WHERE zapytania zewnętrznego sprawdza, czy istnieją wiersze zwracane przez podzapytywanie. Podzapytywanie nie generuje żadnych danych; zwraca wartość TRUE lub FALSE.

Podzapytywanie wprowadzone w programie EXISTS ma następującą składnię: WHERE [NOT] EXISTS (subquery)

Następujące zapytanie znajduje nazwy wszystkich produktów, które znajdują się w podkategorii Wheel:

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

Oto zestaw wyników.

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)

Aby zrozumieć wyniki tego zapytania, należy wziąć pod uwagę nazwę każdego produktu z kolei. Czy ta wartość powoduje, że podzapytywanie zwróci co najmniej jeden wiersz? Innymi słowy, czy zapytanie powoduje, że test istnienia ma wartość TRUE?

Podzapytania wprowadzone w systemie EXISTS różnią się nieco od innych podzapytania w następujący sposób:

  • Słowo kluczowe EXISTS nie jest poprzedzone nazwą kolumny, stałą ani innym wyrażeniem.
  • Lista wyboru podzapytania wprowadzonego przez EXISTS prawie zawsze składa się z gwiazdki (*). Nie ma powodu, aby wyświetlić listę nazw kolumn, ponieważ po prostu testujesz, czy wiersze spełniające warunki określone w podzapytaniu istnieją.

Słowo EXISTS kluczowe jest ważne, ponieważ często nie ma alternatywnego sformułowania bez podzapytania. Chociaż niektóre zapytania utworzone za pomocą EXISTS polecenia nie mogą być wyrażone w inny sposób, wiele zapytań może użyć IN lub operator porównania zmodyfikowany przez ANY lub ALL osiągnąć podobne wyniki.

Na przykład powyższe zapytanie można wyrazić przy użyciu polecenia IN:

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

Podzapytania za pomocą polecenia NOT EXISTS

NOT EXISTS działa jak EXISTS, z wyjątkiem klauzuli WHERE jest spełniony, jeśli żadne wiersze nie są zwracane przez podzapytywanie.

Aby na przykład znaleźć nazwy produktów, które nie znajdują się w podkategorii kół:

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

Podzapytania używane zamiast wyrażenia

W języku Transact-SQL podzapytywanie może być zastępowane w dowolnym miejscu, w jakim wyrażenie może być używane w SELECTinstrukcjach , UPDATE, INSERTi DELETE z wyjątkiem ORDER BY listy.

W poniższym przykładzie pokazano, jak można użyć tego rozszerzenia. To zapytanie znajduje ceny wszystkich produktów rowerów górskich, ich średnią cenę i różnicę między ceną każdego roweru górskiego a średnią ceną.

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

Related content