Slučování tabulek

Slučování/spojování tabulek jsem ukazoval už v kapitole SELECT I a VIEW v odstavci Spojování tabulek. V této kapitole proberu další možnosti spojování dat z více tabulek. Především pak konstrukce s JOIN, které jsou podporovány až v novějších verzích PostgreSQL.^{(Tahle věta už je trochu pasé.)}

Používání aliasů
CROSS JOIN
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
USING, NATURAL JOIN
UNION
- UNION ALL
INTERSECT, EXCEPT
MySQL
SQLite
Oracle

Používání aliasů

Aliasy jsem popisoval již v kapitole SELECT I a VIEW. Pomocí klíčového slova AS můžete vytvořit nové jméno pro sloupec v SELECTu.

Aliasy se dají použít i pro přejmenování tabulky. Hodí se to na zkrácení zápisu při používání tečkové notace, nebo při vytváření korelovaných dotazů (viz následující kapitola). Aliasem zde myslím jiný název pro tabulku. Alias pro tabulku se vytvoří přidáním nového názvu za název tabulky a AS (které je ovšem nepovinné) v klauzuli FROM:

... FROM tabulka [AS] alias_tabulky1, tabulka2 [AS] alias_tabulky2 ...

Použití AS je nepovinné, proto ilustraci jsem ho jednou použil a jednou ne.
Protože je sloupec poznamka v obou tabulkách, musela se použít tečková notace pro jejich odlišení. Sloupec id je jen v jedné tabulce, takže se tečková notace použít nemusí, ale pro ilustraci jsem jí v klauzuli WHERE použil.

Pokud nějakou tabulku přejmenujete, musíte používat všude v SELECTu nové jméno tabulky (alias).

Ušetřil jsem si trochu práce tím, že místo telefon.poznamka jsem mohl (teda už musel) psát t.poznamka. Snad to i trochu zlepšilo čitelnost příkazu, ne?

Poznámka: Všimněte si, že se v příkazu SELECT nejdříve alias použije (v tečkové notaci t.poznamka a k.poznamka) a až poté se definuje (telefon t, kontakt k).

CROSS JOIN

Spojování tabulek tím, že je vyjmenujete za FROM je, řekněme, zastaralý způsob. Pořád se hojně používá (je to pohodlné), ale SQL standard přišel s klíčovým slovem JOIN.

JOIN má několik verzí. První, kterou představím, je nepříliš používaný CROSS JOIN. Není příliš používaný, protože dělá přesně to samé, co vyjmenování tabulek za FROM – udělá kartézký součin všech řádků z tabulek, tj. spojí každou řádku z první tabulky s řádkou z druhé tabulky. Podmínkou WHERE pak můžete vybrat jen ty řádky, které vás zajímají:

Jak vidíte, jediný rozdíl oproti příkladu z odstavce o Používání aliasů je ten, že se místo čárky mezi tabulkami napsalo CROSS JOIN. A protože čárka zabere míň psaní, používá se většinou ona, namísto CROSS JOIN.

INNER JOIN

INNER JOIN („vnitřní spojení“) dělá totéž co CROSS JOIN. Má však navíc klauzuli ON, která říká, jaké řádky se mají spojit.

Když do klauzule ON napíšete stejnou podmínku, jako do klauzule WHERE z příkladu k CROSS JOIN, výsledek bude stejný. Je tu jen drobný rozdíl v teorii:

CROSS JOIN nejdříve spojí všechno se vším a pak pomocí podmínky WHERE profiltruje jen vyhovující řádky.
Naproti tomu INNER JOIN spojí jen to, co vyhovuje podmínce v ON.

Interně jsou ovšem DBMS optimalizované, takže CROSS JOIN ve skutečnosti netahá všechna data z databáze, aby je pak profiltroval. Rozdíl mezi CROSS JOIN + WHERE a INNER JOIN + ON není fakticky žádný.

