SELECT IV - poddotazy

Tato kapitola bude trochu náročnější mozkové cvičení. Proberu zde poddotazy a korelované dotazy, které se vám budou v praxi hodit častěji, než by se mohlo zdát.

Protože je toto poslední kapitola ze základního kurzu o SQL, využiju teď chvilku k tomu, abych udělal reklamu svým dalším tutoriálům. Například byste se mohli podívat na programovací jazyk Python, který umožňuje pomocí modulu psycopg2 s databází PostgreSQL komunikovat. Pokud nevíte o programování zhola nic, pak to budete mít trochu těžší. Programování je vysvětleno od úplných začátků v článku o programovacím jazyku C.

Konec reklamního bloku. A teď zpět k Postgresu :-)

Poddotazy

Poddotazem se myslí příkaz SELECT, jehož výsledek se použije buď jako tabulka pro další SELECT v rámci jednoho SQL dotazu, nebo se vrácená hodnota (hodnoty) použíjí třeba v podmínce WHERE.

Podívejte se nejdříve na využítí SELECTu vracejícího jednu hodnotu. Takovým příkazem bude typicky nějaká statistická hodnota, například průměrný plat. Pokud vás například budou zajímat všichni zaměstnanci s průměrným a nadprůměrným platem, docílíte toho následujícím poddotazem (tabulku zamestnanci jsem vytvořil v kapitole Vytváření relací). Pro zajímavost jsem výsledek setřídil sestupně dle výše platu.

rimmer1=> SELECT * FROM zamestnanci
WHERE plat >= (SELECT AVG(plat) FROM zamestnanci)
ORDER BY plat DESC;
 jmeno | prijmeni | plat  | oddeleni_id | rodne_cislo
-------+----------+-------+-------------+-------------
 Tomas | Mann     | 22000 |           3 | 8001010608
 Vasek | Trn      | 16000 |           3 | 8001010609
 Lenka | Pavova   | 15000 |           2 | 8001010604
 Tom   | Jerry    | 15000 |           2 | 8001010605
(4 řádky)

Poddotaz musí být v závorkách a musí vrátit jen jednu hodnotu (jeden řádek s jedním sloupcem), jinak by tato podmínka nedávala smysl.

Teď řekněme, že mě zajímá průmerný plat z předchozího výsledku. Jednou z možností by bylo udělat z předchozího SELECTu pohled (VIEW) a nad ním zavolat AVG. Nebo se s vytvářením pohledu nebudu zdržovat a udělám rovnou SELECT z předchozího SELECTu.

rimmer1=> SELECT AVG(xxx.plat) FROM (
    SELECT * FROM zamestnanci
    WHERE plat >= (SELECT AVG(plat) FROM zamestnanci)
    ORDER BY plat DESC
) xxx;
        avg        
--------------------
 17000.000000000000
(1 řádka)

Všiměte si, že jsem vnitřní SELECT pojmenoval xxx. Respektive jsem tak pojmenoval výsledek vnitřního SELECTu, kterým je vždy tabulka. Takže jsem „stvořil“ tabulku xxx, ze které jsem provedl SELECT AVG(xxx.plat)

Pokud používám takto poddotaz jako tabulku, vždycky musí být pojmenována.
Použití tečkové notace s názvem tabulky (xxx.plat) už v příkladu nebylo nezbytné, použil jsem ho jen pro příklad.

SELECTů jako tabulek můžete v jednom příkazu použít více, můžete je pak JOINovat a dělat s nimi cokoliv, co s pohledem (VIEW). Poddotaz není vlastně nic jiného, než ad-hoc VIEW.

Porovnávání hodnoty s polem hodnot

V následujících odstavcích popíšu operátory, které se používají k porovnávání s polem hodnot. Můžete je používat s poddotazy i bez nich, ale teprve s poddotazy oceníte jejích sílu :-)

IN

