Sdílet prostřednictvím

Poddotazy (SQL Server)

Platí na:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytický platformový systém (PDW)SQL databáze v Microsoft Fabric

Poddotaz je dotaz, který je vnořený uvnitř příkazu SELECT, INSERT, UPDATE, nebo DELETE nebo uvnitř jiného poddotazu.

Ukázky kódu v tomto článku používají ukázkovou databázi AdventureWorks2025 nebo AdventureWorksDW2025, kterou si můžete stáhnout z domovské stránky Microsoft SQL Serveru pro ukázky a komunitní projekty .

Poddotaz lze použít všude, kde je výraz povolený. V tomto příkladu se poddotaz využívá jako výraz ve sloupci s názvem MaxUnitPrice v příkazu 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

Základy poddotazů

Poddotaz se také nazývá vnitřní dotaz nebo vnitřní výběr, zatímco příkaz obsahující poddotaz se také nazývá vnější dotaz nebo vnější výběr.

Mnoho Transact-SQL příkazů, které obsahují poddotazy, lze alternativně formulovat jako spojení. Další otázky mohou být kladeny pouze u poddotazů. V jazyce Transact-SQL obvykle není rozdíl mezi příkazem, který obsahuje poddotaz, a sémanticky ekvivalentní verzí, která ho neobsahuje. Informace o architektuře o tom, jak SQL Server zpracovává dotazy, najdete v tématu Zpracování příkazů SQL. V některých případech, kde je potřeba ověřit existenci, je spojení efektivnější. Jinak musí být vnořený dotaz zpracován pro každý výsledek vnějšího dotazu, aby se zajistilo odstranění duplicit. V takových případech by spojovací metoda přinesla lepší výsledky.

Následující příklad ukazuje poddotaz SELECT i spojení SELECT , které vrací stejnou sadu výsledků a plán provádění:

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

Poddotaz vnořený do vnějšího SELECT příkazu má následující komponenty:

  • Běžný SELECT dotaz, včetně běžných součástí seznamu výběrů.
  • Regulární FROM klauzule včetně jedné nebo více názvů tabulek nebo zobrazení.
  • Volitelná klauzule WHERE.
  • Volitelná klauzule GROUP BY.
  • Volitelná klauzule HAVING.

Dotaz SELECT poddotazů je vždy uzavřen v závorkách. Nemůže obsahovat klauzuli COMPUTE ani FOR BROWSE klauzuli a může obsahovat ORDER BY pouze klauzuli, pokud TOP je klauzule také zadaná.

Poddotaz může být vnořený uvnitř WHERE nebo HAVING klauzule vnějšího SELECT, INSERT, UPDATE, nebo DELETE příkazu nebo uvnitř jiného poddotazu. Je možné použít až 32 úrovní vnoření, i když se limit liší v závislosti na dostupné paměti a složitosti jiných výrazů v dotazu. Jednotlivé dotazy nepodporují vnoření až 32 úrovní. Poddotaz se může zobrazit kdekoli, kde se dá výraz použít, pokud vrátí jednu hodnotu.

Pokud se tabulka zobrazí jenom v poddotadu, a ne ve vnějším dotazu, sloupce z této tabulky nelze zahrnout do výstupu (výběrový seznam vnějšího dotazu).

Příkazy, které obsahují poddotaz, obvykle mají jeden z těchto formátů:

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

V některých Transact-SQL příkazech je možné poddotaz vyhodnotit, jako by šlo o nezávislý dotaz. Koncepčně se výsledky poddotazů nahradí vnějším dotazem (i když to nemusí nutně způsob, jakým SQL Server ve skutečnosti zpracovává příkazy Transact-SQL s poddotazy).

Existují tři základní typy poddotazů. Ty, které:

  • Pracujte se seznamy zavedenými pomocí IN, nebo těmi, které byly upraveny operátorem porovnání pomocí ANY nebo ALL.
  • Používají neupravený porovnávací operátor a musí vrátit jednu hodnotu.
  • Existují zavedné testy s EXISTS?

Pravidla poddotazů