Jediná výhoda INNER JOIN + ON je sémantická: klauzulí ON říkáte, podle čeho spojujete a pak můžete přidat ještě WHERE na odfiltrování záznamů. U CROSS JOIN byste všechno psali do WHERE a o tuto sémantiku byste přišli:

rimmer1=> SELECT prijmeni, jmeno, telefon, t.poznamka AS "O telefonu",
k.poznamka AS "O kontaktu"
FROM telefon t CROSS JOIN kontakt k
WHERE kontakt_id = k.id AND prijmeni = 'Drevokocur' AND jmeno = 'Lukas';
prijmeni | jmeno | telefon | O telefonu | O kontaktu
------------+-------+---------+--------------+------------
Drevokocur | Lukas | 555555 | |
Drevokocur | Lukas | 555555 | Telefon domu |
(2 řádky)

rimmer1=> SELECT prijmeni, jmeno, telefon, t.poznamka AS "O telefonu",
k.poznamka AS "O kontaktu"
FROM telefon t INNER JOIN kontakt k
ON kontakt_id = k.id
WHERE prijmeni = 'Drevokocur' AND jmeno = 'Lukas';
prijmeni | jmeno | telefon | O telefonu | O kontaktu
------------+-------+---------+--------------+------------
Drevokocur | Lukas | 555555 | |
Drevokocur | Lukas | 555555 | Telefon domu |
(2 řádky)

rimmer1=> SELECT prijmeni, jmeno, telefon, t.poznamka AS "O telefonu",
k.poznamka AS "O kontaktu"
FROM telefon t CROSS JOIN kontakt k
ON kontakt_id = k.id
WHERE prijmeni = 'Drevokocur' and jmeno = 'Lukas';
ERROR: syntax error at or near "ON"
ŘÁDKA 3: ON kontakt_id = k.id WHERE prijmeni = 'Drevokocur' and jmeno...

Už vás asi nepřekvapí, že výsledek prvních dvou SELECTů je stejný. V příkladu s INNER JOIN je jen trochu čitelnější, která podmínka slouží ke spojení řádků a která pro vyfiltrování výsledku.

Třetí SELECT skončil chybou, protože CROSS JOIN nemá klauzuli ON.

Klíčové slovo INNER je nepovinné. Pokud napíšete jen JOIN, DBMS to bude chápat jako INNER JOIN.

LEFT OUTER JOIN

Jestli jsem vás ještě nepřesvědčil o důležitosti ON, tak teď vám ukážu jeho praktický význam.

LEFT OUTER JOIN vybere úplně všechny záznamy z první tabulky, a k nim připojí druhou tabulku podle podmínky ON.

Jinak řečeno, z první (levé) tabulky uvidíte všechny záznamy (v příkladě to budou všechny kontakty), ať už k nim existuje odpovídající záznam z druhé (pravé) tabulky nebo ne. Tam, kde k záznamu levé tabulky existuje záznam z pravé tabulky (v našem případě telefon) bude tento záznam připojen, jinak bude ve sloupcích pro druhou tabulku NULL.

Klíčové slovo OUTER je nepovinné.

Všiměte si, že z tabulky kontaktů teď vidíte všechny záznamy. Jeden záznam vidíte 2x (Lukas Drevokocur), protože k němu existují 2 telefony.

Kdybyste teď chtěli vyfiltrovat jen kontakty s příjemním Drevokocur, museli byste použít podmínku WHERE. Podmínka ON totiž nevyfiltruje z levé tabulky (tabulky kontaktů) vůbec nic! A to je ten podstatný rozdíl mezi ON a WHERE.

Zatímco u INNER JOIN ON filtruje z obou tabulek, takže oproti WHERE žádný faktický rozdíl.

RIGHT OUTER JOIN