IN je operátor, který říká, zda je proměnná v nějaké množině hodnot nebo ne (alespoň jednou), a používá se v podmínce WHERE (stejně jako třeba = atp.). Množinou můžou být hodnoty oddělené čárkou uzavřené v závorkách, nebo výsledek poddotazu > 1 sloupcec s libovolným počtem řádků.

Například budu chtít zjistit zaměstnance, kteří pracují ve všech právních odděleních.

Můžu na to jít takto:

rimmer1=> SELECT prim_klic FROM oddeleni WHERE nazev = 'Pravni oddeleni';
  prim_klic
-----------
         2
         3
(2 řádky)
rimmer1=> SELECT * FROM zamestnanci WHERE oddeleni_id IN (2, 3);
  jmeno  | prijmeni | plat  | oddeleni_id | rodne_cislo
---------+----------+-------+-------------+-------------
 Lenka   | Pavova   | 15000 |           2 | 8001010604
 Tom     | Jerry    | 15000 |           2 | 8001010605
 Martin  | Luter    | 12000 |           2 | 8001010606
 Leopold | King     | 13000 |           2 | 8001010607
 Tomas   | Mann     | 22000 |           3 | 8001010608
 Vasek   | Trn      | 16000 |           3 | 8001010609
 Stary   | Osel     |  9000 |           3 | 8001010610
(7 řádek)

Nebo rovnou takto:

rimmer1=> SELECT * FROM zamestnanci WHERE oddeleni_id IN (
    SELECT prim_klic FROM oddeleni WHERE nazev = 'Pravni oddeleni'
 );
  jmeno  | prijmeni | plat  | oddeleni_id | rodne_cislo
---------+----------+-------+-------------+-------------
 Lenka   | Pavova   | 15000 |           2 | 8001010604
 Tom     | Jerry    | 15000 |           2 | 8001010605
 Martin  | Luter    | 12000 |           2 | 8001010606
 Leopold | King     | 13000 |           2 | 8001010607
 Tomas   | Mann     | 22000 |           3 | 8001010608
 Vasek   | Trn      | 16000 |           3 | 8001010609
 Stary   | Osel     |  9000 |           3 | 8001010610
(7 řádek)

Jak vidíte, použití poddotazu vám ušetří ruční přepisování výsledku prvního SELECTu do podmínky druhého.

Opakem IN je NOT IN. NOT IN vyhoví tehdy, pokud hodnota není v množině ani jednou.

ALL

ALL spolupracuje s dalším operátorem (dle vašeho výběru), který aplikuje na celou množinu. Řádek je do výsledku zahrnut pouze tehdy, vrátí-li operátor pro všechny hodnoty v množině true.

Příklad: vypište všechny zaměstnance, kteří mají maximální plat.
Pouze maximální plat je roven nebo větší než všechny platy:

rimmer1=> SELECT * FROM zamestnanci
WHERE plat >= ALL (SELECT plat FROM zamestnanci WHERE plat IS NOT NULL);
 jmeno | prijmeni | plat  | oddeleni_id | rodne_cislo
-------+----------+-------+-------------+-------------
 Tomas | Mann     | 22000 |           3 | 8001010608
(1 řádka)

Všiměte si podmínky WHERE plat IS NOT NULL, bez které by ALL použil operátor na NULL hodnotu (která se vyskytuje v jednom řádku tabulky zamestnanci) a tím pádem by bez milosti vrátil pro každý řádek NULL. Výsledkem by bylo, že podmínka nikdy nevyhovuje. (Nic není >= NULL.)

S ALL můžete použít jakýkoliv operátor (v příkladu >=), který vrací booleovskou hodnotu (true nebo false).

Opakem ALL není výraz operátor NOT ALL (…), ale NOT výraz operator ALL (…).

SELECT * FROM zamestnanci
WHERE NOT plat >= ALL (SELECT plat FROM zamestnanci WHERE plat IS NOT NULL);
...

