Úvod do datové analýzy - lekce 11

SQL5 - Vnější spojení

V této části je popsáno vnější spojení tabulek, jeho odlišnosti od vnitřního spojení a způsoby, jak lze tento typ spojení prakticky využít.

Rozdíl oproti vnějšímu

Na rozdíl od vnitřního spojení stačí u vnějšího spojení, když se záznam nachází jen v jedné z tabulek (čili se na základě podmínky ON nenalezne v druhé tabulce odpovídající záznam). Takové záznamy potom mají ve výstupu druhou stranu vyplněnou hodnotami NULL. Která strana pravidla může být NULL se určuje variantou vnějšího spojení. V případě LEFT JOINu může hodnoty NULL obsahovat pravá strana. U varianty RIGHT JOIN je to naopak levá strana a v případě FULL JOINu jsou to obě strany.

Jak spojení napsat

Vnější spojení se zapisuje v zásadě stejně jako vnitřní spojení. Jediným rozdílem je určení varianty vnějšího spojení. Může to tedy být například LEFT (OUTER) JOIN. Klíčové slovo OUTER je opět nepovinné, lze tedy psát zkráceně např. LEFT JOIN. Zápis spojení tabulek tedy může vypadat například takto:

SELECT
*
FROM
Product AS p LEFT JOIN Sales s ON p.ProductID = s.ProductID

Takto zapsaný dotaz znamená, že výsledek bude obsahovat všechny záznamy z levé tabulky (Product). V případě, že se podle ProductID najde v pravé tabulce jeden nebo více odpovídajících záznamů, připojí se ve výstupu tyto odpovídající pravé strany. Pokud není v pravé tabulce žádný takový záznam nalezen, vyplní se pravá strana hodnotami NULL. V tomto konkrétním případě tedy budou výsledkem řádky obsahující informace o všech prodejích daného výrobku nebo řádek obsahující pouze levou stranu (výrobek) a na pravé straně hodnoty NULL. To jsou výrobky, které se nikdy neprodaly. Takové výrobky můžeme jednoduše vypsat tak, že vypíšeme jen řádky s pravou stranou obsahující hodnoty NULL. Do podmínky můžeme z pravé strany vybrat libovolný sloupec. Dejte si však pozor na případy, kdy může být hodnota v daném sloupci NULL, i když řádek existuje. Ideální je tedy vybrat do podmínky sloupec, který běžně hodnoty NULL nabývat nemůže - v našem případě například ProductID. Výsledný dotaz by tedy vypadal následovně:

SELECT
*
FROM
Product AS p LEFT JOIN Sales s ON p.ProductID = s.ProductID
WHERE
s.ProductID IS NULL

Všimněte si využití prefixu „s.“, který značí, že pracujeme se sloupce ProductID z tabulky Sales. Dále si všimněte, že podmínka není zapsaná jako = NULL, ale IS NULL. Je tomu tak proto, že hodnota NULL je speciální a nelze ji porovnávat běžným operátorem =.

Úplně stejně funguje RIGHT JOIN. Rozdíl je jen v tom, že ve výsledku budou všechny řádky z pravé tabulky a hodnoty NULL může obsahovat levá strana. V případě FULL JOINu jsou ve výsledku všechny řádky z obou tabulek a NULL hodnoty se mohou vyskytovat na obou stranách. Jednotlivé varianty jsou popsány a graficky znázorněny ve videu.

Podmínka ON

Zde je především potřeba si uvědomit, že součástí podmínky ON mohou být i jiné části, než je cizí klíč. Je však nutné dát si pozor na význam celé podmínky. Ilustrujeme si to na jednoduchém příkladu. Vezmeme předchozí příklad, kdy chceme vypsat výrobky, které se vůbec neprodávaly. Nyní ale budeme chtít výrobky, které se neprodávaly v roce 2014.

Dotaz tedy o tuto podmínku rozšíříme následujícím způsobem:

SELECT
*
FROM
Product AS p LEFT JOIN Sales s ON p.ProductID = s.ProductID
WHERE
s.ProductID IS NULL AND
DATEPART(YEAR, s.Date) = 2014

Výsledkem tohoto dotazu je prázdná množina. Je to dané tím, že výstup vnějšího spojení, který je výsledkem části FROM, obsahuje řádky všech prodejů a řádky, v nichž je celá pravá strana vyplněná hodnotami NULL. Podmínka s.ProductID IS NULL ponechá pouze řádky s prázdnou pravou stranou. Pokud se na ně následně pokusíme použít podmínku, že rok musí být 2014, všechny řádky zmizí, protože ve sloupci s.Date obsahují hodnotu NULL, nikoliv hodnotu 2014. Jak tedy tento dotaz napsat správně. Opět si vezmeme na pomoc praktický příklad. Pokud máme katalog výrobků (tabulka Product) a chceme vybrat ty, které se neprodávaly v roce 2014, tak zkrátka půjdeme výrobek po výrobku a u každého si na katalogový list poznačíme čísla faktur z roku 2014, v nichž daný výrobek figuroval. Jakmile to provedeme u všech výrobků, můžeme vybrat jen katalogové listy, na nichž není žádné číslo faktury. Dotaz by tedy vypadal takto:

SELECT
*
FROM
Product AS p
LEFT JOIN Sales s
ON p.ProductID = s.ProductID AND DATEPART(YEAR, s.Date) = 2014
WHERE
s.ProductID IS NULL