RIGHT OUTER JOIN je totéž co LEFT OUTER JOIN, jen podmínka ON nevyfiltruje nic z pravé tabulky. (Respektive, psal jsem přece že ON se používá na spojování, takže, přesněji řečeno, RIGHT OUTER JOIN vypíše vše z pravé tabulky, plus připojí všechny existující záznamy z levé tabulky odpovídající podmínce ON).

Klíčové slovo OUTER je nepovinné.

Ve výsledku jsou všechny záznamy z pravé tabulky (z telefonů) a všechny záznamy z kontaktů odpovídající podmínce ON.

A ano, předchozí příklad by se dal přepsat pomocí LEFT OUTER JOIN se stejným výsledkem, stačí jen přehodit pořadí tabulek:

Ještě jeden příklad na závěr: jak zjistit, ke kterým telefonům nemáte žádný kontakt? Využiji toho, že spojuji tabulku telefon přes sloupec kontakt_id. Když bude ve výsledku tento sloupec NULL, tak je jasené, že není s ničím spojen:

FULL OUTER JOIN

FULL OUTER JOIN vybere všechny záznamy z obou tabulek. Kde si záznamy odpovídají (odpovídají podmínce ON), budou spojeny v jeden řádek. Pokud pro řádek neexistuje odpovídající řádek v jedné či druhé tabulce, bude nakonec vypsán a hodnoty z druhé tabulky nahrazeny hodnotou NULL.

V případě tabulek kontakt a telefon znamená, že ve výsledku uvidíte všechna jména (i ty, u kterých není telefonní číslo) i všechna telefonní čísla (i ty, ke kterým nepatří žádné jméno).

Klíčové slovo OUTER je nepovinné.

USING, NATURAL JOIN

Jen telegraficky zmíním, že místo ON můžete použít USING, pokud používáte podmínku rovnosti nad sloupci stejného jména.

Prostě to znamená, že místo ON table1.sloupec = table2.sloupec můžete psát USING(sloupec).

To bych ale musel mít jinou politiku pojmenovávání primárních a cizích klíčů, aby se mi USING hodilo.
Třeba v tabulce kontakt bych musel mít místo id kontakt_id a pak bych mohl použít kontakt JOIN telefon USING(kontakt_id).

NATURAL JOIN je ještě větší zkratka. NATURAL JOIN je vlastně INNER JOIN, který se spojuje pomocí sloupců stejného jména z obou tabulek.

Kdybych měl v tabulce kontakt místo id kontakt_id, pak by následující tři JOINy dělali to samé*:

... kontakt JOIN telefon ON kontakt.kontakt_id = telefon.kontakt_id WHERE ...
... kontakt JOIN telefon USING(kontakt_id) WHERE ...
... kontakt NATURAL JOIN telefon WHERE ...

* NATURAL JOIN je velice náchylný na změnu definice tabulek. Náhodou přidáte do tabulky sloupec stejného jména jako je v té druhé a NATURAL JOIN jej potichoučku zahrne do slučovací podmínky (na tom, jestli jde o primární a cizí klíč vůbec nesejde!). Protože tabulka telefon i kontakt mají sloupeček stejného jména (poznamka), byl by třetí JOIN z příkladu výše ve skutečnosti ekvivalentní těmto:

... kontakt JOIN telefon ON kontakt.kontakt_id = telefon.kontakt_id AND kontakt.poznamka = telefon.poznamka WHERE ...
... kontakt JOIN telefon USING(kontakt_id, poznamka) WHERE ...

Traduje se, že je z tohoto důvodu v některých zemích použití NATURAL JOIN trestán smrtí :-). Já vám tedy rozhodně doporučuji NATURAL JOIN nepoužívat nikdy, nikde a za nic. Ani pod dohledem profesionálů.

UNION

UNION (sjednocení) slouží ke spojování tabulek „nad sebe“. Tabulky, které chcete nad sebe spojit, musí mít stejný počet sloupců a sloupce musí mít stejný datový typ (nebo alespoň „kompatibilní“ datový typ).

