Naši partneři
BMI SYSTEM CZECH
Informační systémy budoucnosti

INNER JOIN, OUTER JOIN, CROSS JOIN


Úvodem - spojování dat

Dnešní článek na téma, které považuji za samozřejmé a nezbytné pro práci s daty uloženými v databázi, jsem se rozhodl napsat poté, co se mě opakovaně ptali účastníci mých kurzů na to, jak operátory JOIN (případně UNION a další) vlastně fungují. Abych nemusel pořád dokola malovat stejné obrázky, rozhodl jsem se, udělat jakousi webovou prezentaci na téma JOIN, která následuje dále.

 
JOIN obecně

Operátor JOIN je ve všech svých podobách opeátorem složení dvou množin záznamů. Nejprve si řekněme, co mezi sebou můžeme spojovat:

  • Dvě tabulky
  • Tabulku a view
  • Dvě view
  • Tabulkovou funkci a tabulku (ne přes parametry funkce)
  • View a tabulkovou funkci (opět ne přes parametry)
  • Dvě tabulkové funkce (opět ne přes parametry jedné z funkcí)
  • Výsledek předchozího spojení s tabulkovým objektem (tabulkou, view, tabulkovou funkcí, s jiným výsledkem spojení)

Jinými slovy - opravdu spojujeme dvě relační množiny záznamů. Nerelační haldu dat (třeba výsledek skalární funkce, nebo ad absurdum video) v operátoru JOIN neumíme použít.

Celé spojování pomocí JOIN vychází z poměrně snadno pochopitelné množinové operace složení dvou množin.

Mějme množiny uspořádaných dvojic R = {[a,1], [b,2], [c,3], ...} a S = {[1,X],[2,Y],[3,Z],...}. Operace složení R 0 S = {[a,X],[b,Y],[c,Z], ...}. Množinové složení tedy probíhá tak, že z první (levé) množiny uspořádaných dvojic bereme druhý prvek každé uspořádané dvojice, a srovnáváme jej s prvními prvky všech uspořádaných dvojic druhé (pravé) množiny. Výsledkem složení je pak množina uspořádaných dvojic tvořených prvními prvky uspořádaných dvojic levé množiny a druhými prvky uspořádaných dvojic pravé množiny. Velmi podobně bude fungovat i JOIN, ale JOIN obsahuje ještě klauzuli ON, která "uvolňuje" pravidlo "druhý z první = první z druhé", ale dává nám na výběr to, čemu budeme říkat "kritérium spojení". Takže pokud bychom složení množin z našeho příkladu provedli "databázově", napíšeme třeba:

R JOIN S ON R.druhý = S.první

nebo také:

R JOIN S ON R.první = S.druhý

a výsledkem (pokud budeme předpokládat, že malá a velká písmena jsou totéž), bude {[1,X],[2,Y],[3,Z],...}.

Další finesy operátoru JOIN, tedy zejména směr tzv. vnějšího (OUTER) spojení, si rozebereme v dalších kapitolách.

 
INNER JOIN - vnitřní spojení

Na operátoru INNER JOIN si ukážeme kromě funkčnosti také syntaxi - zápis. Takže si představme dvě tabulky popsané v následujícím schématu:

Obr 1: Schéma Člověk a jeho pes

Úloha zní následovně: Vytvořme seznam lidí (jméno a příjmení), a k nim přiřaďme jména psů.

řešení tedy spočívá v tom, že musíme spojit obě tabulky přes nějakou společnou hodnotu. Společnou hodnotou je primární klíč tabulky Člověk (Id), a cizí klíč v tabulce Pes (ClovekId - odkazuje na primární klíč tabulky Clovek). Tabulky tedy spojit umíme, a teď už zbývá jen napsat správný příkaz:

SELECT Jmeno, Prijmeni, JmenoPsa

FROM Clovek INNER JOIN Pes ON Clovek.Id = Pes.ClovekId

