Udostępnij za pomocą

Podzapytania (SQL Server)

Dotyczy do:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSystem Platform Analitycznych (PDW)Baza danych SQL w Microsoft Fabric

Podzapytanie to zapytanie zagnieżdżone wewnątrz instrukcji SELECT, INSERT, UPDATE lub DELETE, albo wewnątrz innego podzapytania.

Przykłady kodu w tym artykule korzystają z przykładowej bazy danych AdventureWorks2025 lub AdventureWorksDW2025, 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, gdzie dozwolone jest wyrażenie. W tym przykładzie podzapytanie zostało użyte jako wyrażenie w kolumnie 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

Podstawy podzapytania

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 za pomocą podzapytań. 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 konieczne jest sprawdzenie istnienia, złączenie daje 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 łączenia 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 instrukcji zewnętrznej SELECT ma następujące składniki:

  • Regularne zapytanie SELECT, w tym typowe 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 .

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

Podzapytanie może być zagnieżdżone wewnątrz klauzuli WHERE lub HAVING zewnętrznego SELECT, INSERT, UPDATE lub DELETE, albo 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 przekraczającego 32 poziomy. 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. Te, które:

  • Operuj na listach wprowadzonych za pomocą IN, lub tych, które zostały zmodyfikowane przez operator porównania ANY lub ALL.
  • Są wprowadzane z niezmodyfikowanym operatorem porównania i muszą zwracać pojedynczą wartość.
  • Czy wprowadzono testy istnienia przy użyciu EXISTSpolecenia.

Reguły podzapytania

Podzapytywanie podlega następującym ograniczeniom:

  • Lista wyboru w podzapytaniu wprowadzonym za pomocą operatora porównania może zawierać tylko jedno wyrażenie lub nazwę kolumny (przy czym EXISTS i IN działają odpowiednio na SELECT * lub listę).
  • 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 kluczowego DISTINCT nie można używać z podzapytaniami, które zawierają GROUP BY element.
  • COMPUTE i INTO klauzuli nie można określić.
  • 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, zgodnie z EXISTS konwencją, ma gwiazdkę (*) zamiast pojedynczej nazwy kolumny. Reguły dla podzapytania wprowadzonego za pomocą EXISTS są takie same jak reguły dla standardowej listy wyboru, ponieważ podzapytanie wprowadzone za pomocą EXISTS tworzy test istnienia i zwraca wartość TRUE lub FALSE zamiast danych.

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 na liście wyboru podzapytania jest kwalifikowane przez klauzulę FROM podzapytania Sales.Customer, czyli przez tabelę.

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

Ogólna zasada polega na tym, że nazwy kolumn w instrukcji są niejawnie kwalifikowane przez tabelę przywołaną w klauzurze FROM 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

Zawsze można jawnie podać nazwę tabeli, co nigdy nie jest błędem, i zawsze można nadpisać niejawne założenia dotyczące nazw tabel za pomocą jawnych kwalifikacji.

Important

Jeśli w podzapytaniu odwołanie jest do kolumny, która nie istnieje w tabeli, do której odnosi się klauzula FROM podzapytania, ale istnieje w tabeli, do której odnosi się klauzula FROM zapytania zewnętrznego, zapytanie wykonuje się 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 najgłębsze zwraca identyfikatory przedstawicieli handlowych. Zapytanie na następnym wyższym poziomie jest przetwarzane przy użyciu tych identyfikatorów sprzedawców i zwraca identyfikatory kontaktowe 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 połączenie.

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

Skorelowane podzapytania

