Zobrazení zdroje stránky LEFT INNER JOIN

[[Category:Články]]
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.
<pre>
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
</pre>
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)
<pre>
Autori (id, jmeno, prijmeni)  .. pk je sloupec id
Knihy (id, autor_id, nazev) .. pk je sloupec id, fk (cizí klíč) je sloupec autor_id</pre>
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.
<pre>
SELECT jmeno, prijmeni, vek 
   FROM Zamestnanci
  WHERE vek >= 30;
</pre>
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:
<pre>
SELECT * 
  FROM Autori, Knihy;
</pre>
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.
<pre>
SELECT nazev, prijmeni, jmeno
   FROM Knihy
        JOIN
        Autori
        ON Knihy.autor_id = Autori.id;
</pre>
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ě:
<pre>
SELECT nazev, prijmeni, jmeno
   FROM Knihy
        JOIN
        Autori
        ON Knihy.autor_id = Autori.id
  WHERE autori.prijmeni = 'Čapek';
</pre>
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:
<pre>
SELECT nazev, prijmeni, jmeno
   FROM Knihy, Autori
  WHERE Knihy.autor_id = Autori.id AND autori.prijmeni = 'Čapek';
</pre>
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:
<pre>
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; 
</pre>
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:
<pre>
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; 
</pre>
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.
<pre>
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; 
</pre>
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:
<pre>
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;
</pre>
Č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:
<pre>
SELECT jmeno, prijmeni, pocet
   FROM Zamestnanci z
        LEFT JOIN 
        PracovniNeschopnosti p
        ON z.id = p.zam_id
  WHERE pocet IS NULL;
</pre>
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ší:
<pre>
-- 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);
</pre>
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.
<pre>
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;
</pre>
nebo
Jelikož se JOIN použil pouze k filtrování záznamů, lze předchozí dotaz převést
do tvaru s poddotazem:
<pre>
-- 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);
</pre>
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. 
<pre>
  Zamestnanci CROSS JOIN Oddeleni ~ Zamestnanci JOIN Oddeleni ON true
</pre>
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ů).
<pre>
SELECT count(*), z1.jmeno, z1.prijmeni
   FROM Zamestnanci z1
        JOIN
        Zamestnanci z2
        ON z1.id >= z2.id
  GROUP BY z1.jmeno, z1.prijmeni; 
</pre>
==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).
<pre>
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;  
</pre>
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.