ANY/SOME

ANY je podobné jako ALL, jen s tím rozdílem, že vrací true, když operátor vrátí true alespoň pro jednu hodnotu z množiny.

V příkladu vyberu všechny platy, které jsou větší nebo rovny dvojnásobku nejmenšího platu. (Nejmenší plat je 9000. Takže podmínku splní každý plat který je větší nebo roven 18000).

Podmínka ANY požaduje splnění podmínky (>=) alespoň u jednoho záznamu. V příkladě tedy stačí, aby byl plat větší než dvojnásobek toho nejmenšího.

rimmer1=> SELECT * FROM zamestnanci
WHERE plat >= ANY (SELECT plat*2 FROM zamestnanci);
 jmeno | prijmeni | plat  | oddeleni_id | rodne_cislo
-------+----------+-------+-------------+-------------
 Tomas | Mann     | 22000 |           3 | 8001010608
(1 row)

Všiměte si, že ve vnořeném SELECTu není podmínka NOT NULL. ANY vrací true, když vyhoví alespoň jedna hodnota z množiny. Když jsou některé hodnoty NULL, tak sice nevyhoví, ale to nevadí.

SOME je jen synonymum pro ANY.

Operátor IN je vlastně jen synonymum pro = ANY.

rimmer1=> SELECT * FROM zamestnanci
WHERE oddeleni_id = ANY (
    SELECT prim_klic FROM oddeleni WHERE nazev = 'Pravni oddeleni'
    );
  jmeno  | prijmeni | plat  | oddeleni_id | rodne_cislo
---------+----------+-------+-------------+-------------
 Lenka   | Pavova   | 15000 |           2 | 8001010604
...
 Stary   | Osel     |  9000 |           3 | 8001010610
(7 řádek)

Porovnávání řádků

V SQL existuje něco, čemu se říká řádkový konstruktor.
Řádkový konstruktor vypadá takto: ROW(hodnota, hodnota, …)

Klíčové slovo ROW není povinné, pokud konstruujete řádek s více jak jedním sloupcem. Takže jako řádkový konstruktor slouží i jen hodnoty oddělené čárkou uzavřené do závorek.

Příklad použití:

rimmer1=> SELECT ROW('a', 3, 5.2) as priklad;
  priklad  
-----------
 (a,3,5.2)
(1 řádka)

A teď k čemu je to dobré: řádky můžete porovnávat. Porovnávají se z leva do prava po jednotlivých sloupcích (hodnotách). Aby se dva řádky rovnali, musí být všechny hodnoty stejné. Aby byl jeden řádek větší než druhý, musí mít větí první nestejnou hodnotu (z leva).

rimmer1=> SELECT ('a', 3, 5.2) = ('a', 3, 5.2) as rovnost,
('a', 3, 5.3) > ('a', 3, 5.2) as vetsi;
 rovnost | vetsi
---------+-------
 t       | t
(1 řádka)

Praktičtější využití může být toto (oba SELECTy dělají totéž):

SELECT * FROM telefon WHERE predvolba = '908' AND telefon = '300300';
SELECT * FROM telefon WHERE (predvolba, telefon) = ('908', '300300');

Zábava příjde s poddotazy. Jako příklad zkusím najít všechny telefony, které mají předvolbu a operatora jinou, než je v tabulce operator. Abych něco našel, tak si nejdřív něco takového do tabulky vložím:

INSERT INTO telefon (predvolba, telefon, operator_id) VALUES ('908','666666',2);

Teď onen SELECT s poddotazem:

rimmer1=> SELECT * FROM telefon t
WHERE (t.predvolba,t.operator_id) NOT IN (SELECT p.predvolba, p.operator_id FROM predvolba p);
 predvolba | telefon | operator_id | tarif_id | kontakt_id | poznamka
-----------+---------+-------------+----------+------------+----------
 908       | 666666  |           2 |          |            |