Jak UNION funguje? Napíšete libovolný SELECT, pak klíčové slovo UNION a pak zase SELECT, který vrací stejný počet sloupců …

Třídění (ORDER BY) nemůže být součástí jednotlivých SELECTů v UNIONu. Setřídit můžete až celou výslednou tabulku!

Využití příkazu UNION ukážu na tabulace zamestnanci z příkladu z kapitoly Vytváření relací.

V kapitole SELECT II jsem popisoval funkce pro získávání statistických údajů. Umíte je vypsat v tabulce s jedním řádkem:

rimmer1=> SELECT 'Průměr:' AS "Funkce", AVG(plat) as "Výsledek"
FROM zamestnanci;
Funkce | Výsledek
---------+--------------------
Průměr: | 13400.000000000000
(1 řádka)

rimmer1=> SELECT 'Maximum:' AS "Funkce", MAX(plat) AS "Výsledek"
FROM zamestnanci;
Funkce | Výsledek
----------+----------
Maximum: | 22000
(1 řádka)

Poznámka: Všimněte si, že Průmer, jakožto konstantní hodnota textu, je v jednoduchých uvozovkách, kdežto Funkce, jakožto název sloupce, ve dvojitých. Jelikož název sloupce neobsahuje mezeru, jsou zde dvojité uvozovky zbytečné, v případě hodnoty, která bude ve sloupci, jsou (jednoduché) uvozovky povinné. To jen tak na připomenutí.

Určitě pro vás nebude problém tyto hodnoty vypsat jedním SELECTem v jednom řádku. Ale co kyž je chcete vypsat jedním příkazem pod sebou? Na to je tu UNION.

Pomocí UNION můžete spojit předchozí dvě tabulky vytvořené příkazy SELECT do jedné, neboť mají stejný počet sloupců (dva) stejných datových typů (řetězec a číslo).

rimmer1=> SELECT 'Průměr:' AS "Funkce", AVG(plat) as "Výsledek"
FROM zamestnanci
UNION
SELECT 'Maximum:' AS "Funkce2", MAX(plat) AS "Výsledek2"
FROM zamestnanci;
Funkce | Výsledek
----------+--------------------
Maximum: | 22000
Průměr: | 13400.000000000000
(2 řádky)

V druhém SELECTu jsem schválně změnil názvy sloupců, abyste viděli, že se berou v potaz názvy jen z prvního SELECTu.

Spojovat můžete kolik chcete tabulek vytvořených příkazem SELECT (nejenom tabulky se statistickými hodnotami, které jsme si ukazovali). Jediné (pochopitelné) omezení je v počtu sloupců a v nutnosti stejných datových typů těchto sloupců:

rimmer1=> SELECT 'Průměr:' AS "Funkce", AVG(plat) as "Výsledek"
FROM zamestnanci
UNION
SELECT 'Maximum:' AS "Funkce", MAX(plat) AS "Výsledek"
FROM zamestnanci
UNION
SELECT 'Minimum:', MIN(plat)
FROM zamestnanci
ORDER BY "Výsledek";
Funkce | Výsledek
----------+--------------------
Minimum: | 9000
Průměr: | 13400.000000000000
Maximum: | 22000
(3 řádky)

Pokud nepoužijete ORDER BY, seřazení řádků ve výsledků není definováno. To znamená, že může být jakékoliv. To znamená, že mohou být i proházené řádky ze SELECTů v UNIONu. Nenechte se zmýlit tím, že se vám při vašich pokusech vrací vždy nejdříve řádky z jedné tabulky a pak z té druhé. Jakmile začnete pracovat s vekým množstvím dat, do hry začnou vstupovat indexy atp., najednou se to začne chovat jinak!

Pokud chcete mít ve výsledku nejdříve řádky z jedné tabulky a pak z druhé, jde to udělat třeba takto:

