SQL – pokročilé dotazy

16. října 2000

•

V dnešním desátém článku o SQL si prohloubíme znalosti o vnořování SQL dotazů, zejména o použití mocného operátoru EXISTS a přejdeme tak ke tvorbě pokročilých dotazů. K pochopení všech příkladů uváděných v tomto článku, je potřeba mít znalosti v rozsahu všech předchozích dílů. V závěru článku je pro vás připravena malá prověrka znalostí.

Operátor EXISTS

V minulém díle jsem se bavili o jednoduchých vnořených SQL dotazech, pomocí nichž jsme si nejprve zjistili nějakou informaci z databáze a na základě jí jsme pak hledali informace další. Charakteristické pro tyto vnořené dotazy bylo, že vnořený dotaz zapsaný v příkazu SELECT se vyhodnotil pouze jednou a server si vrácené hodnoty zapamatoval. Ke čtveřici operátorů pro vnořené SQL dotazy si přidáme ještě jeden, poslední. Je jím operátor EXISTS. Tento operátor nám vrací pravdivostní hodnotu TRUE nebo FALSE na základě toho, zdali vnořený SQL dotaz vrací nějakou hodnotu. Syntaxe se od předchozích operátorů se trochu liší:

EXISTS ( vnořený SQL dotaz )

To znamená, že jej neaplikujeme na žádný sloupec, ani hodnotu. Pakliže vnořený SQL dotaz vrátí alespoň jeden řádek, operátor EXISTS nám vrátí TRUE. Má ještě jednu zvláštnost. Vnořený SQL dotaz v argumentu operátoru EXISTS se vyhodnocuje zvlášť pro každý řádek. Proto je potřeba uvážit použití operátoru EXISTS, neboť při špatně navržených dotazech by mohlo dojít k relativně velkému zatížení SQL serveru.

Zatímco u ostatních operátorů pro vnořené SQL dotazy jsme nebyli nuceni používat aliasy na jména tabulek, u operátoru EXISTS to bude téměř nutností. To vyplývá z toho, že vnořený dotaz se vyhodnocuje pro každý řádek zvlášť a tam se nám nesmí dostat do kolize dva tytéž názvy tabulek. Ale dost bylo teorie, pojďme se podívat na nějaké příklady.

Základní použití operátoru EXISTS

Vezměme si náš databázový systém knihovny z 5. dílu a představme si následující situaci. Záznamy o knihách nám do systému zadávají naše sekretářky a my jako vedoucí si chceme zkontrolovat, zdali u každé knihy jsou zadáni autoři. Chceme tedy dotaz na seznam knih, u kterých nemáme zadaného žádného autora:

SELECT název
FROM kniha
WHERE NOT EXISTS (
        SELECT autor_id
        FROM napsané_knihy
        WHERE kniha.id = napsané_knihy.publikace_id
      )

Všimněte si, že jsem v příkladu nepoužil aliasů tabulek – jsou samozřejmě zbytečné, neboť ke kolizi jmen tabulek nám zde nedochází. Pár slov k tomuto příkladu, jak pracuje. Zaprvé je nutné si uvědomit, jakou vypovídací schopnost má tabulka NAPSANÉ_KNIHY. V našem případě velmi klíčovou, neboť existence alespoň jednoho záznamu v této tabulce nám potvrzuje, že k dané knize je přiřazen alespoň jeden autor. To znamená, že pokud sekretářka do našeho systému zapomene uvést autora k dané knize, tak se to projeví neexistencí záznamu v tabulce NAPSANÉ_KNIHY pro hodnotu KNIHA_ID dané knihy.

Vnější SELECT by nám vrátil všechny řádky z tabulky KNIHA. Než je nám ale každý řádek vrácen, operátorem EXISTS si server zjistí, zdali ke konkrétnímu řádku (resp. ID knihy) existuje záznam v tabulce NAPSANÉ_KNIHY. Pokud ne, vnořený SELECT nevrátí žádnou hodnotu a tedy EXISTS se vyhodnotí na FALSE, následne NOT EXISTS na TRUE a tento řádek je nám ve výpisu vrácen.

Příklad 1

Vzpomínáte si na příklad v 8. díle, kde jsem měli dotaz: „vypište seznam knih, kteří napsali autoři Jan Novák a Petr Nový“? V závorce jsem uváděl větu „a možná někdo další, ale to už nás nezajímá“. Nyní máme situaci jinou. Chceme dotaz na seznam knih, kteří napsali právě autoři Jan Novák a Petr Nový. Připomeňme si, jak vypadal náš dotaz, když jsme uvážili, že nějakou knihu mohli kromě těchto dvou, napsat ještě další autoři:

SELECT název
FROM kniha, napsané_knihy, autoři
WHERE kniha.id = napsané_knihy.kniha_id
  AND napsané_knihy.autor_id = autoři.id
  AND jméno = ‚Jan‘ AND příjmení = ‚Novák‘
INTERSECT
SELECT název
FROM kniha, napsané_knihy, autoři
WHERE kniha.id = napsané_knihy.kniha_id
   AND napsané_knihy.autor_id = autoři.id
   AND jméno = ‚Petr‘ AND příjmení = ‚Nový‘

Zápis SQL dotazu jsem uvedl z důvodu následného srovnání. Co znamená slovní vyjádření „právě autoři Jan Novák a Petr Nový“. Vyjděme z uvedeného dotazu. V řeči databáze to znamená, že budeme opět dělat průnik, kde nejprve sesbíráme knihy autora Jana Nováka a pak knihy Petra Nového, ale v každé větvi tohoto složeného SQL dotazu budeme testovat podmínku, že počet autorů je roven právě dvěma. V průniku nám pak zůstanou pouze ty knihy, které napsali tito dva zmínění autoři a nikdo jiný. SQL dotaz vypadá tedy následovně:

SELECT název
FROM kniha, napsané_knihy, autoři
WHERE kniha.id = napsané_knihy.publikace_id
  AND napsané_knihy.autor_id = autoři.id
  AND jméno = ‚Jan‘ AND příjmení = ‚Novák‘
  AND EXISTS (
        SELECT 1
        FROM napsané_knihy nk
        WHERE napsané_knihy.publikace_id = nk.publikace_id
        GROUP BY nk.publikace_id
        HAVING COUNT(*) = 2
      )
INTERSECT
SELECT název
FROM kniha, napsané_knihy, autoři
WHERE kniha.id = napsané_knihy.publikace_id
   AND napsané_knihy.autor_id = autoři.id
   AND jméno = ‚Petr‘ AND příjmení = ‚Nový‘
   AND EXISTS (
         SELECT 1
         FROM napsané_knihy nk
         WHERE napsané_knihy.publikace_id = nk.publikace_id
         GROUP BY nk.publikace_id
         HAVING COUNT(*) = 2
       )

Pěkné, že? Tento dotaz je značně netriviální, neboť jsme zde využili skládání a vnořování dotazů, spolu s agregačními funkcemi. Možná se ptáte, co znamená SELECT 1. Protože se operátorem EXISTS ptáme pouze na existenci alespoň jednoho záznamu, je jedno, jaké konkrétní hodnoty nám vnořený SELECT vrací. A tedy, pokud má něco vracet, nechť je to číslo 1. Komu by tam jednička vadila, může uvést libovolný sloupec z tabulek, které jsou v subdotazu uvedeny, v našem případě např. PUBLIKACE_ID.

Příklad 2

Na závěr uvedu ještě jeden příklad, už bez podrobnějších komentářů, k samostatnému prostudování.

Vypište jména autorů, kteří samostatně (bez pomocí jiných) napsali alespoň 5 knih:

SELECT jméno, příjmení
FROM autoři, napsané_knihy
WHERE napsané_knihy.autor_id = autoři.id
  AND NOT EXISTS (
        SELECT 1
        FROM napsané_knihy x
        WHERE x.publikace_id = napsané_knihy.publikace_id
          AND x.autor_id != napsané_knihy.autor_id
      )
GROUP BY jméno, příjmení
HAVING COUNT(*) >= 5

Zadání příkladů – cvičení

Pro ty, kteří by si chtěli procvičit sestavování složitějších SQL dotazů, je určeno následující zadání několika příkladů.

a) Vypište seznam knih, které napsal pan Horáček samostatně (tj. ne jako spoluautor).
b) Vypište seznam knih, které byly vydány v devadesátých letech, a to alespoň třemi různými nakladatelstvími.
c) Formulujte slovní dotaz, na nějž odpověď obdržíme následujícím SELECTem:

SELECT jméno, příjmení
FROM napsané_knihy, autoři
WHERE napsané_knihy.autor_id = autoři.id
  AND NOT EXISTS (
        SELECT 1
        FROM napsané_knihy x, napsané_knihy y
        WHERE x.autor_id = napsané_knihy.autor_id
          AND x.publikace_id = y.publikace_id
        GROUP BY y.publikace_id
        HAVING COUNT(*) = 1
      )

Jejich řešení se dozvíte na začátku příštího dílu. Mezitím mi ovšem můžete svá řešení posílat e-mailem a já je budu s vámi konzultovat. (Použité tabulky jsou definovány v 5. díle seriálu o SQL.)