Poddotaz podléhá následujícím omezením:

  • Výběrový seznam poddotazu zavedeného s porovnávacím operátorem může obsahovat pouze jeden výraz nebo název sloupce (s tím rozdílem, že EXISTS a IN pracuje s SELECT * nebo se seznamem).
  • WHERE Pokud klauzule vnějšího dotazu obsahuje název sloupce, musí být kompatibilní se sloupcem v seznamu výběrů poddotazů.
  • Datové typy ntext, text a obrázek nelze použít v seznamu výběrových poddotazů.
  • Vzhledem k tomu, že musí vrátit jednu hodnotu, poddotazy zavedené operátorem nemodifikovaného porovnání (za kterým není následovat klíčové slovo ANY nebo ALL) nemohou obsahovat GROUP BY a HAVING klauzule.
  • Klíčové DISTINCT slovo nelze použít s poddotazy, které obsahují GROUP BY.
  • Klauzule COMPUTE a INTO nelze zadat.
  • ORDER BY lze zadat pouze tehdy, pokud je zadán také TOP.
  • Zobrazení vytvořené pomocí poddotazů nejde aktualizovat.
  • Výběrový seznam poddotazů zavedených EXISTSpodle konvence má hvězdičku (*) místo názvu jednoho sloupce. Pravidla pro poddotaz zavedený s EXISTS jsou stejná jako pravidla pro standardní seznam výběrových dotazů, protože poddotaz zavedený s EXISTS vytváří test existence a namísto vracení dat vrátí hodnotu TRUE nebo FALSE.

Kvalifikace názvů sloupců v poddotazech

V následujícím příkladu je sloupec BusinessEntityID ve vnější klauzuli dotazu WHERE implicitně kvalifikovaný názvem tabulky v klauzuli FROM (Sales.Store). Odkaz na CustomerID ve výběrovém seznamu poddotazu je určen klauzulí poddotazu FROM, tj. tabulkou Sales.Customer.

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

Obecné pravidlo je, že názvy sloupců v příkazu jsou implicitně kvalifikované tabulkou odkazovanou v FROM klauzuli na stejné úrovni. Pokud sloupec v tabulce odkazované v FROM klauzuli poddotazu neexistuje, je implicitně kvalifikovaný tabulkou odkazovanou v FROM klauzuli vnějšího dotazu.

Takto vypadá dotaz s těmito implicitními předpoklady:

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

Nikdy není špatné uvést název tabulky explicitně a vždy je možné přepsat implicitní předpoklady o názvech tabulek pomocí explicitních kvalifikací.

Important

Pokud se na sloupec odkazuje v poddotazu, který v tabulce odkazované klauzulí poddotazce FROM neexistuje, ale existuje v tabulce odkazované klauzulí vnějšího FROM dotazu, spustí se dotaz bez chyby. SQL Server implicitně kvalifikuje sloupec v poddotazu názvem tabulky ve vnějším dotazu.

Více úrovní vnoření

Poddotaz může obsahovat jeden nebo více poddotazů. Příkaz může obsahovat libovolný počet vnořených poddotazů.

Následující dotaz najde jména zaměstnanců, kteří jsou také prodejci.

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

Tady je soubor výsledků.

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)

Nejvnitřnější dotaz vrátí ID prodejní osoby. V další vyšší úrovni se dotaz vyhodnocuje s ID prodejních osob a vrací kontaktní ID zaměstnanců. Vnější dotaz nakonec pomocí ID kontaktů najde jména zaměstnanců.

Tento dotaz můžete také vyjádřit jako spojení:

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

Korelované poddotazy

Mnoho dotazů je možné vyhodnotit spuštěním poddotazu jednou a nahrazením výsledné hodnoty nebo hodnot do WHERE klauzule vnějšího dotazu. V dotazech, které obsahují korelovaný poddotaz (označovaný také jako opakující se poddotaz), závisí poddotaz na vnějším dotazu pro jeho hodnoty. To znamená, že poddotaz se provádí opakovaně, jednou pro každý řádek, který může být vybrán vnějším dotazem.

Tento dotaz načte jednotlivý záznam křestního jména a příjmení každého zaměstnance, jehož bonus v tabulce SalesPerson je 5000 a u kterých se identifikační čísla zaměstnanců shodují v tabulkách Employee a 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

Tady je soubor výsledků.

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

(2 row(s) affected)

Předchozí poddotaz v tomto příkazu nelze vyhodnotit nezávisle na vnějším dotazu. Potřebuje hodnotu pro Employee.BusinessEntityID, ale tato hodnota se změní, protože SQL Server zkoumá různé řádky v Employee. Přesně tak se tento dotaz vyhodnocuje: SQL Server považuje každý řádek Employee tabulky za zahrnutí do výsledků nahrazením hodnoty v každém řádku do vnitřního dotazu. Pokud například SQL Server nejprve prozkoumá řádek Syed Abbas, proměnná Employee.BusinessEntityID převezme hodnotu 285, kterou SQL Server nahradí vnitřním dotazem. Tyto dva ukázky dotazu představují rozklad předchozí ukázky s korelovaným poddotazem.

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