(1 řádka)

Předchozí SELECT se dá přepsat pomocí JOINu. Není z něho možná tak „čitelné“ o co se snažím, ale JOIN verze SELECTů bývají v praxi o dost rychlejší než SELECTy s poddotazy. (V tabulce s pár tisíci záznami rozdíl obvykle nepoznáte.)

SELECT t.* FROM telefon t
LEFT JOIN predvolba p
ON (t.predvolba, t.operator_id) = (p.predvolba, p.operator_id)
WHERE p.predvolba is null;

Pozor! Následující SQL dotaz dělá něco jiného a nevrátí žádnou řádku:

SELECT t.* FROM telefon t WHERE
t.predvolba   NOT IN (SELECT p.predvolba    FROM predvolba p) AND
t.operator_id NOT IN (SELECT p2.operator_id FROM predvolba p2);

Korelované dotazy

Při vytváření korelovaného dotazu se využívá jednak poddotazů a jednak aliasů. Korelované dotazy jsou dotazy několika SELECTů do jedné a té samé tabulky (pomocí poddotazů) v rámci jednoho SQL příkazu. Aliasy slouží k tomu, aby šlo rozeznat z jakého SELECTu (poddotazu) pochází sloupec, se kterým se pracuje.

Korelované dotazy nejsou opravdu nic jiného než poddotazy nad stejnou tabulkou a několik příkladů jste už vyděli s tabulou zamestnanci výše. Jen se navíc navzájem mezi se bou odkazují a ovlivňují tak svůj výsledek.

Pomocí korelovaného dotazu můžu například vypsat všechny zaměstnance, kteří mají maximální plat v oddělení, ve kterém pracují.

rimmer1=> SELECT prijmeni, plat, oddeleni_id
FROM zamestnanci z1
WHERE z1.plat = (
    SELECT MAX(z2.plat) FROM zamestnanci z2
    WHERE z1.oddeleni_id = z2.oddeleni_id
);
 prijmeni | plat  | oddeleni_id
----------+-------+-------------
 Mala     | 12000 |           1
 Pavova   | 15000 |           2
 Jerry    | 15000 |           2
 Mann     | 22000 |           3
(4 rows)

Podívejte se, kde je zakopaný pes. První a druhá řádka příkazu je obyčený SELECT s aliasem tabulky zamestnanci z1.

V tuto chvíli by se vybrali všechny řádky z tabulky zamestnanci (alias z1). Mě však zajímají jen ty řádky z tabulky z1, kde je maximální plat pro řádky se stejnou hodnotu v sloupci oddeleni_id.
Tato podmínka je podstatou tohoto korelovaného dotazu. Moc dobře se to pochopit nedá, že? Ale jde to :-).

Ve výsledku to funguje asi takto: Pro každý řádek z tabulky z1 se udělá vnořený SELECT nad tabulkou z2, do jehož podmínky se za z1.oddeleni_id dosadí hodnota aktuálně zpracovávaného řádku z z1, pro který se korelovaný dotaz provádí.

Teoreticky to znamená, že se pro každý řádek ze z1 provede extra poddotaz, což není zrovna nejvýkonější záležitost. V našem příkladě by si mohl optimalizátor DBMS zapamatovat výsledky pro stejná z1.oddeleni_id a nedělat stejný dotaz znovu, ale i tak je lepší se korelovaným dotazům vyhýbat (pokud je to teda možné).

Není důvod, proč si to ještě trochu nezkomplikovat. Místo oddeleni_id bude jistě lepší mít ve výsledku název oddělení. Název oddělení je uložen v tabulce oddeleni, takže se musí předcházející korelovaný dotaz propojit (JOINovat) s touto tabulkou.

