LINUXSOFT.cz Přeskoč levou lištu
Uživatel: Heslo:  
   CZUKPL

> MySQL (26) - Poddotazy

Jak je to s těmi poddotazy v MySQL? Umí je nebo neumí?

24.6.2005 07:00 | Petr Zajíc | Články autora | přečteno 38536×

Komerční sdělení: Pořádáme Kurzy MySQL

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.

Poddotaz je když...

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ří:

  • Čitelnost. Pokud luštíte existující dotaz a pokoušíte si představit, k čemu asi slouží, bude zápis s poddotazy většinou dosti názorný.
  • Strukturovanost. "Vnitřní" dotaz můžete otestovat a odladit dříve než jej učiníte součástí "vnějšího" dotazu.
  • Relativní jednoduchost. Poddotaz můžete použít leckde a jak ještě uvidíme, jsou poměrně flexibilní a přitom snadné na psaní.

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.

  • Dotaz s poddotazy může pomalý, pekelně pomalý. Uvědomte si totiž, že někdy milé databázi nezbyde nic rychlejšího, než tupě provést poddotaz, nějak si zapamatovat jeho výsledky, ty použít ve vnějším dotazu a pak vrátit data. (Normálně se MySQL pokusí dotaz před jeho provedením optimalizovat, aby byl proveden co nejrychleji. U poddotazů je velká šance, že optimalizaci nepůjde použít).
  • Dotaz s poddotazy může být někdy trochu nevyzpytatelný. Když se například zapletete s hodnotami NULL, může s tím být docela legrace.

Použití poddotazů

BETWEEN a IN

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.

Poddotazy a 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.

Poddotazy versus spojení

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.

Verze pro tisk

pridej.cz

 

DISKUZE

bohuzel, neumi 27.6.2005 10:39 Petr Aubrecht
Vnoření selecty 27.6.2005 14:50 Petr Zajíc
  L Re: Vnoření selecty 28.6.2005 21:41 Petr Aubrecht
    |- Re: Vnořen_é_ selecty 28.6.2005 21:43 Petr Aubrecht
    L Re: Vnoření selecty 10.7.2005 07:46 Petr Zajíc




Příspívat do diskuze mohou pouze registrovaní uživatelé.
> Vyhledávání software
> Vyhledávání článků

12.7.2016 13:14 /František Kučera
Spolek OpenAlt zve na 130. distribuovaný sraz příznivců svobodného softwaru a otevřených technologií (hardware, 3D tisk, SDR, DIY, makers…), který se bude konat ve čtvrtek 21. července od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5).
Přidat komentář

11.7.2016 16:53 /Redakce Linuxsoft.cz
Konference LinuxDays hledá přednášející. Přihlášky poběží do konce prázdnin, v září bude hlasování a program. Více na https://www.linuxdays.cz/2016/cfp/.
Přidat komentář

8.5.2016 17:19 /Redakce Linuxsoft.cz
PR: Dne 26.5.2016 proběhne v Praze konference Cloud computing v praxi. Tématy bude např. nejnovější trendy v oblasti cloudu a cloudových řešení, cloudové služby, infrastruktura cloudu, efektivní využití cloudu, možné nástrahy cloudů a jak se jim vyhnout
Přidat komentář

21.4.2016 8:01 /František Kučera
Spolek OpenAlt zve na 127. distribuovaný sraz příznivců svobodného softwaru a otevřených technologií (hardware, 3D tisk, SDR, DIY, makers…), který se bude konat ve čtvrtek 28. dubna od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5).
Přidat komentář

2.3.2016 22:41 /Ondřej Čečák
Letošní ročník konference InstallFest již tento víkend!
Přidat komentář

14.2.2016 16:39 /Redakce Linuxsoft.cz
O víkendu 5. a 6. března 2016 proběhne na pražském Strahově 8. ročník tradiční konference InstallFest. Celkem za dva dny uvidíte ​30 přednášek​ a ​6 workshopů.
Přidat komentář

5.2.2016 17:38 /Petr Ježek
Utilitka z XFce "xfce4-power-manager" nejen umožňuje nastavení lhůty pro uspání či hybernaci, ale i zapínání a vypínání prezentačního módu pro nerušené sledování videí. Stačí ji nastavit v každém vybavenějším panelu a v jakémkoli nontiled WM/DE.
Přidat komentář

10.1.2016 11:32 /Pavel `Goldenfish' Kysilka
LinuxMarket změnil provozovatele. Nově jej provozuje Marek Pszczolka. Více info a detaily #1 a #2.
Přidat komentář

   Více ...   Přidat zprávičku

> Poslední diskuze

10.6.2016 21:10 / pavel riha
FreeBSD 10.3 a virtualizace

8.6.2016 21:56 / Milan Gallas
Nevalidní prefix m

7.5.2016 14:58 / Teodor Komárek
Soubory

20.4.2016 0:07 / Jakub Cleing
Sázkový panel PHP FUSION

9.4.2016 9:43 / jiwopene@gmail.com
Re: problém s dpkg a nemožností instalovat

Více ...

ISSN 1801-3805 | Provozovatel: Pavel Kysilka, IČ: 72868490 (2003-2016) | mail at linuxsoft dot cz | Design: www.megadesign.cz | Textová verze