Jak je vidět ze zápisu, spojení realizujeme v klauzuli FROM, tedy v místě, kam píšeme seznam tabelárních objektů použitých v dotazu. Operátor "JOIN" stavíme mezi dvě tabulky, které chceme spojit, a za samotné spojení (Clovek JOIN Pes) připíšeme ještě kritérium (nebo více kritérií) spojení. Jinými slovy - za spojení dopíšeme klíčové slovo "ON", za které napíšeme logický výrok (v našem případě rovnost Clovek.Id = Pes.ClovekId), který je v průběhu dotazu vyhodnocován, a záznamy, které mají v příslušných položkách shodu, jsou spojeny a vyslány do výstupu dotazu.

 
OUTER JOIN - vnější spojení

Operátor OUTER JOIN se v mnohém podobá operátoru INNER JOIN, ale má přeci jen svá specifika. První z nich spočívá v tom, že stanovujeme "směr" spojení. Směr je buď zleva (LEFT OUTER JOIN), zprava (RIGHT OUTER JOIN), nebo z obou stran (FULL OUTER JOIN). Jaký má směr význam, si hned ukážeme.

Vraťme se k našemu schématu Člověk a jeho pes, a zkusme si představit, že v tabulce Člověk jsou záznamy, na kterých není závislý žádný záznam v tabulce Pes. Zkrátka, máme v evidenci lidi bez psů. A v této situaci řešme úlohu, kdy potřebujeme vytvořit SELECT, v jehož výsledku se objeví položky Jméno, Příjmení (obě z tabulky člověk) a Jméno psa (z tabulky pes), a to tak, že jméno a příjmení člověka se musí ve výsledku objevit i tehdy, když člověk nemá psa.

Pokud bychom spojení mezi tabulkami Člověk a Pes napsali pomocí operátoru INNER JOIN, lidé bez psů se ve výsledku neobjeví. Na to právě slouží operátor LEFT (resp. RIGHT) OUTER JOIN.

Píšeme tedy:

SELECT Jmeno, Prijmeni, JmenoPsa

FROM Clovek LEFT OUTER JOIN Pes ON Clovek.Id = Pes.ClovekId

nebo můžeme napsat

SELECT Jmeno, Prijmeni, JmenoPsa

FROM Pes RIGHT OUTER JOIN Clovek ON Clovek.Id = Pes.ClovekId

Při obou zápisech docílíme toho, že z tabulky Člověk se vypíší na výstup všechny záznamy, ale z tabulky pes jen ty, které mají shodu v kritériu spojení. U lidí, kteří nemají psa, bude v položce JmenoPsa hodnota NULL.

Na celém využití OUTER JOINU je důležitá poloha tabulek kolem slova JOIN. V případě LEFT OUTER JOIN se vypíše obsah té tabulky, která je od slova JOIN vlevo, v případě RIGHT OUTER JOIN je to přesně naopak.

Abychom nezapomněli okomentovat FULL OUTER JOIN - Je to operátor "pravo-levý", to znamená, že se vypíší kompletní obsahy obou tabulek, ale spojeny budou pouze záznamy, které budou mít shodu v kritériu spojení, ostatní záznamy budou mít v položkách "opačné" tabulky hodnotu NULL.

V následující kapitolce ještě chvíli budeme OUTER JOINY zkoumat, ukážeme si totiž, že při použití OUTER JOIN spojení je nutné dát dobrý pozor na podmínky.

 
OUTER JOIN - pozor na klauzuli WHERE!

Pokud používáme vnější spojení (např. LEFT OUTER JOIN) musíme si dávat velký pozor na podmínky, které klademe na pravou tabulku. Zní to krkolomně? Pomůže příklad. Z našeho datového schématu máme za úkol vybrat všechny lidi, a k těm, kteří mají, i psa. Ale musíme myslet nejen na lidi, kteří nemají žádný záznam o psu v tabulce Psi, ale také na lidi, kteří psa měli, ale uhynul (položka Datum úhynu).

Co nás napadne jako první? LEFT OUTER JOIN:

SELECT * FROM clovek LEFT OUTER JOIN pes ON clovek.id = pes.clovekid

Dobrá, vybrali jsme všechny lidi, a k nim psy, pokud existují, ale vybrali jsme i uhynulé (tedy reálně neexistující) psy.

Co nás napadne jako další? Vhodná podmínka WHERE:

SELECT * FROM clovek LEFT OUTER JOIN pes ON clovek.id = pes.clovekid

WHERE Pes.DatumUhynu IS NULL

Co se stalo? Položili jsme podmínku na pravou tabulku levého spojení. Výsledkem tohoto počinku je stav, kdy záznamy se nejprve spojí podle kritéria uvedeného u samotného JOINu, a pak jsou tyto spojené záznamy filtrovány podmínkou WHERE. To znamená, že člověk, který má jen uhynulého psa (má datum úhynu různé od NULL) zmizí z výsledkové sady úplně, jako celý záznam! Tím pádem nesplníme úlohu "vypište VŠECHNY lidi ...".

Jak z toho ven? Přidat podmínku na datum úhynu do JOINu:

SELECT * FROM clovek LEFT OUTER pes ON clovek.id = pes.clovekid

AND datumuhynu IS NULL

Proč je to jinak? Už v úvodu článku stojí psáno, že za klíčovým slovem ON stavíme logický výrok (tedý formuli, jejíž vyhodnocení je buď true nebo false), který řeší shodu záznamů pro spojení. Podmínka WHERE naopak slouží jako filtr už spojených záznamů. Takže když jsme spojovali záznamy jen na základě shody hodnot položek primárního a cizího klíče, provedl se LEFT OUTER JOIN, který byl nakonec "odfiltrován" klauzulí WHERE. Klauzule WHERE tak mimoděk způsobila, že některé záznamy spojené pomocí JOIN byly eliminovány, a naše zadání nebylo splněno. Ale když jsme podmínku kladenou na pravou tabulku levého spojení dali přímo do logického výroku spojení (jako součást JOINu), tato podmínka eliminovala pouze záznamy z pravé tabulky levého JOINu, a pak se teprve provedlo spojení.

Pokud byl předchozí popis poněkud spletitý, zkuste si tabulky sami vyrobit, a do tabulky lidí dát tři lidi (třeba pan "Psový", "Nepsový" a "Uhynulý"), a do tabulky psů dejte k jednomu člověku záznam se psem, který má datum úhynu NULL, a ke druhému člověku dále záznam, který bude mít datum úhynu vyplněno (logicky nějakým minulým datem, ale pro pokus to není podmínkou). Ke třetímu člověku nedávejte žádný záznam. Pak spusťte tři uvedené příkazy SELECT, a zkoumejte obsah výsledku.

 
CROSS JOIN - ať to máme komplet

Operátor spojení CROSS JOIN má jedno specifikum - nemá klíčové slovo ON, které by určilo kritéria spojení. Takže pokud se budeme držet našich lidí se psy, a provedeme mezi nimi spojení CROSS JOIN, bude výsledkem kartézský součin, tedy kombinace všech záznamů z obou tabulek. Syntaxe vypadá takhle:

SELECT * FROM Lide CROSS JOIN Psi

K čemu je něco takového dobré? K testování. Pokud máte k dispozici třeba databázi AdventureWorks (cvičná databáze instalovaná v rámci instalace MS SQL Serveru 2005 nebo výše), vyhledejte tabulku Person.Contact, a když se do ní podíváte, zjistíte, že je v ní kombinace všech jmen a příjmení. Takto se ze dvou malých tabulek stala jedna veliká, která má reprezentativní vzorek dat. V produkci ale tento operátor prakticky nemá uplatnění.

 
Sdílejte tuto položku se svými přáteli
 
Související články
V předchozím vývojářském článku o operátoru JOIN jsem sliboval popis CROSS APPLY. Tady je.
7/29/2015 5:35:31 PM
TOPlist