Výsledek je 0,00 (Syed Abbas nedostal žádný bonus, protože není prodejcem), takže se vnější dotaz vyhodnotí na:

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

Vzhledem k tomu, že je tato hodnota false, řádek pro Syed Abbas není součástí výsledků předchozího ukázkového dotazu s korelovaným poddotazem. Projděte si stejný postup s řádkem pro Pamela Ansman-Wolfe. Uvidíte, že tento řádek je součástí výsledků, protože WHERE 5000 IN (5000) obsahuje výsledky.

Korelované poddotazy můžou v klauzuli také obsahovat funkce FROM s hodnotami tabulky odkazováním na sloupce z tabulky ve vnějším dotazu jako argument funkce s hodnotou tabulky. V tomto případě se pro každý řádek vnějšího dotazu vyhodnotí funkce s hodnotou tabulky podle poddotazu.

Typy poddotazů

Poddotazy lze zadat na mnoha místech:

Poddotazy s aliasy pro tabulky

Mnoho příkazů, ve kterých poddotaz a vnější dotaz odkazují na stejnou tabulku, lze uvést jako spojení sebe sama (spojování tabulky k sobě). Pomocí poddotazů můžete například najít adresy zaměstnanců z konkrétního státu:

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

Tady je soubor výsledků.

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

(4 row(s) affected)

Nebo můžete použít samoobslužné připojení:

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 tabulek a e2 jsou povinné, protože tabulka, která je připojená k sobě, se zobrazuje ve dvou různých rolích. Aliasy lze použít také v vnořených dotazech, které odkazují na stejnou tabulku ve vnitřním a vnějším dotazu.

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

Explicitní aliasy tabulek jasně ukazují, že odkaz na Person.Address poddotaz neznamená totéž jako odkaz ve vnějším dotazu.

Poddotazy s IN

Výsledkem poddotazu zavedeného s IN (nebo s NOT IN) je seznam nulových nebo více hodnot. Jakmile poddotaz vrátí výsledky, vnější dotaz je použije. Následující dotaz najde názvy všech produktů kol, které společnost Adventure Works Cycles vyrábí.

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

Tady je soubor výsledků.

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)

Tento příkaz se vyhodnotí ve dvou krocích. Nejprve vnitřní dotaz vrátí identifikační číslo podkategorie, které odpovídá názvu Wheel (17). Za druhé, tato hodnota je nahrazena ve vnějším dotazu, který najde názvy produktů přiřazené k identifikačním číslům podkategorií v Production.Product.

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

Jedním rozdílem při použití spojení místo poddotazů pro tento a podobný problém je, že spojení umožňuje zobrazit sloupce z více než jedné tabulky ve výsledku. Pokud například chcete do výsledku zahrnout název podkategorie produktu, musíte použít verzi join.

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

Tady je soubor výsledků.

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)

Následující dotaz najde název všech dodavatelů, jejichž hodnocení kredibility je dobré, od kterého Adventure Works Cycles objednává nejméně 20 položek a jejichž průměrná doba dodání je menší než 16 dnů.

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

Tady je soubor výsledků.

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)

Vnitřní dotaz se vyhodnotí a vytvoří čísla ID dodavatelů, kteří splňují kvalifikace poddotazů. Vnější dotaz se pak vyhodnotí. Do klauzule vnitřního i vnějšího dotazu můžete zahrnout více než jednu podmínku WHERE .

Při použití spojení se stejný dotaz vyjadřuje takto:

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

Spojení lze vždy vyjádřit jako poddotaz. Poddotaz může být často, ale ne vždy vyjádřen jako spojení. Důvodem je, že spojení jsou symetrická: tabulku A můžete spojit v B libovolném pořadí a získat stejnou odpověď. Totéž neplatí v případě, že je zapojen poddotaz.

Poddotazy s FUNKCÍ NOT IN

Poddotazy zavedené s klíčovým slovem NOT IN vrací také seznam nulových nebo více hodnot. Následující dotaz najde názvy produktů, které nejsou hotová jízdní kolo.

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

