SQL – vnořené dotazy

9. října 2000

•

Vnořené dotazy využijeme tam, kde potřebujeme získat nějaké informace na základě jiných údajů uložených v databázi. V tomto díle si povíme, jak nám mohou být vnořené dotazy užitečné. Zmíním se o zásadách, které musíme dodržovat při psaní vnořených dotazů. Řekneme si o základním jednoduchém vnořování a vnořování s využitím operátorů IN, ANY, SOME a ALL.

Použití vnořených dotazů

Vnořený dotaz není nic jiného, než příkaz SELECT vnořený do jiného příkazu SELECT. Vnořené dotazy využijeme tam, kde potřebujeme nejprve zjistit nějakou informaci a v závislosti na ní zjistit pak informace další. Typickým příkladem by mohl být následující dotaz. Vypište seznam všech zaměstnanců, kteří mají nadprůměrný plat. Pomocí klasických SQL dotazů, které zatím známe, tento výsledek obdržíme ve dvou krocích. Nejprve si zjistíme, jaký je průměrný plat ve firmě (použijeme agregační funkci AVG):

SELECT AVG(plat)
FROM platy

Tento dotaz nám vrátí např. číslo 15600. Toto číslo si zapamatujeme a použijeme jej v konstrukci následujícího dotazu:

SELECT jméno, příjmení
FROM platy
WHERE plat > 15600

Jistě uznáte, byť je tento příklad docela jednoduchý, že by někdy bylo pohodlnější, kdybychom oba kroky mohli spojit do jednoho, tj. napsali bychom jeden příkaz, který by nám potřebný seznam zaměstnanců vrátil.

Jednoduché vnořené dotazy

Za jednoduchý vnořený dotaz budeme považovat vnořený příkaz SELECT vracející nám jednu hodnotu. Můžeme psát restrikce s využitím právě té hodnoty, kterou nám vrátí vnořený SELECT. Vnořený příkaz se píše do závorek a v dotazu je uveden v části WHERE. Výše uvedený dotaz bychom zapsali následovně:

SELECT jméno, příjmení
FROM platy
WHERE plat > (SELECT AVG(plat) FROM platy)

V restrikci výše uvedeného příkazu máme podmínku plat > vypočtená_hodnota. Vypočtená_hodnota se nám vyhodnotí vnořeným SELECTem na jednu hodnotu a máme tedy podmínku zapsanou v pořádku a dotaz je korektní. Pro úplnost uvádím, že vnořený SELECT se vyhodnotí pouze jednou, hodnotu si SQL server „zapamatuje“ a s ní pak porovnává všechny ostatní hodnoty v tabulce.

V jednoduchých vnořených dotazech (někdy nazýváme poddotazy) nám obdobným způsobem budou figurovat i další agregační funkce. V následujících příkladech, demonstrujících užití jednoduchých poddotazů se budu odkazovat na tabulky, které jsem si zadefinovali v 5. díle o jazyce SQL.

Příklady

Zjistěte název a cenu nejlevnější knihy.

SELECT název, cena
FROM kniha, výtisk
WHERE kniha.id = výtisk.publikace_id
AND cena = (SELECT MIN(cena) FROM výtisk)

Vypište jména všech autorů, kteří se podíleli při psaní nejdelší knihy (=s největším počtem stran)

SELECT jméno, příjmení
FROM autoři, napsané_knihy, výtisk
WHERE autoři.id = napsané_knihy.autor_id
AND napsané_knihy.publikace_id = výtisk.publikace_id
AND počet_stran = (SELECT MAX(počet_stran) FROM výtisk)

Poddotazy vracející více hodnot

V předchozích příkladech jsme měli situaci, že vnořený dotaz nám vrátil jednu hodnotu (toto nám v podstatě zajistila agregační funkce). Někdy ale můžeme nechat vyhodnotit poddotaz, který nám vrátí více hodnot. Chceme např. dotaz, vracející nám názvy všech knih vydaných v týchž letech, jako díla Aloise Jiráska. Vnořený poddotaz nám zjistí množinu všech roků, ve kterých bylo kdy něco od Jiráska vydáno a pak vyhledáme všechny knihy vydané v těchto letech. Než uvedu zápis takového dotazu, zmíním se ještě o nových operátorech.