Wiele zapytań można ocenić, wykonując podzapytanie raz i podstawiając wartość lub wartości do klauzuli WHERE 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 imienia i nazwiska każdego pracownika, dla którego bonus w tabeli SalesPerson wynosi 5000 i dla którego numery identyfikacyjne pracowników są zgodne w tabelach 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 podzapytanie w tym zapytaniu nie może być oceniane niezależnie od zapytania zewnętrznego. Potrzebuje wartości dla Employee.BusinessEntityID, ale ta wartość zmienia się, gdy SQL Server bada różne wiersze w Employee. Dokładnie tak właśnie oceniane jest to zapytanie: SQL Server analizuje każdy wiersz tabeli Employee, zastępując jego wartość w zapytaniu wewnętrznym, aby uwzględnić je w wynikach. Na przykład, jeśli SQL Server najpierw zbada wiersz Syed Abbas, zmienna Employee.BusinessEntityID przyjmuje wartość 285, którą SQL Server wstawia do zapytania wewnętrznego. 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 nie jest uwzględniony w wynikach poprzedniego przykładowego zapytania z skorelowanym podzapytaniem. Wykonaj tę samą procedurę z wierszem dla Pamela Ansman-Wolfe. Zobaczysz, że ten wiersz jest uwzględniony w wynikach, ponieważ WHERE 5000 IN (5000) zawiera wyniki.

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

Typy podzapytania

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

Podzapytania z aliasami tabel

Wiele zapytań, w których podzapytanie i część zewnętrzna zapytania odnoszą się do tej samej tabeli, można przedstawić 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

Aliase tabel e1 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 zagnieżdżonych zapytaniach, które odwołują się zarówno do tej samej tabeli w zapytaniu wewnętrznym, jak 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 klauzulą 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)

To stwierdzenie jest oceniane 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 są powiązane z numerami identyfikacyjnymi podkategorii w 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 łączenia.

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 przetwarzane, generując numery identyfikatorów dostawców, którzy spełniają warunki 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.

Korzystając z łączenia, to samo zapytanie można wyrazić 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

Łączenie można zawsze wyrazić jako podzapytanie. Podzapytanie może często, ale nie zawsze, być wyrażone jako sprzężenie. Dzieje się tak, ponieważ sprzężenia są symetryczne: tabelę można połączyć AB w jednej kolejności i uzyskać tę samą odpowiedź. Nie jest tak samo, jeśli jest zaangażowane podzapytanie.

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 połączenie. 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 kolumnie ListPrice w tabeli Production.Product. Podzapytanie w klauzuli WHERE odwołuje się do tabeli Purchasing.ProductVendor, aby ograniczyć aktualizowane wiersze w tabeli Product wyłącznie do tych dostarczonych przez BusinessEntity1540.

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 łączenia:

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 (takim operatorem, po którym nie następuje ANY lub ALL) musi zwracać pojedynczą wartość zamiast listy wartości, podobnie jak podzapytania wprowadzone za pomocą IN. 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 | ANY.

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ą. Element > ANY (1, 2, 3) oznacza 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 katalogowe są większe lub równe maksymalnej cenie katalogowej w jakiejkolwiek 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 zostanie zmienione na ALL, zapytanie zwróci tylko te produkty, których cena katalogowa jest większa lub równa wszystkim cenom katalogowym 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 koło, które tworzy Adventure Works Cycles, możesz użyć IN 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 to 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 przedstawicielami handlowymi, a następnie, dla każdego terytorium, zapytanie zewnętrzne znajduje klientów, którzy nie znajdują się w żadnym z nich.

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 z użyciem 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 za pomocą EXISTS polecenia 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 za pomocą EXISTS są nieco inne niż inne 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 kluczowe EXISTS jest ważne, ponieważ często nie ma alternatywnej konstrukcji bez podzapytań. Chociaż niektóre zapytania tworzone za pomocą EXISTS nie mogą być wyrażone inaczej, wiele zapytań może użyć IN lub operatory porównania zmodyfikowane przez ANY lub ALL, aby 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 z użyciem NOT EXISTS

NOT EXISTS działa jak EXISTS, z wyjątkiem, że klauzula WHERE jest spełniona, jeśli żadne wiersze nie są zwracane przez podzapytanie.

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 podzapytanie może zostać zastąpione wszędzie tam, gdzie może być używane wyrażenie w instrukcjach SELECT, UPDATE, INSERT i DELETE, z wyjątkiem listy ORDER BY.

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

Treści powiązane

  • Last updated on