Tento příkaz nelze převést na spojení. Analogické nerovné spojení má jiný význam: Najde názvy produktů, které jsou v nějaké podkategorii, která není hotovým jízdním kolem.

Poddotazy v příkazech UPDATE, DELETE a INSERT

Poddotazy lze vnořit do UPDATE, DELETE, INSERT a SELECT příkazů pro manipulaci s daty (DML).

Následující příklad zdvojnásobí hodnotu ve ListPrice sloupci v Production.Product tabulce. Poddotaz v WHERE klauzuli odkazuje na tabulku Purchasing.ProductVendor, aby omezil aktualizaci řádků v tabulce Product pouze na ty, které poskytuje BusinessEntity1540.

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

Tady je ekvivalentní příkaz s použitím JOINu:

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

Pro přehlednost v případě, že se na stejnou tabulku odkazuje v jiných poddotazech, použijte alias cílové tabulky:

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

Poddotazy s porovnávacími operátory

Poddotazy lze zavést pomocí jednoho z relačních operátorů (=, < >, >, > =, <, ! >, ! <nebo < =).

Poddotaz zavedený pomocí neupraveného operátoru porovnání (operátoru porovnání, za kterým nenásleduje ANY nebo ALL) musí vrátit jednu hodnotu, nikoli seznam hodnot, na rozdíl od poddotazů zavedených pomocí IN. Pokud takový poddotaz vrátí více než jednu hodnotu, sql Server zobrazí chybovou zprávu.

Pokud chcete použít poddotaz zavedený s nemodifikovaným relačním operátorem, musíte být obeznámeni s daty a povahou problému, abyste věděli, že poddotaz vrátí přesně jednu hodnotu.

Pokud například předpokládáte, že každý obchodní zástupce pokrývá pouze jednu prodejní oblast a chcete najít zákazníky nacházející se v oblasti, kterou pokrývá Linda Mitchell, můžete napsat příkaz s poddotazem, který je zaveden jednoduchým = operátorem porovnání.

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

Pokud by však Linda Mitchell pokrývalo více než jedno prodejní území, výsledkem by byla chybová zpráva. Místo relačního operátoru =IN lze použít formulaci (=ANY také funguje).

Poddotazy zavedené s neupravenými relačními operátory často zahrnují agregační funkce, protože vracejí jednu hodnotu. Například následující příkaz najde názvy všech produktů, jejichž ceníková cena je větší než průměrná ceníková cena.

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

Vzhledem k tomu, že poddotazy zavedené s nemodifikovanými relačními operátory musí vrátit jednu hodnotu, nemohou zahrnovat GROUP BY ani HAVING klauzule, pokud nevíte, že GROUP BY nebo HAVING klauzule sama vrací jednu hodnotu. Například následující dotaz najde produkty s cenou vyšší než produkt s nejnižší cenou, který je v ProductSubcategoryID14.

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

Relační operátory změněné ANY, SOME nebo ALL

Relační operátory, které zavádějí poddotaz, lze upravit klíčovými slovy ALL nebo ANY. SOME je standardním ekvivalentem ISO pro ANY. Další informace o těchto relačních operátorech naleznete v tématu SOME | ANY.

Poddotazy zavedené s upraveným relačním operátorem vrací seznam nulových nebo více hodnot a můžou obsahovat GROUP BY klauzuli nebo HAVING klauzuli. Tyto poddotazy lze přepočítat pomocí EXISTS.

Příklad použití porovnávacího operátoru > ukazuje, že > ALL je větší než každá hodnota. Jinými slovy, znamená to větší než maximální hodnota. Například > ALL (1, 2, 3) znamená je větší než 3. > ANY znamená větší než alespoň jedna hodnota, tj. větší než minimum. To > ANY (1, 2, 3) znamená větší než 1.

Pro řádek v poddotazu s > ALL, aby splnil podmínku zadanou ve vnějším dotazu, musí být hodnota ve sloupci zavádějícím poddotaz větší než každá hodnota v seznamu hodnot vrácených poddotazem.

Podobně to znamená, > ANY že aby řádek splňoval podmínku zadanou ve vnějším dotazu, musí být hodnota ve sloupci, který zavádí poddotaz, větší než alespoň jedna z hodnot v seznamu hodnot vrácených poddotazem.

Následující dotaz poskytuje příklad poddotazu zavedeného s relačním operátorem upraveným ANY. Najde produkty, jejichž ceníkové ceny jsou větší nebo rovny maximální ceníkové ceně libovolné podkategorie produktu.

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