Operátory

Vnořování dotazů nám rozšiřuje klasickou množinu relačních operátorů o operátory další, které se aplikují z levé strany na sloupec a z pravé strany na vnořený dotaz vracející více hodnot. Mezi ně patří operátory IN, ANY (SOME), ALL.

Operátor IN slouží pro jednoduché porovnání, zdali se hodnota sloupce vlevo vyskytuje mezi hodnotami vrácených vnořeným dotazem. Kdybychom např. chtěli názvy knih vydané v letech 1997 až 2000, mohli bychom takový dotaz pomocí operátoru IN zapsat takto:

SELECT název
FROM kniha, výtisk
WHERE kniha.id = výtisk.publikace_id
AND rok IN (1997, 1998, 1999, 2000)

Výraz v závorce je množina všech hodnot, ve kterých se má daná hodnota hledat. Většinou neuvádíme výčet explicitně, ale vrací nám jej nějaký vnořený dotaz. V této chvíli si tedy lze uvést náš dotaz na názvy knih vydaných v týchž letech, jako díla Aloise Jiráska:

SELECT název
FROM kniha, autoři, napsané_knihy, výtisk
WHERE kniha.id = napsané_knihy.publikace_id
  AND napsané_knihy.autor_id = autoři.id
  AND kniha.id = výtisk.publikace_id
  AND rok IN ( SELECT rok
        FROM autoři a, výtisk v, napsané_knihy n
        WHERE a.id = n.autor_id
        AND n.publikace_id = v.publikace_id
        AND a.jméno LIKE ‚Alois‘
        AND a.příjmení LIKE ‚Jirásek‘
      )

Všimněte si, že jsem ve vnořeném dotazu v části FROM použil aliasy pro názvy tabulek. To proto, že tytéž názvy se objevují i v nadřazeném SELECTu. Kdybyste v tomto příkladu aliasů nepoužili, chyba by to nebyla, dotaz by vrátil očekávaná data. Ale někdy (a uvidíme to v příštím díle) je použití aliasů nutné, neboť v některých případech bude SQL server potřebovat vědět, na kterou tabulku v restrikci vnořeného dotazu se odkazujeme – zdali na tu „lokální“ nebo na tu „globální“.

V případě dlouhých názvů některých tabulek je výhodou je v rámci dotazu nahradit kratšími názvy. Podotýkám, že aliasy tabulek se narozdíl od aliasů sloupců, neuvozují modifikátorem AS.

Použití operátorů ANY(SOME) a ALL ukazuje následující syntaxe:

sloupec relační_operátor ANY|SOME|ALL (vnořený SELECT)

Operátor ANY nebo SOME určují, že relace se vztahuje na alespoň jednu z hodnot, kterou vrátí vnořený dotaz, operátor ALL aplikuje relaci na všechny hodnoty vrácené poddotazem.

Příklad

Chceme seznam takových knih od Vítězslava Nezvala, jejichž výtisky nebyly nikdy dražší, než kterýkoliv výtisk díla Aloise Jiráska.

SELECT název
FROM kniha, výtisk, napsané_knihy, autoři
WHERE autoři.id = napsané_knihy.autor_id
  AND napsané_knihy.publikace_id = kniha.id
  AND kniha.id = výtisk.publikace_id
  AND jméno LIKE ‚Vítězslav‘
  AND příjmení LIKE ‚Nezval‘
  AND cena < ALL (SELECT cena
        FROM napsané_knihy n, výtisk v, autoři a
        WHERE v.publikace_id = n.publikace_id
        AND n.autor_id = a.id
        AND a.jméno LIKE ‚Alois‘
        AND a.příjmení LIKE ‚Jirásek‘
      )

Výše uvedený dotaz můžeme přepsat použitím operátoru ANY v kombinaci s operátorem NOT (rozdíl bude jen v řádku, kde uvádíme sloupec CENA):

  …
  AND NOT cena > ANY (SELECT cena
        …

V příštím díle se budu věnovat pokročilým SQL dotazům, podrobně popíšu operátor EXISTS, který nám rozšíří vnořování dotazů o další možnosti. A dále se zmíním o využití vnořených dotazů při manipulaci s daty.