Co jsem myslel tím, že musí být datové typy v sloupcích stejné, nebo kompatibilní? DBMS je musí umět převést na stejný datový typ:

rimmer1=> SELECT '2013' AS "datum" UNION SELECT 333 AS "integer";
datum
-------
333
2013
(2 řádky)
rimmer1=> SELECT '2013-12-31' AS "datum" UNION SELECT 333 AS "integer";
ERROR: invalid input syntax for integer: "2013-12-31"
ŘÁDKA 1: SELECT '2013-12-31' AS "datum" UNION SELECT 333 AS "integer"...
^

V prvním příkladu převedl DBMS text '2013' na datový typ INTEGER, v druhém příkladu už to nešlo.

Dobrá rada: Když to nejde jinak, převeďte sloupce explicitně na TEXT. K tomu slouží funkce CAST, nebo postgresovské ::.

rimmer1=> SELECT '2013-12-31' AS "text"
UNION
SELECT CAST(333 AS TEXT) AS "taky text";
text
------------
2013-12-31
333
(2 řádky)
rimmer1=> SELECT '2013-12-31' AS "text"
UNION
SELECT 333::text AS "taky text";
text
------------
2013-12-31
333
(2 řádky)

Ještě lepší rada: u uložených SQL příkazů (v nějakém programu) raději používejte přetypování vždy. Mohlo by se stát, že si to vyzkoušíte bez přetypování s daty, které postgres přetypuje (jako '2013'), ale později do tabulky vložíte data, která přetypovat nejdou ('2013-12-31') a SQL příkaz vám umře.

UNION ALL

UNION má jednu zákeřnou vlastnost, a to, že eliminuje duplicitní řádky z výsledku. To většinou není to, co chcete. Zabránit tomu se dá použítím ALL:

rimmer1=> SELECT 'A' UNION SELECT 'B' UNION SELECT 'A';
?column?
----------
B
A
(2 řádky)
rimmer1=> SELECT 'A' UNION ALL SELECT 'B' UNION SELECT 'A';
?column?
----------
B
A
(2 řádky)

rimmer1=> SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'A';
?column?
----------
A
B
A
(3 řádky)

rimmer1=> SELECT 'A' UNION SELECT 'B' UNION ALL SELECT 'A';
?column?
----------
A
B
A
(3 řádky)

Z příkladu, doufám, vše jasné :-)

Prioritu UNION (i INTERSECT a EXCEPT, viz níže) můžete určit závorkami:

rimmer1=> SELECT 'A' UNION (SELECT 'B' UNION ALL SELECT 'A');
?column?
----------
B
A
(2 řádky)

Používejte UNION s ALL, pokud nemáte dobrý důvod k eliminaci duplicitních řádků.

INTERSECT, EXCEPT

INTERSECT [ALL] (průnik) funguje podobně jako UNION, ale vrací jen řádky, které jsou v obou tabulkách. Bez ALL eliminuje duplicitní řádky. (Řádky jsou stejné, pokud mají všechny sloupečky stejnou hodnotu).

EXCEPT [ALL] (rozdíl) vrátí všechny řádky z první tabulky, kromě těch, které se vyskytují v druhé tabulce.

Tabulkami myslím výsledky SELECTů v INTERSECTu nebo EXCEPTu.

MySQL

MySQL implementuje CROSS JOIN jako synonymum k INNER JOIN, takže jej můžete používat s ON.

MySQL neumí FULL [OUTER] JOIN. Můžete ho nasimulovat pomocí UNION. (Tentokrát se zrovna hodí UNION bez ALL).

MySQL je trochu chytřejší při konverzi datových typů, takže tento SQL dotaz projde, protože se hodnoty zkonvertují automaticky na TEXT.

SELECT '2013' AS "datum" FROM dual UNION SELECT 333 AS "integer" FROM dual;

U více UNIONů nemůžete jednoduše určovat prioritu závorkami. Můžete si ale pomoci tatko:

