Dotaz v dotazu

Vedoucí obchodu Pixel Shop chce vědět, které hry jsou dražší než průměrná hra. Průměr najít umíte:

SELECT AVG(cena) FROM hry;

Mohli byste tedy napsat druhý dotaz s WHERE cena > 740.5. Funguje to, ale číslo jste opsali ručně: když obchod změní nějakou cenu, změní se i průměr a váš dotaz bude potichu vracet špatnou odpověď. A zkratka, která se nabízí, nefunguje vůbec:

SELECT nazev
FROM hry
WHERE cena > AVG(cena);

Error on line 1: misuse of aggregate function AVG()

Zpráva říká, že je agregační funkce použitá špatně. WHERE rozhoduje o každém řádku zvlášť, ještě než vzniknou nějaké skupiny (vzpomeňte si, v jakém pořadí se části dotazu provádějí), takže zatím nemá žádný průměr, se kterým by mohlo porovnávat.

Řešení je dát první dotaz do závorek přesně tam, kde bylo číslo:

SELECT nazev, cena
FROM hry
WHERE cena > (SELECT AVG(cena) FROM hry)
ORDER BY cena DESC, nazev;

Dotaz v závorkách je poddotaz: dotaz uvnitř jiného dotazu. Databáze ho provede jako první, dostane jednu hodnotu, 740,5, a pak provede vnější dotaz, jako byste místo poddotazu napsali 740.5. Na rozdíl od opsaného čísla ale poddotaz spočítá průměr znovu pokaždé, když dotaz spustíte, takže výsledek vždycky sedí.

Poddotaz je úplný dotaz s vlastním SELECT a FROM a může mít i vlastní WHERE. Když vrací jedinou hodnotu (jeden řádek s jedním sloupcem), můžete ho použít všude, kde by mohlo stát číslo, text nebo datum. Tento dotaz najde poslední objednávku: poddotaz najde nejpozdější datum a vnější dotaz objednávku z toho dne:

SELECT *
FROM objednavky
WHERE datum = (SELECT MAX(datum) FROM objednavky);

Poddotaz může být i součástí výpočtu: cena - (SELECT AVG(cena) FROM hry) AS rozdil v SELECT ukáže, o kolik je každá hra dražší nebo levnější než průměr.

Typická chyba: poddotaz, který najde víc řádků tam, kde se čeká jedna hodnota. Které hry stojí stejně jako některá akční hra?

SELECT nazev, cena
FROM hry
WHERE cena = (SELECT cena FROM hry WHERE zanr = 'Akční')
ORDER BY nazev;

Akční hry jsou čtyři, za 649, 899, 799 a 999 Kč, dotaz ale našel jen hry za 649 Kč. V SQLite to není chyba: když poddotaz použitý jako hodnota najde víc řádků, SQLite potichu vezme ten první (tady cenu hry Shadow Ninja, první akční hry v tabulce) a ostatní zahodí. Jiné databáze skončí chybou. Poddotaz použitý jako hodnota by proto měl vracet právě jednu hodnotu: obvykle agregační funkci jako AVG() nebo MAX(), nebo podmínku, které odpovídá právě jeden řádek. Na celý seznam hodnot je IN a to přijde na řadu hned v dalším cvičení.