Podmínka obsahující rok se tedy musí přidat již do podmínky ON v rámci LEFT JOINu.

Pořadí tabulek

Na rozdíl od vnitřního spojení je u vnějšího spojení pořadí tabulek naprosto zásadní. Vysvětlíme si to například na vnějším spojení tabulek Product a Manufacturer. Dotaz

SELECT
*
FROM
Product AS p
LEFT JOIN Manufacturer m ON p.ManufacturerID = m.ManufacturerID

vrátí ve výstupu všechny produkty, přičemž v pravé části výsledku bude uveden výrobce nebo hodnoty NULL v případě, že daný výrobek výrobce nemá (nebo má vyplněn identifikátor neexistujícího výrobce).

Oproti tomu dotaz

SELECT
*
FROM
Product AS p
LEFT JOIN Manufacturer m ON p.ManufacturerID = m.ManufacturerID

vrátí ve výstupu všechny výrobce a na pravé straně budou výrobky, které daný výrobce vyrábí. Každý z výrobců tedy může mít ve výstupu více řádků. V případě, že výrobce nevyrábí žádné výrobky, bude mít ve výstupu jen jeden řádek, v jehož pravé části budou hodnoty NULL.

Pro úplnost doplníme, že pokud bychom z výstupu vynechali řádky, jejichž pravá strana obsahuje hodnoty NULL, budou výstupy obou dotazů stejné a budou odpovídat výstupu vnitřního spojení.

Jak spojení kombinovat

Kombinování různých typů spojení (ale i více spojení stejného typu) v rámci jednoho dotazu může být někdy poměrně nepřehledné. Dobrým zvykem je vytvořit si zkušební datovou sadu obsahující takové kombinace hodnot, že jsou pokryty všechny kombinace. Na této sadě si potom můžete vyzkoušet, jaké jsou mezivýsledky jednotlivých spojení a zda splňují vaše očekávání. Zaměřte se především na pořadí tabulek a na to, jestli by podmínky obsažené v části WHERE neměly být součástí některé z podmínek ON.

Vzhledem k tomu, že kombinování různých typů spojení bývá složité, může být vhodnější se mu vyhnout. To lze provést řadou různých způsobů, z nichž většina spočívá v tom, že si výsledky jednotlivých spojení uložíme do nějakého dočasného úložiště. Tímto úložištěm mohou být například dočasné tabulky, tabulkové proměnné nebo CTE. Poslední jmenovaná varianta je rozebrána v následující části.

Praktické užití

Vnější spojení je nesmírně výhodné obzvláště v případech, kdy potřebujeme hledat právě záznamy, které svůj protějšek ve druhé tabulce nemají. Jak jste viděli na předchozích příkladech, může se jednat například o hledání výrobců, kteří nemají výrobky. Kromě toho existují i další obvyklé vzory, kde se vnější spojení využívá. Některé z nich si popíšeme podrobněji.

Hledání extrémů

Jedním z častých použití je hledání extrémů. V tomto případě se zpravidla využívá tzv. self-join. To je spojení, kdy se na pravé i levé straně vyskytuje stejná tabulka. Funguje to v zásadě tak, že pro každý řádek hledáte v tabulce jiný řádek, jehož hodnta je větší (resp. menší). Pokud takový řádek nenajdete, znamená to, že se jedná o maximum (minimum). Možná si říkáte, proč k tomu nevyužít agregační funkce MAX a MIN. Rozdíl je v tom, že tyto funkce vrací nejvyšší hodnotu v daném sloupci, ale nevrátí ostatní sloupce daného záznamu. Při použití vnějšího spojení toho lze snadno dosáhnout. Ukázku tohoto vzoru uvidíte v příští části.

Hledání poškozených vazeb

Toto je typický příklad využití vnějšího spojení. Viděli jste ho už na jednom z předchozích příkladů, kdy jsme hledali výrobky, které nemají žádného výrobce. Pokud je stanoveno, že k takové situaci nesmí docházet, lze takto jednoduše identifikovat vadné řádky a ty následně opravit (pokud je to možné) nebo vymazat.

Odstraňování duplicit

Tento způsob využití je v zásadě velmi podobný hledání extrémů. Funguje tak, že se opět provede vnější self-join, v podmínce ON se určí rovnost jednotlivých položek a nerovnost alespoň jedné z nich. Představte si například, že každý prodej je zanesen v nějaké tabulce, která obsahuje unikátní sloupec, např. Id. V takovém případě lze duplicity snadno nalézt a odstranit tak, že pro každý z řádků hledáme jiný řádek, který má všechny položky shodné a nižší hodnotu položky Id. Všechny řádky výstupu, které mají obě strany, obsahují na své zdrojové straně duplicitní záznam. Pokud je tedy nějaký záznam v tabulce vícekrát, bude mít na druhé straně hodnoty NULL pouze ten z nich, který má nejnižší hodnotu Id.

Další materiály

Pokud si budete chtít spojování tabulek vyzkoušet přetahováním jednotlivých řádků jako ve videu, můžete využít soubor SQL5.drawio. Stačí přistoupit na adresu draw.io, zvolit možnost Open Existing Diagram a vybrat tento soubor.

Samostatná práce

V prezentaci SQL5 - Samostatná práce naleznete příklady na procvičení znalostí získaných v této části. Na každém snímku je vždy zadání a po posunutí se zobrazí odpověď. Než se na odpověď podíváte, zkuste nejdřív přijít na řešení samostatně.

‍