LEFT INNER JOIN
Outer join (left nebo right) je pravděpodobně nejkomplikovanější část příkazu SELECT - alespoň pro začátečníky. Jakmile tuto klauzuli zvládnou, tak v podstatě zvládli SQL. Jsou tu ještě pasti a záludnosti v podobě korelovaných poddotazů. Těm se ale lze při troše štěstí vyhnout. Zato spojování tabulek je denním chlebem skoro každého kodéra a je docela zásadní tomuto základnímu mechanismu porozumět. Článek je určen začátečníkům, nikoliv však absolutním (postačuje základní znalost SQL).
Prolog, Příkaz SELECT
Příkaz SELECT je pilířem nesoucím SQL. Je až neuvěřitelné, jaký rozsah úloh lze řešit tímto jedním příkazem. Tento příkaz obsahuje části, kterým říkáme klauzule, a kterými určujeme funkci příkazu. Při jejich zápisu záleží na pořadí. Nesmíme přehazovat klauzule uvnitř příkazu. Klauzule jsou nepovinné. Používáme pouze ty, které potřebujeme.
SELECT <seznam výrazů> -- výraz může obsahovat odkaz na sloupec tabulky FROM <zdroje> -- zdroj dat (tabulka, seznam tabulek, další SELECT) WHERE <predikát> -- zobrazí se pouze ty řádky, kde je predikát pravdivý (viz dále) GROUP BY <seznam sloupců> -- určení podmnožin pro agregační funkce
Existuje ještě několik málo dalších klauzulí (HAVING, ORDER BY, LIMIT, ...), které se však nevyskytují v žádném příkladu v tomto článku, a proto v popisu příkazu SELECT nejsou uvedené. Tyto základní klauzule jsou podrobně vysvětleny např v seriálu na pcsvetu http://www.pcsvet.cz/art/article.php?id=1380.
Normalizace
Jedním ze základních principů relačních databází je uložení údajů v samostatných tabulkách. Tzv. normální formy jsou pravidla, která popisují vlastnosti těchto tabulek. V sedmdesátých letech tou hlavní motivací pro uplatňování normálních forem byla úspora prostoru na disku (nedochází k redundantnímu uložení dat) a efektivní práce s uloženými daty (nedochází ke zbytečnému přepisování dat). Přestože hw sedmdesátých let je nesrovnatelný se současným, výše zmíněné důvody platí stále (díky náročnější byrokracii, managementu založeném na dolování a analýze dat, globalizaci). Denormalizované tabulky (to jsou tabulky, které nevyhovují pravidlům normálních forem) zpomalují zpracování SQL příkazů a signalizují nedostatečnou dekompozici dat (česky řečeno - míchají se jablka s hruškami). Stále nejrozšířenější je ukládání tabulky po řádkách a datových stránkách. Pokud je tabulka široká, tak se na datovou stránku vejde méně řádků a databáze musí přečíst více stránek (bloků) z disku. Čtení a zápis na disk jsou stále (a ještě budou) nejpomalejší operace. Pokud dotaz vyžaduje všechny sloupce tabulky, pak je to v pořádku. To však v případě širokých tabulek většinou nebývá tak úplně pravda, a tudíž zpracování příkazu není tak efektivní, jak by mohlo být, neboť se z disku načítají data, která se vůbec nepoužijí. Výjimkou jsou tzv. OLAP databáze. Tam jsou obvyklé široké tabulky. Od OLAP databází se očekává trochu něco jiného a většinou OLAP databáze i jinak ukládají data (po sloupcích).
Takže máme dost praktických důvodů proč držet data v oddělených tabulkách. Pokud ale data rozdělíme do tabulek, tak je musíme umět opět sloučit. Bez toho bychom sice měli optimálně uložená data, ale prakticky by nám k ničemu nebyla. A abychom byli schopni spojit tabulky dohromady (resp. jejich obsah), musíme vědět jak.
Primární a cizí klíče
Mírně předběhnu. Existují jednoduché a složené klíče. Jednoduché klíče jsou spojené s jedním sloupcem, složené s několika sloupci. Jiný rozdíl mezi nimi není. Pro jednoduchost budu popisovat pouze jednoduché klíče. Primární klíč je sloupec v tabulce, pomocí kterého lze jednoznačně určit každý řádek v tabulce. Cizí klíč je takový sloupec tabulky, který obsahuje pouze hodnoty, které se vyskytují v sloupci jiné (což je mnohem častější) nebo stejné tabulky, který je označen jako primární klíč. Příklad (Autor, Kniha)
Autori (id, jmeno, prijmeni) .. pk je sloupec id Knihy (id, autor_id, nazev) .. pk je sloupec id, fk (cizí klíč) je sloupec autor_id
Pokud hodnota autor_id bude např. 10, pak to bude znamenat, že knihu napsal autor s id rovno 10.
Starší relační databáze dovolovaly slučovat obsah tabulek pouze na základě vazby primárního a cizího klíče. SQL není tak striktní. Umožňuje sloučit tabulky více-méně jak se komu zachce. To má svoje výhody. Ale na druhou stranu to bývá častým zdrojem občas obtížně identifikovatelných chyb (a tedy docela záludných). Pro začátečníky je důležité si vždy ověřit, zda-li skutečně spojují tabulky na základě pk a fk. Na 99.99% je to jediná smysluplná možnost. Tady SQL neposkytuje žádnou ochranu a ani neupozorní, že je něco špatně. Je to problém i v praxi, zejména pokud máte chaoticky navržené databázové schéma bez konzistence v názvech sloupců.
Predikát
Predikát je výraz, který po dosazení proměnných má hodnotu PRAVDA nebo NEPRAVDA (true nebo false). Nejčastějším místem, kde se predikát objeví, je klauzule WHERE.
SELECT jmeno, prijmeni, vek FROM Zamestnanci WHERE vek >= 30;
Predikát vek >= 30 je pravdivý jen pro určitou podmnožinu zaměstnanců (těm co je alespoň třicet) a z této podmnožiny se pak sestaví tabulka o sloupcích jmeno, prijmeni, vek.
Predikát může obsahovat logické operátory (např. AND, OR, ...). Stále se však bude jednat o jeden predikát (vek >= 30 AND prijmeni LIKE 'S%'). Vždy se snažte zapsat predikát tak aby pro každý binární operátor byl na obou stranách pouze název sloupce nebo na jedné straně pouze název sloupce a na straně druhé konstanta. Tedy:
- správně a > b, špatně a + 1 > b + 1,
- správně a > 10, špatně a + 5 > 15
Kartézský součin
Kartézský součin je jedním ze způsobů jak sloučit dvě tabulky dohromady (další jsou sjednocení(součet), rozdíl, průnik). Výsledkem je množina dvojic obsahující všechny možné kombinace (pro dvě množiny, každý prvek z prvé s každým prvkem z druhé). Totéž lze aplikovat i na tabulky. Samozřejmě, že z této množiny dvojic řádků, má jen určitá podmnožina smysl. Tuto smysluplnou podmnožinu určuje, jak jinak, predikát. SQL automaticky počítá kartézský součin pro všechny tabulky, které se objeví v klauzuli FROM. Pokud tab. Autor bude obsahovat 5 řádků, a tabulka Kniha 10, pak výsledkem dotazu:
SELECT * FROM Autori, Knihy;
bude 50 řádků (což je ničím neomezený kartézský součin tabulek Autor a Kniha).
INNER JOIN (vnitřní spojení)
V praxi se ukázalo, že je výhodné odděleně zapisovat predikát vztahující se k spojení tabulek a predikát filtrující zobrazené záznamy. Snáze se hledají chyby (používání JOINu nejen autorovi ušetřilo čas, který by promarnil laděním chybného dotazu).
Pozn.: SQL, coby neprocedurální jazyk, neobsahuje nástroje, které by umožnily ověřit věcnou správnost sestaveného příkazu. Když je něco špatně, tak na problém často upozorní až uživatel - že se mu něco nezobrazuje, nebo naopak zobrazuje víckrát. Jednou z pomůcek, jak minimalizovat chyby, je strukturovaný zápis (pozn. autora, zde použitou formu zápisu navrhl Joe Celko).
Nejjednodušší variantou je tzv. INNER JOIN (pozn. INNER a OUTER není nutné používat a v podstatě se ani nedoporučuje používat - zbytečně prodlužují zápis). Ten odpovídá kartézskému součinu tabulek a aplikaci predikátu na tento součin.
SELECT nazev, prijmeni, jmeno
FROM Knihy
JOIN
Autori
ON Knihy.autor_id = Autori.id;
Všimněte si predikátu. Odpovídá rovnosti primárního a cizího klíče. Tento dotaz zobrazí název knihy a jméno a příjmení autora (správně vedle sebe). Pokud by nás zajímal jeden určitý autor, pak příkaz bude vypadat následovně:
SELECT nazev, prijmeni, jmeno
FROM Knihy
JOIN
Autori
ON Knihy.autor_id = Autori.id
WHERE autori.prijmeni = 'Čapek';
Všimněte si dvou predikátů. Ve starších verzích SQL (před rokem 99), kde chyběla podpora JOINu, se vše zapisovalo dohromady:
SELECT nazev, prijmeni, jmeno FROM Knihy, Autori WHERE Knihy.autor_id = Autori.id AND autori.prijmeni = 'Čapek';
V tomto případě je i tato varianta přehledná. V případě komplikovanějších dotazů se stávalo, že se pozapomnělo některou tabulku ukotvit (chyběl právě ten predikát, který se povinně zapisuje za klíčové slůvko ON), což v důsledku vedlo k enormní zátěži databázového serveru. Použití JOINu této chybě nezabrání, ale nepochybně ji minimalizuje.
Rychlost
Ohledně efektivity operace slučování tabulek koluje neuvěřitelné množství mýtů a fám. Většina programátorů používala SQL, aniž by do hloubky rozuměli technologii a docela často zaměňovali příčinu s důsledkem. Na vině byly i ne zcela dotažené a odladěné databáze.
- není žádný rozdíl ve výkonnosti mezi použitím JOINu a původním zápisem pro spojení tabulek. Pamatuji doby, kdy se JOIN nepoužíval právě z obavy o ztrátu výkonu. To je nesmysl. Liší se pouze zápis. Smysl a provádění je stejné.
- nehraje roli jaké sloupce spojujete (jestli jsou nebo nejsou primárním či cizím klíčem), ale je důležité, jestli na těchto sloupcích je index, a zda-li si typově odpovídají spojované sloupce v predikátu (jinak se musí v každém řádku provádět konverze, což má nemalou režii). Nad sloupcem primárního klíče se vždy vytváří unikátní index. Některé databáze ještě automaticky vytváří index i nad sloupci s cizími klíči. Indexy zásadně urychlují spojení tabulek. To nezůstalo bez povšimnutí programátorů. Výsledkem je fáma, že efektivita spojení závisí na referenční integritě. Nezávisí. Závisí na indexech. Indexy na cizím klíči automaticky vytváří např. Firebird (naopak PostgreSQL je nevytváří).
Databázový administrátor by neměl podlehnout pokušení indexovat každý sloupec dat v databázi. Hledá se kompromis, který je platný pro určitou aplikaci, firmu, dobu. Platí totiž, že každý index způsobí zpomalení operací INSERT, UPDATE, DELETE. Kromě vlastní změny dat se ještě musí aktualizovat všechny indexy nad tabulkou. Čím jich je více, tím to déle trvá.
Pozn.: Ještě dlouho, co už to dávno nebyla pravda, se tradovalo, že JOIN není přenositelný. RDBMS Oracle totiž, kromě JOINu, nabídla a nabízí vlastní zápis pro OUTER JOIN. Nicméně jak INNER, tak OUTER JOIN je (už skoro deset let) součástí standardu a všechny významné SQL RDBMS obsahují podporu této klauzule.
OUTER JOIN (vnější spojení)
Představte si databázi, kde máte tabulku zaměstnanců a tabulku obsahující evidenci pracovní neschopnosti zaměstnanců. Pokud Vás zajímá, který zaměstnanec byl v pracovní neschopnosti, tak použijete INNER JOIN. Ten zobrazuje spárované záznamy. Mám záznam jak v tabulce zaměstnanců, tak v tabulce pracovní neschopnosti. Pokud by Vás zajímali všichni zaměstnanci nebo jen ti, kteří byli stále zdraví (tudíž nemají záznam v tabulce pracovní neschopnosti), INNER JOIN Vám nepomůže. Prostě Vám záznamy těchto zaměstnanců nezobrazí. Příklad: Chceme zobrazit všechny zaměstnance a jejich počet hodin v pracovní neschopnosti:
Zamestanci(id, jmeno, prijmeni)
PracovniNeschopnosti(id, zam_id, den, pocet)
-- jednoduchá varianta, zobrazí záznamy pro ty, kteří mají záznam
-- v tabulce PracovniNeschopnosti
SELECT jmeno, prijmeni, pocet
FROM Zamestnanci z
JOIN (SELECT zam_id, sum(pocet) AS pocet
FROM PracovniNeschopnosti
GROUP BY zam_id) s
ON z.id = s.zam_id;
Tuto úlohu řeší OUTER JOIN. Jednoduše doplní výsledek tak, aby obsahovala celou tabulku uvedenou nalevo od klíčového slova JOIN (LEFT JOIN) nebo napravo (RIGHT JOIN). Nově přidané řádky zákonitě musí někde obsahovat jakousi vatu. Například Zaměstnanec, který nikdy nebyl nemocný, nemůže mít přiřazenou hodnotu sum(pocet). Tou vatou je hodnota NULL. Takže v další iteraci by náš dotaz vypadal takto:
SELECT jmeno, prijmeni, pocet
FROM Zamestnanci z
LEFT JOIN (SELECT zam_id, sum(pocet) AS pocet
FROM PracovniNeschopnosti
GROUP BY zam_id) s
ON z.id = s.zam_id;
Kdybyste si dali tu práci a příklad přepsali a vyzkoušeli, tak zjistíte, že to ještě není ono. U trvale zdravých zaměstnanců se nezobrazuje nula, ale prázdný řetězec neboli NULL. To už je drobnost. Použijeme funkci COALESCE, která slouží k náhradě NULL za libovolnou jinou hodnotu.
SELECT jmeno, prijmeni, coalesce(pocet, 0)
FROM Zamestnanci z
LEFT JOIN (SELECT zam_id, sum(pocet) AS pocet
FROM PracovniNeschopnosti
GROUP BY zam_id) s
ON z.id = s.zam_id;
Je důležité mít na paměti, že OUTER JOIN je podstatně náročnější na provádění než INNER JOIN, a tudíž jej používat jen tehdy, když je třeba. OUTER JOIN zapsaný bez klauzule JOIN může mít tuto podobu:
SELECT jmeno, prijmeni, 0 -- vždy zdraví zaměstnanci
FROM Zamestnanci
WHERE id NOT IN (SELECT zam_id
FROM PracovniNeschopnosti)
UNION ALL
SELECT jmeno, prijmeni, pocet -- zaměstnanci se záznamem
FROM Zamestnanci z,
(SELECT zam_id, sum(pocet) AS pocet
FROM PracovniNeschopnosti
GROUP BY zam_id) s
WHERE z.id = s.zam_id;
Často se OUTER JOIN používá pro vyhledání něčeho co někde není v kombinaci s operátorem IS NULL. Výpis všech vždy zdravých zaměstnanců lze provést dotazem:
SELECT jmeno, prijmeni, pocet
FROM Zamestnanci z
LEFT JOIN
PracovniNeschopnosti p
ON z.id = p.zam_id
WHERE pocet IS NULL;
Tento dotaz lze také přepsat s použitím poddotazu (případně korelovaného poddotazu). Opět záleží na aplikaci a datech, která varianta bude nejefektivnější:
-- klasický dotaz
SELECT jmeno, prijmeni
FROM Zamestnanci
WHERE id NOT IN (SELECT zam_id
FROM PracovniNeschopnosti);
SELECT jmeno, primeni
FROM Zamestnanci
WHERE id <> ALL (SELECT zam_id
FROM PracovniNeschopnosti);
-- korelovaný poddotaz (pouze pro úplnost, až na výjimky nebude tím optimálním)
SELECT jmeno, prijmeni
FROM Zamestnanci
WHERE NOT EXISTS (SELECT id
FROM PracovniNeschopnosti
WHERE zam_id = Zamestnanci.id);
Pozn.: V některých databázích je zjevná asymetrie v rychlosti zpracování LEFT OUTER JOINu a RIGHT OUTER JOINu (výhodnější je používat LEFT JOIN). Některé databáze RIGHT JOIN nemají implementován (SQLite). Jiné databáze tyto problémy nemají (PostgreSQL).
Několik poznámek na konec. Přesun GROUP BY do derivované tabulky je jedním z triků, které urychlují provádění dotazů. Platí, že čím víc sloupců klauzule GROUP BY obsahuje, tím je její zpracování pomalejší a vyžaduje více paměti. V příkladu navíc dochází ke spojení skutečné a derivované (odvozené) tabulky. To ve starších před SQL databázích nešlo. Následující příklad demonstruje právě tuto vlastnost SQL. Spojují se dvě tabulky na základě složeného predikátu. Jedná se o "klasický" příklad zobrazení zaměstnanců s nejvyšší mzdou v rámci oddělení. Tento vzor se opakuje v řadě SQL úloh.
Zamestanci(id, oddeleni_id, jmeno, prijmeni, mzda)
Oddeleni(id, nazev)
-- složený predikát
SELECT jmeno, prijmeni, mzda, nazev
FROM Zamestnanci z
JOIN (SELECT oddeleni_id, max(mzda) AS max_mzda
FROM Zemestnanci
GROUP BY oddeleni_id) s
ON z.oddeleni_id = s.oddeleni_id AND z.mzda = s.max_mzda
JOIN
Oddeleni o
ON o.id = z.oddeleni_id;
nebo Jelikož se JOIN použil pouze k filtrování záznamů, lze předchozí dotaz převést do tvaru s poddotazem:
-- jednoduchý predikát s binárním operátorem rovnost řádků
SELECT jmeno, prijmeni, mzda, nazev
FROM Zamestnanci z
JOIN
Oddeleni o
ON o.id = z.oddeleni_id
WHERE (z.oddeleni_id, z.mzda) = (SELECT oddeleni_id, max(mzda)
FROM Zamestnanci
GROUP BY oddeleni_id);
Konečně další možností je použití korelovaného poddotazu. Výsledky obou výše uvedených dotazů jsou stejné. Odlišný je však jejich prováděcí plán, tudíž i doba zpracování. A opět záleží na skutečných datech, která varianta bude optimální. Volba optimální varianty je jedním z mála způsobů jak ovlivnit zpracování SQL příkazu. Svou roli tu hraje i použitá databáze. To co je optimální pro MySQL, nemusí být optimální pro PostgreSQL nebo Firebird (a naopak). A aby to bylo ještě o něco komplikovanější, záleží i na konfiguraci serveru (na velikosti přidělené paměti).
FULL JOIN a CROSS JOIN
FULL JOIN zobrazí kompletně jak tabulku uvedenou nalevo od klíčového slova JOIN, tak tabulku uvedenou napravo. Nepoužívá se často a v SQL je spolu s CROSS JOINem spíše pro jazykovou úplnost. Zrovna tak CROSS JOIN, který odpovídá kartézskému součinu.
Zamestnanci CROSS JOIN Oddeleni ~ Zamestnanci JOIN Oddeleni ON true
Dříve se CROSS JOIN používal pro generování testovacích dat. V několika málo iteracích jsme se s jeho pomocí vygenerovali tabulky s milióny řádků.
SELF JOIN
Jako SELF JOIN se označuje obyčejně INNER JOIN, který má na obou stranách tutéž tabulku. Je to technika, která se používala k obejití chybějící funkcionality v SQL (takovou typickou úlohou, kterou v SQL92 jednoduše nevyřešíte, je výpočet klouzavých průměrů). Výhodou řešení založených na SELF JOINech je přenositelnost, nevýhodou rychlost. Moderní databáze podporují tzv. analytické dotazy, které umožňují efektivně řešit úlohy, které se řešily SELF JOINem. Pokud nejsou k dispozici analytické dotazy, pak se obvykle vyplatí použít uložené procedury. Náročnost na zpracování není možná ani tou hlavní nevýhodou. Jedná se o absolutní alchymii jakoby opsanou z testů Mensy. Zdaleka nejvíc se s touto technikou setkáte v SQL kvízech Joe Celka (asi jediná osoba na planetě, která tyto věci dokáže vymyslet).
Rozšířenou ukázkou je číslování řádků ve výpisu. Předpokládejme, že id zaměstnanců netvoří souvislou řadu, nicméně požadujeme, aby každý řádek měl své pořadové číslo (Ve starších verzích SQL bylo striktně dodržováno pravidlo, že o zobrazení se stará aplikace. Tudíž i o číslování řádků).
SELECT count(*), z1.jmeno, z1.prijmeni
FROM Zamestnanci z1
JOIN
Zamestnanci z2
ON z1.id >= z2.id
GROUP BY z1.jmeno, z1.prijmeni;
Poddotaz, korelovaný poddotaz, derivovaná tabulka - rekapitulace
Příkaz SELECT může obsahovat další vnořené příkazy SELECT. Pokud se tento vnořený příkaz vyskytne v klauzuli FROM, jedná se o derivovanou tabulku. V ostatních případech (seznam zobrazovaných sloupců a klauzule WHERE) takový SELECT nazýváme poddotazem. Pokud se v poddotazu vyskytne odkaz na vnější příkaz SELECT (pomocí aliasu), pak se jedná o korelovaný (vázaný poddotaz).
SELECT *
FROM Zamestnanci
WHERE oddeleni_id = (SELECT id -- poddotaz
FROM Oddeleni
WHERE nazev = 'Administrativa');
SELECT *
FROM Zamestnanci z -- alias na tabulku
WHERE mzda = (SELECT max(mzda)
FROM Zamestnanci
WHERE oddeleni_id = z.oddeleni_id); -- korelovany poddotaz
-- derivovaná tabulka
SELECT z.jmeno, z.prijmeni, mzda, s.max_mzda, s.min_mzda
FROM Zamestnanci z
JOIN (SELECT oddeleni_id, max(mzda) AS max_mzda, min(mzda) AS min_mzda
FROM Zamestnanci
GROUP BY oddeleni_id) s
ON z.oddeleni_id = s.oddeleni_id;
V 90% případů bude znatelně nejpomalejší varianta obsahující korelovaný poddotaz, bez kterého se ovšem některé úlohy nedají vyřešit. A opět, bude záležet na RDBMS a skutečných datech (objem, proporce, indexy, rozdělení,..), která že varianta bude pro určitou úlohu optimální. Napsat dotaz obsahují spojení tabulek je vlastně docela jednoduché. Přesto se při tom docela často chybuje, a velké procento vývojářů dává přednost nejrůznějším grafickým návrhovým prostředím. Napsat skutečně optimální dotaz je už jiná káva a i jiná kapitola.