mysql> SELECT 'A' UNION (SELECT 'B' UNION ALL SELECT 'A');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'UNION ALL SELECT 'A')' at line 1

mysql> SELECT 'A'
UNION
SELECT * FROM (SELECT 'B' UNION ALL SELECT 'A') AS temp;
+---+
| A |
+---+
| A |
| B |
+---+
2 rows in set (0.00 sec)

SELECT * FROM je SELECT ze SELECTu, který musí být pojmenován, proto to AS temp na konci.

MySQL neumí INTERSECT [ALL] ani EXCEPT [ALL].

SQLite

SQLite, stejně jako MySQL, implementuje CROSS JOIN jako synonymum k INNER JOIN.

SQLite nepodporuje RIGHT [OUTER] JOIN. Ale už víte, že se dá snadno nahradit pomocí LEFT [OUTER] JOIN.

SQLite nepodporuje ani FULL [OUTER] JOIN. Můžete ho nahradit pomocí UNION a dvou LEFT JOIN.

SELECT jmeno, prijmeni, predvolba, telefon
FROM telefon LEFT JOIN kontakt ON kontakt_id = kontakt.id
UNION
SELECT jmeno, prijmeni, predvolba, telefon
FROM kontakt LEFT JOIN telefon ON kontakt_id = kontakt.id;

V prvním LEFT JOINu je telefon nalevo a kontakt napravo, v druhém LEFT JOINu je to naopak.

SQLite je stejně chytrý jako MySQL při konverzi datových typů v UNIONu. Takže tento příklad funguje:

SELECT '2013' AS "datum" FROM dual UNION SELECT 333 AS "integer" FROM dual;

Stejně jako v MySQL nemůžete prioritu UNION určit jednoduše závorkami, ale musíte si pomoci SELECTem ze SELECTu.

SQLite podporuje INTERSECT a EXCEPT, ale obojí bez ALL.

Oracle

V Oracle nemůžete používat AS pro vytvoření aliasu tabulky. Bez AS to funguje:

SELECT prijmeni, jmeno, telefon, t.poznamka AS "O telefonu",
k.poznamka AS "O kontaktu" FROM telefon t, kontakt k
WHERE kontakt_id = k.id;

SELECT prijmeni, jmeno, telefon, t.poznamka AS "O telefonu",
k.poznamka AS "O kontaktu" FROM telefon t CROSS JOIN kontakt k
WHERE kontakt_id = k.id;

Další drobný problém je s tím, že Oracle převádí automaticky identifikátory sloupců na velká písmena. Takže se musí používat uvozovky, když je to potřeba:

SELECT prijmeni, jmeno, 1 as "tab" FROM kontakt
UNION
SELECT prijmeni, jmeno, 2 as "tab" FROM zamestnanci
ORDER BY "tab", prijmeni, jmeno;

"tab" musí být v uvozovkách i v ORDER BY, protože je v uvozovkách v prvním SELECTu.

Všechny SELECTy, které jsem v této lekci používal bez FROM, musí mít v Oracle FROM (nejlépe z tabulky dual).

Oracle je v automatickém převodu datových typů v UNIONu nejhloupější, takže nefunguje ani jeden z příkladů bez CAST. Takhle to funguje:

SELECT '2013-12-31' AS "datum" FROM dual
UNION
SELECT CAST(333 AS VARCHAR2(20)) AS "text" FROM dual;

SELECT '2013-12-31' AS "text" FROM dual
UNION
SELECT CAST(333 AS VARCHAR2(20)) AS "taky text" FROM dual;

V Oracle můžete použít závorky k určení priority UNIONů:

SELECT 'A' FROM dual
UNION
(SELECT 'B' FROM dual UNION ALL SELECT 'A' FROM dual);

Oracle podporuje INTERSECT (bez ALL). Namísto EXCEPT používá MINUS (dělá to to samé, jen si to jinak říká).