LINUXSOFT.cz Přeskoč levou lištu

ARCHIV



   

> 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 44838×

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ů

28.11.2018 23:56 /František Kučera
Prosincový sraz spolku OpenAlt se koná ve středu 5.12.2018 od 16:00 na adrese Zikova 1903/4, Praha 6. Tentokrát navštívíme organizaci CESNET. Na programu jsou dvě přednášky: Distribuované úložiště Ceph (Michal Strnad) a Plně šifrovaný disk na moderním systému (Ondřej Caletka). Následně se přesuneme do některé z nedalekých restaurací, kde budeme pokračovat v diskusi.
Komentářů: 1

12.11.2018 21:28 /Redakce Linuxsoft.cz
22. listopadu 2018 se koná v Praze na Karlově náměstí již pátý ročník konference s tématem Datová centra pro business, která nabídne odpovědi na aktuální a často řešené otázky: Jaké jsou aktuální trendy v oblasti datových center a jak je optimálně využít pro vlastní prospěch? Jak si zajistit odpovídající služby datových center? Podle jakých kritérií vybírat dodavatele služeb? Jak volit vhodné součásti infrastruktury při budování či rozšiřování vlastního datového centra? Jak efektivně datové centrum spravovat? Jak co nejlépe eliminovat možná rizika? apod. Příznivci LinuxSoftu mohou při registraci uplatnit kód LIN350, který jim přinese zvýhodněné vstupné s 50% slevou.
Přidat komentář

6.11.2018 2:04 /František Kučera
Říjnový pražský sraz spolku OpenAlt se koná v listopadu – již tento čtvrtek – 8. 11. 2018 od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5). Tentokrát bez oficiální přednášky, ale zato s dobrým jídlem a pivem – volná diskuse na téma umění a technologie, IoT, CNC, svobodný software, hardware a další hračky.
Přidat komentář

4.10.2018 21:30 /Ondřej Čečák
LinuxDays 2018 již tento víkend, registrace je otevřená.
Přidat komentář

18.9.2018 23:30 /František Kučera
Zářijový pražský sraz spolku OpenAlt se koná již tento čtvrtek – 20. 9. 2018 od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5). Tentokrát bez oficiální přednášky, ale zato s dobrým jídlem a pivem – volná diskuse na téma IoT, CNC, svobodný software, hardware a další hračky.
Přidat komentář

9.9.2018 14:15 /Redakce Linuxsoft.cz
20.9.2018 proběhne v pražském Kongresovém centru Vavruška konference Mobilní řešení pro business. Návštěvníci si vyslechnou mimo jiné přednášky na témata: Nejdůležitější aktuální trendy v oblasti mobilních technologií, správa a zabezpečení mobilních zařízení ve firmách, jak mobilně přistupovat k informačnímu systému firmy, kdy se vyplatí používat odolná mobilní zařízení nebo jak zabezpečit mobilní komunikaci.
Přidat komentář

12.8.2018 16:58 /František Kučera
Srpnový pražský sraz spolku OpenAlt se koná ve čtvrtek – 16. 8. 2018 od 19:00 v Kavárně Ideál (Sázavská 30, Praha), kde máme rezervovaný salonek. Tentokrát jsou tématem srazu databáze prezentaci svého projektu si pro nás připravil Standa Dzik. Dále bude prostor, abychom probrali nápady na využití IoT a sítě The Things Network, případně další témata.
Přidat komentář

16.7.2018 1:05 /František Kučera
Červencový pražský sraz spolku OpenAlt se koná již tento čtvrtek – 19. 7. 2018 od 18:00 v Kavárně Ideál (Sázavská 30, Praha), kde máme rezervovaný salonek. Tentokrát bude přednáška na téma: automatizační nástroj Ansible, kterou si připravil Martin Vicián.
Přidat komentář

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

> Poslední diskuze

31.7.2023 14:13 / Linda Graham
iPhone Services

30.11.2022 9:32 / Kyle McDermott
Hosting download unavailable

13.12.2018 10:57 / Jan Mareš
Re: zavináč

2.12.2018 23:56 / František Kučera
Sraz

5.10.2018 17:12 / Jakub Kuljovsky
Re: Jaký kurz a software by jste doporučili pro začínajcího kodéra?

Více ...

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