Použití samostatných nebo korelovaných poddotazů

  • 3 min

Dříve jsme se podívali na samostatné poddotazy; ve kterém je vnitřní dotaz nezávislý na vnějším dotazu, provede se jednou a vrátí jeho výsledky do vnějšího dotazu. T-SQL také podporuje korelované poddotazy, ve kterých vnitřní dotaz odkazuje na sloupec ve vnějším dotazu a koncepčně provede jednou za řádek.

Práce s korelovanými poddotazy

Podobně jako u samostatných poddotazů jsou korelované poddotazy příkazy SELECT vnořené do vnějšího dotazu. Korelované poddotazy mohou být také skalární nebo poddotazy s více hodnotami. Obvykle se používají, když vnitřní dotaz potřebuje odkazovat na hodnotu ve vnějším dotazu.

Na rozdíl od samostatných poddotazů je však při použití korelovaných poddotazů potřeba zvážit několik zvláštních aspektů:

  • Korelované poddotazy nelze spustit odděleně od vnějšího dotazu. Toto omezení komplikuje testování a ladění.
  • Na rozdíl od samostatných poddotazů, které se zpracovávají jednou, se korelované poddotazy spustí několikrát. Logicky se vnější dotaz spustí jako první a pro každý vrácený řádek se zpracuje vnitřní dotaz.

Následující příklad používá korelovaný poddotaz k vrácení nejnovější objednávky pro každého zákazníka. Poddotaz odkazuje na vnější dotaz a odkazuje na jeho hodnotu CustomerID v klauzuli WHERE. Pro každý řádek ve vnějším dotazu poddotaz najde maximální ID objednávky pro zákazníka odkazovaného na tento řádek a vnější dotaz zkontroluje, jestli je řádek, na který se dívá, řádek s tímto ID objednávky.

SELECT SalesOrderID, CustomerID, OrderDate
FROM SalesLT.SalesOrderHeader AS o1
WHERE SalesOrderID =
    (SELECT MAX(SalesOrderID)
     FROM SalesLT.SalesOrderHeader AS o2
     WHERE o2.CustomerID = o1.CustomerID)
ORDER BY CustomerID, OrderDate;

Psaní korelovaných poddotazů

Pokud chcete psát korelované poddotazy, zvažte následující pokyny:

  • Napište vnější dotaz, který přijme odpovídající výsledek vrácení z vnitřního dotazu. Pokud je vnitřní dotaz skalární, můžete použít operátory rovnosti a porovnání, například =, <, >a <>, v klauzuli WHERE. Pokud vnitřní dotaz může vrátit více hodnot, použijte predikát IN. Naplánujte zpracování výsledků s hodnotou NULL.
  • Identifikujte sloupec z vnějšího dotazu, na který bude odkazovat korelovaný poddotaz. Deklarujte alias pro tabulku, která je zdrojem sloupce ve vnějším dotazu.
  • Identifikujte sloupec z vnitřní tabulky, který se porovná se sloupcem z vnější tabulky. Vytvořte alias pro zdrojovou tabulku, jak jste to udělali pro vnější dotaz.
  • Napište vnitřní dotaz, který načte hodnoty ze svého zdroje na základě vstupní hodnoty z vnějšího dotazu. Například použijte vnější sloupec v klauzuli WHERE vnitřního dotazu.

Korelace mezi vnitřními a vnějšími dotazy nastane, když na vnější hodnotu odkazuje vnitřní dotaz pro porovnání. Je to tato korelace, která dává poddotaz svému názvu.

Práce s EXIST

Kromě načítání hodnot z poddotazů poskytuje T-SQL mechanismus pro kontrolu, jestli se z dotazu vrátí nějaké výsledky. Predikát EXISTS určuje, zda existují nějaké řádky, které splňují zadanou podmínku, ale nevrací je, vrátí hodnotu PRAVDA nebo NEPRAVDA. Tato technika je užitečná pro ověřování dat, aniž by došlo k režii načítání a zpracování výsledků.

Pokud poddotaz souvisí s vnějším dotazem pomocí predikátu EXISTS, SQL Server zpracuje výsledky poddotazu zvláštním způsobem. Místo načtení skalární hodnoty nebo vícehodnotového seznamu z poddotazů funkce EXISTS jednoduše zkontroluje, jestli ve výsledku nejsou nějaké řádky.

Predikát EXISTS je koncepčně ekvivalentní načtení výsledků, počítání vrácených řádků a porovnávání počtu s nulou. Porovnejte následující dotazy, které vrátí podrobnosti o zákaznících, kteří zadali objednávky:

První ukázkový dotaz používá v poddotadu funkci COUNT:

SELECT CustomerID, CompanyName, EmailAddress 
FROM Sales.Customer AS c 
WHERE
(SELECT COUNT(*) 
  FROM Sales.SalesOrderHeader AS o
  WHERE o.CustomerID = c.CustomerID) > 0;

Druhý dotaz, který vrátí stejné výsledky, používá funkci EXISTS:

SELECT CustomerID, CompanyName, EmailAddress 
FROM Sales.Customer AS c 
WHERE EXISTS
(SELECT * 
  FROM Sales.SalesOrderHeader AS o
  WHERE o.CustomerID = c.CustomerID);

V prvním příkladu musí poddotaz spočítat všechny výskyty jednotlivých custid nalezených v tabulce Sales.SalesOrderHeader a porovnat výsledky počtu s nulou, stačí jednoduše určit, že zákazník zadal objednávky.

V druhém dotazu vrátí funkce EXISTS hodnotu TRUE pro custid , jakmile se v tabulce Sales.SalesOrderHeader najde příslušná objednávka. Úplné účtování každého výskytu není nutné. Všimněte si také, že poddotaz ve formuláři EXISTS není omezen na vrácení jednoho sloupce. Tady máme SELECT *. Vrácené sloupce jsou irelevantní, protože kontrolujeme, jestli se vůbec nevrátí nějaké řádky, ne jaké hodnoty jsou v těchto řádcích.

Z hlediska logického zpracování jsou oba formuláře dotazu ekvivalentní. Z hlediska výkonu může databázový stroj zacházet s dotazy odlišně, protože je optimalizuje pro provádění. Zvažte testování každého z nich pro vlastní použití.

Poznámka:

Pokud převádíte poddotaz pomocí funkce COUNT(*) na jeden pomocí funkce EXISTS, ujistěte se, že poddotaz používá select * a ne SELECT COUNT(*). FUNKCE SELECT COUNT(*) vždy vrátí řádek, takže funkce EXISTS vždy vrátí hodnotu PRAVDA.

Další užitečnou aplikací funkce EXISTS je negování poddotazů s not, jako v následujícím příkladu, který vrátí zákazníka, který nikdy nevystavil objednávku:

SELECT CustomerID, CompanyName, EmailAddress 
FROM SalesLT.Customer AS c 
WHERE NOT EXISTS
  (SELECT * 
   FROM SalesLT.SalesOrderHeader AS o
   WHERE o.CustomerID = c.CustomerID);

SQL Server nebude muset vracet data o souvisejících objednávkách pro zákazníky, kteří zadali objednávky. Pokud se v tabulce Sales.SalesOrderHeader najde custid, nevyhodnotí se hodnota NEPRAVDA a vyhodnocení se rychle dokončí.

Pokud chcete psát dotazy, které používají poddotazy, zvažte následující pokyny:

  • Klíčové slovo EXISTUJE přímo po WHERE. Žádný název sloupce (nebo jiný výraz) před ním není, pokud není použit také.
  • V poddotazu použijte select *. Poddotaz nevracejí žádné řádky, takže není nutné zadávat žádné sloupce.