rimmer1=> SELECT prijmeni, plat, nazev
FROM zamestnanci z1, oddeleni
WHERE z1.oddeleni_id = oddeleni.prim_klic AND
z1.plat = (
    SELECT max(z2.plat) FROM zamestnanci z2
    WHERE z1.oddeleni_id = z2.oddeleni_id
);
 prijmeni | plat  |      nazev      
----------+-------+-----------------
 Mala     | 12000 | Sekretariat
 Pavova   | 15000 | Pravni oddeleni
 Jerry    | 15000 | Pravni oddeleni
 Mann     | 22000 | Pravni oddeleni
(4 rows)

MySQL

MySQL zachází poněkud podivně s hodnotou NULL u operátoru ALL. Náslecující SELECT vrátí maximální plat, i když existuje řádek, kde je plat NULL.

SELECT * FROM zamestnanci WHERE plat >= ALL (SELECT plat FROM zamestnanci);

V PostgreSQL i Oracle je potřeba přidat do poddotazu podmínku plat IS NOT NULL.

MySQL neumí zacházet s řádkem jako by to byl sloupec (nemá datový typ „řádek“), takže tento příklad nefunguje:

SELECT ROW('a', 3, 5.2) as priklad;

Jinak funguje vše jako v Postgresu, včetně tohoto:

rimmer1> SELECT ('a', 3, 5.2) = ('a', 3, 5.2) as rovnost, ('a', 3, 5.3) > ('a', 3, 5.2) as vetsi;
+---------+-------+
| rovnost | vetsi |
+---------+-------+
|       1 |     1 |
+---------+-------+
1 row in set (0,00 sec)

SQLite

SQLite nemá operátory ALL a ANY/SOME.

Nemá ani konstruktor řádku (ROW), takže celou část kapitolu o něm můžte, pokud jde o SQLite, zapomenout. (Což není taková hrůza, protože víte, jak všechny SQL dotazy přepsat bez použití řádkového konstruktoru.)

Vše ostatní z této kapitoly funguje tak, jako v PostgreSQL.

Oracle

Oracle umí řádkový konstruktor tak trochu. Umí ho jen v části WHERE a jen s poddotazy. Proto toto v Oracle nefunguje:

SELECT ROW('a', 3, 5.2) as priklad FROM dual;
SELECT ('a', 3, 5.2) = ('a', 3, 5.2) as rovnost, ('a', 3, 5.3) > ('a', 3, 5.2) as vetsi;
SELECT * FROM telefon WHERE (predvolba, telefon) = ('908', '300300');

Zatímco první dva SELECTy byste museli nějak přepsat bez použití řádkového konstruktoru, ten třetí jde za použití poddotazu. Toto v Oracle funguje:

SELECT * FROM telefon WHERE (predvolba, telefon) = (SELECT '908', '300300' FROM dual);

Nicméně, asi by bylo lepší i toto přepsat bez řádkového konstruktoru (viz výše, kde je to ukázáno).

Protože funguje řádkový konstruktor jen v klauzuli WHERE, nefunguje ani toto:

SELECT t.* FROM telefon t
LEFT JOIN predvolba p
ON (t.predvolba, t.operator_id) = (p.predvolba,p.operator_id)
WHERE p.predvolba is null;

A musí se to přepsat takto:

SELECT t.* FROM telefon t
LEFT JOIN predvolba p
ON (t.predvolba = p.predvolba AND t.operator_id = p.operator_id)
WHERE p.predvolba is null;

Vše ostatní z této kapitoly funguje tak, jako v PostgreSQL.

Komentář Hlášení chyby
Created: 11.9.2005
Last updated: 10.9.2015
Tato stánka používá ke svému běhu cookies, díky kterým je možné monitorovat, co tu provádíte (ne že bych to bez cookies nezvládl). Také vás tu bude špehovat google analytics. Jestli si myslíte, že je to problém, vypněte si cookies ve vašem prohlížeči, nebo odejděte a už se nevracejte :-). Prohlížením tohoto webu souhlasíte s používáním cookies. Dozvědět se více..