SQL – skládání dotazů

2. října 2000

•

Dnešní díl budu věnovat různým typům skládání SQL dotazů. S výstupními řádky můžeme provádět sjednocení, průnik, nebo rozdíl (resp. doplněk). Řekneme si, jaké zásady při psaní skládaných dotazů musíme dodržovat a jak je to s tříděním skládaných dotazů.

Podívejme se příkaz SELECT z trochu matematického pohledu. Na každý dotaz obdržíme výstupní řádky, které vyhovují našemu dotazu. Na tyto řádky můžeme pohlížet jako na množinu. S množinami můžeme provádět základní operace: sjednocení, průnik a rozdíl. Nyní si rozšíříme syntaxi příkazu SELECT do tvaru, který jsem uváděl na začátku povídání o psaní dotazů.

‚select‘ :=
SELECT seznam sloupců
FROM seznam tabulek
[WHERE restrikce] [GROUP BY výrazy pro seskupení] [HAVING doplňující podmínky pro skupinu] [UNION ‚select‘] [INTERSECT ‚select‘] [MINUS ‚select‘] [ORDER BY dle čeho třídit]

Sjednocení dotazů

Pro sjednocení výsledků dotazů slouží klíčové slovo UNION, za kterým následuje další příkaz SELECT. Mějme následující příklad. Chceme seznam všech děl, které napsal Alois Jirásek, nebo Vítězslav Nezval. Takový dotaz můžeme zapsat i bez použití skládání dotazů, mohl by vypadat následovně:

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 = ‚Alois‘ AND příjmení = ‚Jirásek‘)
    OR
    (jméno = ‚Vítězslav‘ AND příjmení = ‚Nezval‘)
    )

Pomocí sjednocení předchozí dotaz lze přepsat následovně:

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 = ‚Alois‘ AND příjmení = ‚Jirásek‘
UNION
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 = ‚Vítězslav‘ AND příjmení = ‚Nezval‘

Oba dva zápisy nám vrátí stejnou množinu záznamů. V takovém případě doporučuji dotaz zapsat prvním způsobem bez použití skládání.

Průnik dotazů

Průnik již má větší praktické využití. Vezměme si následující příklad. Chceme názvy všech knih, které napsal Jan Novák a zároveň Petr Nový (a možná někdo další, ale to už nás nezajímá). Napíšeme následující dotaz:

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‘)
    AND
    (jméno = ‚Petr‘ AND příjmení = ‚Nový‘)
    )

Takový dotaz je samozřejmě chybný, i když by se někomu na první pohled mohlo zdát, že přesně vyhovuje zadání. Problém je v tom, že podmínky restrikce se vždy vyhodnocují v rámci jednoho řádku. V žádném řádku pseudotabulky, která vznikne přirozeným spojením uvedených tří tabulek, nemůže nastat situace, že by jméno bylo Jan a zároveň Petr. Ve skutečnosti jsou tyto dvě podmínky protichůdné a tak nám tento dotaz nikdy nedá žádnou odpověď. Řešením je použití operace průniku: (pro průnik se používá klíčové slovo 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 = ‚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ý‘

Rozdíl dotazů

Mějme nyní situaci, že bychom chtěli seznam všech knih, které napsal Jan Novák, ale zároveň které nenapsal Petr Nový. Pokud budeme vycházet ze základní úvahy, jako v předchozí první variantě, napsali bychom následující dotaz:

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‘)
    AND NOT
    (jméno = ‚Petr‘ AND příjmení = ‚Nový‘)
    )

Uvedený dotaz není úplně v pořádku. Vrátí nám všechny názvy knih, které napsal Jan Novák, ale nevezme už vůbec v úvahu, že je nesměl napsat Petr Nový. Je to opět z toho důvodu, že pokud platí že jméno je Jan, tak je jasné, že už není Petr, stejná situace je s příjmením. Podmínka za AND NOT nám v podstatě nic neříká a výsledek nijak neovlivňuje. Správným řešením v tomto případě je použití rozdílu dotazů: (pomocí klíčového slova MINUS)

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‘
MINUS
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ý‘

Doplněk

Na doplněk můžeme pohlížet v tomto případě jako na zvláštní užití rozdílu dotazů. V podstatě jde o to, že v prvním SELECTu uvedeme celou možnou množinu odpovědí a druhým SELECTem pak z výsledku vypustíme ty řádky, které nechceme. Příkladem by mohl být seznam knih, na kterých se nepodíleli Jan Novák a Petr Nový jako spoluautoři:

SELECT název
FROM kniha
MINUS
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ý‘

Tento SELECT je složením jednoho jednoduchého příkazu a jednoho složeného. Pomocí INTERSECT z těch dvou posledních SELECTů získáme názvy knih, které napsali Jan Novák a Petr Nový jako spoluautoři a pomocí MINUS pak vrátíme ty knihy, které spolu nenapsali (tj. doplněk do množiny všech existujících knih v našem systému).

Zásady pro skládání dotazů

Při skládání dotazů musíme dodržet několik zásad. První se týká vrácených sloupců. Počet projektovaných sloupců v jednotlivých SELECTech musí být stejný, a jednotlivé sloupce musí být stejného datového typu (ne tedy nutně přesně stejné názvy sloupců). Druhá zásada se týká třídění výstupů ze složených dotazů. Pokud chceme třídit výstup, klíčové slovo ORDER BY použijeme až úplně na konci zápisu složeného příkazu. Nesmíme třídit jednotlivé SELECTy. Viz následující příklad:

Poslední věc se týká vyhodnocování. Množinové operace UNION, INTERSECT a MINUS se vyhodnocují zezadu, tj. jde se odspodu, jako první se vyhodnotí poslední množinový operátor, jako poslední se vyhodnotí první uvedený. V případě nejednoznačnosti lze v některých systémech dílčí SELECTy uzávorkovat pomocí kulatých závorek.