Jak je to s těmi poddotazy v MySQL? Umí je nebo neumí?
24.6.2005 07:00 | Petr Zajíc | přečteno 44469×
Minule jsem poprvé v tomto seriálu použil techniku, které se v odborné terminologii říká poddotaz. K poddotazům se váže poměrně oblíbený mýtus, a to sice ten, že je MySQL neumí. Tábor zastánců MySQL pak tvrdil, že poddotazy se dají při programování obejít, tábor odpůrců této databáze se zase naopak nechal slyšet, že to nejde a že MySQL je tím pádem naprosto nepoužitelná databáze. Dnes si ukážeme, jak je to doopravdy - co poddotazy jsou, k čemu slouží a jak je v MySQL efektivně používat.
Termínem "poddotaz" je v SQL míněn platný "vnitřní" dotaz, který je součástí nějakého většího, "vnějšího" dotazu. Anglicky se označuje pojmem subquery, a můžeme rovněž slýchat označení vnořený dotaz. Zavzpomínáme-li na minulý díl, byl tam uveden tento poddotaz (v textu příkazu SQL jsem jej označil červeně).
select oddeleni,
avg(plat) from pracovnik group by oddeleni having avg(plat)>(select avg(plat) from pracovnik);
Jak vlastně vzniklo tvrzení, že MySQL nepodporuje poddotazy? Obyčejná databáze skutečně poddotazy nepodporuje, tak tomu skutečně dřív bývalo, že ani MySQL poddotazy neměla. Ale pak se pár chytrých hlav dalo dohromady a řekli: Dost! A není náhodou, že těch pár chytrých hlav se sešlo v MySQL! Víte, co to znamená? To znamená, že jsme zase o krok před nimi!
Takže teď vážně: MySQL podporuje poddotazy od verze 4.1 (což je podle některých správců "poměrně nedávno" a tuto verzi na svých systémech ještě nemusejí mít). Pro některé čtenáře to tedy bude spíše pohled do budoucnosti, než že by mohli poddotazy hned začít využívat. Dřívější verze MySQL (což zahrnuje řady 4.0 a všechny "trojkové") práci s poddotazy v plném slova smyslu skutečně neuměly.
Poddotazy mají některé výhody, o nichž byste měli vědět (a díky nimž se používají). Mezi nejvýznamnější z nich patří:
Každá mince má dvě strany, a proto i poddotazy mají svoje nevýhody. Podotýkám, že tyto nevýhody se týkají hlavně špatně napsaných poddotazů, ale měli byste o nich vědět i v případě, že se chystáte psát dobré poddotazy.
Typické použití poddotazů je ve spojení s predikáty BETWEEN a IN. Ty byly již zmíněny v díle o filtrování dat. Zejména predikát IN je častým "terčem" poddotazů. Funguje to následovně: nejprve připomenu příklad z dílu o filtrování dat:
select * from lidi where
mesto in ('Praha', 'Brno', 'Ostrava');
Všimněte si, že v tomto příkladu žádný poddotaz není a že hodnoty měst jsou zadány "natvrdo". Pokud bychom seznam měst měli uložen v samostatné tabulce, můžeme dotaz přepsat na:
select * from lidi where
mesto in (select mesto from mesta);
Výhoda je jasná - jestliže se změní tabulka měst, druhý dotaz bude i nadále fungovat. Ten první by se ale musel po každé změně seznamu měst přepsat. V poddotazu můžeme používat prakticky všechny věci, které jsme se naučili u příkazu SELECT. Nic nám tedy nebrání vybrat jen lidi z měst nad 100000 obyvatel:
select * from lidi where
mesto in (select mesto from mesta where obyvatel > 100000);
Jednu věc byste ale vědět měli - poddotaz použitý v souvislosti s predikátem IN musí vracet jen jeden sloupec. Takže, následující syntaxe je špatná, protože poddotaz vrací dva sloupce a nebohý hlavní dotaz netuší, který z nich má pro porovnání použít:
select * from lidi where
mesto in (select mesto, poloha from mesta);
Poddotazy lze používat mnoha dalšími způsoby. V souvislosti s tím mě napadá, že je třeba zmínit se o speciálním operátoru - EXISTS.
EXISTS funguje tak, že vrátí logickou hodnotu TRUE, pokud následující poddotaz obsahuje nějaké řádky. Pokud poddotaz žádné řádky nevrátí, EXISTS vrací FALSE. Vše bude asi nejlepší osvětlit na nějakém příkladu. Dejme tomu, že chceme vybrat všechna města, v nichž máme nějaké záznamy o obyvatelích:
select * from mesta
where exists (select * from lidi where lidi.mesto=mesta.mesto);
Česky řečeno vrátí tento dotaz jen taková města, v nichž máme nějaké obyvatele. Na tomto typu poddotazu si můžete všimnout zajímavé věci - a to sice že poddotaz se "odvolává" na data v hlavím dotazu. To je možné. Ve skutečnosti je to pro funkci EXISTS naprosto typické. Poddotazům tohoto typu se říká korelační (anglicky correlated).
Jak jsme v příkladech viděli, poddotaz může být v klauzuli HAVING a v klauzuli WHERE. Ve skutečnosti může být v příkazu prakticky kdekoli. Poddotaz může být dokonce i v jiném poddotazu! S poddotazy se budeme v dalších dílech seriálu sektávat průběžně, takže se s nejobvyklejšími konstrukcemi poddotazů ještě seznámíte.
V souvislosti s poddotazy byste měli vědět, že některé z nich lze přepsat na spojení. Spojení bývá obecně rychlejší. Následující dva dotazy jsou tedy funkčně totožné, jeden však používá poddotaz a druhý spojení.
select * from lidi where
mesto in (select mesto from mesta);
select lidi.* from lidi join mesta on lidi.mesto= mesta.mesto;
V dobách, kdy MySQL nepodporovala poddotazy byl přepis na spojení jedinou možností, jak se s podobnými úlohami vypořádat. Nezapomeňme však, že některé poddotazy na spojení zkrátka přepsat nejdou, a tudíž je lze smysluplně provést jen na verzích MySQL 4.1 a novějších. Pěkný rozbor této problematiky je v manuálu k MySQL.