Pro každou podkategorii produktu vnitřní dotaz najde maximální ceníkovou cenu. Vnější dotaz se podívá na všechny tyto hodnoty a určí, které ceny seznamu jednotlivých produktů jsou větší nebo rovny maximální ceně seznamu produktů. Pokud ANY se změní na ALL, dotaz vrátí pouze ty produkty, jejichž ceníková cena je větší nebo rovna všem cenám seznamu vrácených ve vnitřním dotazu.

Pokud poddotaz nevrací žádné hodnoty, celý dotaz nevrátí žádné hodnoty.

Operátor = ANY je ekvivalentní operátoru IN. Chcete-li například najít názvy všech produktů kol, které společnost Adventure Works Cycles vyrábí, můžete použít buď IN nebo = 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

Tady je sada výsledků pro některý z dotazů:

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)

Operátor <> ANY se však liší od NOT IN:

  • <> ANY znamená ne = a, nebo ne = b, nebo ne = c
  • NOT IN znamená ne = a, a ne = b, a ne = c
  • <> ALL znamená totéž jako NOT IN

Následující dotaz například najde zákazníky nacházející se na území, které není pokryto žádnými obchodními zástupci.

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

Výsledky zahrnují všechny zákazníky s výjimkou těch, jejichž prodejní území jsou NULL, protože každé území, které je přiřazeno zákazníkovi, je pokryto prodejní osobou. Vnitřní dotaz najde všechna prodejní teritoria pokrytá obchodními zástupci, a pak vnější dotaz najde zákazníky, kteří nejsou v žádném z nich.

Z stejného důvodu při použití NOT IN v tomto dotazu výsledky neobsahují žádné zákazníky.

Stejné výsledky můžete získat pomocí operátoru <> ALL , který je ekvivalentní NOT IN.

Poddotazy s EXISTS

Když je poddotaz zaveden s klíčovým slovem EXISTS, poddotaz funguje jako test existence. Klauzule WHERE vnějšího dotazu testuje, zda existují řádky vrácené poddotazem. Poddotaz ve skutečnosti nevygeneruje žádná data; vrátí hodnotu TRUE nebo FALSE.

Poddotaz zavedený s EXISTS následující syntaxí: WHERE [NOT] EXISTS (subquery)

Následující dotaz najde názvy všech produktů, které jsou v podkategorii Kola:

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

Tady je soubor výsledků.

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)

Pokud chcete porozumět výsledkům tohoto dotazu, zvažte také název každého produktu. Způsobuje tato hodnota, že poddotaz vrátí alespoň jeden řádek? Jinými slovy, způsobí dotaz, že test existence bude vyhodnocen jako TRUE?

Poddotazy, se kterými jsou zavedeny EXISTS , se trochu liší od jiných poddotazů následujícími způsoby:

  • Klíčové slovo EXISTS není před názvem sloupce, konstantou nebo jiným výrazem.
  • Výběrový seznam poddotazů zavedených EXISTS téměř vždy se skládá z hvězdičky (*). Není důvod vypsat názvy sloupců, protože právě testujete, jestli existují řádky, které splňují podmínky zadané v poddotazu.

Klíčové EXISTS slovo je důležité, protože často neexistuje alternativní formulace bez poddotazů. I když některé dotazy vytvořené pomocí EXISTS nelze vyjádřit jiným způsobem, mnoho dotazů může použít IN nebo operátor porovnání upravený pomocí ANY nebo ALL k dosažení podobných výsledků.

Předchozí dotaz lze například vyjádřit pomocí:IN

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

Poddotazy s NOT EXISTS

NOT EXISTS funguje podobně jako EXISTS, s výjimkou WHERE klauzule je splněna, pokud poddotaz nevrátí žádné řádky.

Pokud například chcete najít názvy produktů, které nejsou v podkategorii kola:

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

Poddotazy použité místo výrazu

V jazyce Transact-SQL lze poddotaz nahradit všude, kde lze výraz použít v SELECT, UPDATE, INSERTa DELETE příkazy s výjimkou seznamu ORDER BY .

Následující příklad ukazuje, jak můžete toto vylepšení použít. Tento dotaz najde ceny všech produktů horských kol, jejich průměrnou cenu a rozdíl mezi cenou každého horského kola a průměrnou cenou.

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

Související obsah

  • Last updated on