SQL – spojování tabulek a tvorba pohledů

30. října 2000

•

Dnes se podíváme na vnější spojení tabulek, které nám rozšíří možnosti klasického vnitřního přirozeného spojení. Uděláme si srovnání těchto metod a řekneme si, jaké výhody nám vnější spojení tabulek může přinést. V druhé části se podíváme na tvorbu pohledů v databázi (tzv. view), k čemu jsou nám dobrá a jak je můžeme využít.

Vnitřní spojení

Vzpomínáte si na 6. díl, kde jsem mluvil o přirozeném spojení tabulkek? Pro rychlé zopakování uvedu příklad:

 POZDRAV   VYSLOVENÍ --------   -------- cis poz     kdo  co 1   ahoj    1    2 2   zdar    2    3 3   cau     3    2

Uvedené dvě tabulky zobrazují informace o tom, jaká osoba (číslo) vyslovila jaký pozdrav. Např. vyčteme, že osoba číslo 1 vyslovila pozdrav ‚zdar‘, atd. Jak bude vypadat přirozené spojení těchto dvou tabulek?

 kdo  co cis poz ---  -- --- ---- 1    2  2   zdar 2    3  3   cau 3    2  2   zdar

Když jsem o přirozeném spojení mluvil, uváděl jsem jednu důležitou vlastnost. A to, že do souhrnné tabulky nejsou zahrnuty řádky, které neměly odpovídající záznamy v tabulce druhé. Konkrétně ve výsledné tabulce chybí záznam s položkou ‚ahoj‘ – protože ji nikdo nevyslovil a neměla tedy záznam v tabulce VYSLOVENÍ. Chceme seznam všech vyslovených pozdravů a u každého z nich, kolikrát bylo vysloven:

SELECT poz, count(*)
FROM pozdrav, vyslovení
WHERE co = cis
GROUP BY poz

Uvedený dotaz nám vrátí 2 záznamy, první pro pozdrav ‚zdar‘ a druhý pro pozdrav ‚cau‘. Co kdybychom ale chtěli vypsat seznam všech existujících pozdravů a u každého z nich vypsat, kolikrát byl vysloven? Chtěli bychom tedy vypsat i řádek pro pozdrav ‚ahoj‘ a u něj číslo 0.

Vnější spojení

Je jasné, že s tímto dosavadním přístupem, který nazýváme vnitřní spojení (= některé řádky, které nemají odpovídající partnery v jiných tabulkách, se do výsledné souhrnné tabulky přirozeného spojení vůbec nedostanou), si nevystačíme. Proto standard SQL definuje tzv. vnější spojení, které nám umožní do souhrnné tabulky dostat i ty původně vynechávané řádky.

Chceme-li použít vnějšího spojení, musíme příkaz SELECT zapsat v odlišné syntaxi. Ta dle standardu SQL vypadá následovně:

SELECT seznam sloupců
FROM první_tabulka LEFT|RIGHT JOIN druhá_tabulka ON podmínka_spojení
WHERE restrikce
…

Uvedená syntaxe se oproti té předchozí liší jen ve druhém řádku. Jako první a druhou tabulku uvedeme názvy tabulek, které chceme spojit. Pak můžeme použít LEFT nebo RIGHT JOIN. Co to znamená? Tím řekneme, že v levé nebo v pravé tabulce dovolíme výskyt prázdných hodnot. (Levou a pravou tabulkou rozumím tabulku, která je zapsaná vlevo nebo vpravo vzhledem ke klíčovému slovu LEFT JOIN nebo RIGHT JOIN.) Za klíčové slovo ON dáme podmínku pro spojení, která má stejný tvar, jako v případě klasického vnitřního spojení.

Pro lepší pochopení se vraťme k našemu příkladu. Chceme tedy seznam všech pozdravů (nejen vyslovených) a u každého z nich uvedeno, kolikrát byl vysloven:

SELECT poz, count(co)
FROM pozdrav LEFT JOIN vysloveni ON cis = co
GROUP BY poz

Spojujeme zleva, tj. řekli jsme, že v tabulce POZDRAV chceme do výstupu všechny její záznamy, i když nemají odpovídající „partnery“ ve druhé tabulce. Jinými slovy, připouštíme existenci nulového výskytu hodnot pro odpovídající pozdrav v tabulce VYSLOVENI.

Odlišnosti od standardu

Pokud byste hledali místo, kde se databázové systémy hodně liší od standardu, pak vnější spojení je jedním z typických příkladů. Vezměme si dva, dle mého názoru nejpoužívanější, systémy MySQL a Oracle. MySQL se tohoto standardu drží, ale nemůžete použít RIGHT JOIN. Což až tak moc nevadí, neboť prohozením pořadí první a druhé tabulky a zápisem LEFT JOIN vyjádříte totéž spojení. Systém Oracle tuto situaci řeší jinak. V podstatě povoluje obě možnosti (levou i pravou). Zápis je velmi podobný původnímu zápisu vnitřního spojení. U názvu sloupce v restrikci zapíšeme vlevo nebo vpravo (tj. v závisloti, zda-li chceme dělat LEFT nebo RIGHT JOIN) speciální operátor (+). Uvedený příklad by v systému Oracle byl zapsán následovně:

SELECT poz, count(co)
FROM pozdrav, vysloveni
WHERE pozdrav.cis (+) = vysloveni.co
GROUP BY poz

Zápis je mnohem přehlednější a snadněji zapamatovatelný. Ve sloupci, ve kterém chci povolit existenci nulového počtu odpovídajících partnerů v jiné tabulce, zapíši operátor (+) a nemusím si pamatovat jinou syntaxi. S největší pravděpodobností v jiných databázových systémech najdete další různé odlišnosti.

Pohledy

Pohledy si lze představit jako virtuální (logické) tabulky v databázi. Pohledy nám mohou přinést řadu výhod. Jednou z nich je zpřehlednění práce s daty a sestavování dotazů. Dále mají své velké uplatnění z hlediska ochrany dat, např. můžeme vytvořit pohled, který bude mít stejný obsah jako tabulka záznamů o lidech ve firmě, s tím, že nebude ale obsahovat sloupec RODNÉ_ČÍSLO. My jako správci systému nemusíme nastavovat právo na čtení tabulky LIDÉ, ale dáme právo na čtení takového pohledu a máme zajištěno, že si rodná čísla nepřečte žádná neoprávněná osoba.

Pohled nemusí být vytvářen pouze nad jednou tabulkou, ale můžeme pohledem tabulek spojit více. A přesto, můžeme nad pohledy vykonávat standardní operace INSERT, DELETE, UPDATE, SELECT a SQL server už zajistí správnou manipulaci s odpovídajícími „fyzickými“ tabulkami. Z hlediska práce s tabulkami se nám pohled (často nazývaný view) jeví jako normální tabulka.

Pohled se vytváří příkazem CREATE VIEW, jehož syntaxe je následující:

CREATE VIEW název_pohledu [(názvy sloupců)] AS
SELECT …

V podstatě je view určeno výsledkem SQL dotazu, který může být libovolně složitý. Zápis [(názvy sloupců)] použijeme tehdy, chceme-li nějakým způsobem změnit názvy sloupců.

Příklady pohledů

Máme tabulku LIDÉ v níž evidujeme údaje o zaměstnanci ve firmě. Obsahuje sloupce ID, JMÉNO, PŘÍJMENÍ, RODNÉ_ČÍSLO, VZDĚLÁNÍ. Uživatelům budeme chtít zpřístupnit údaje z této tabulky, vyjma rodných čísel:

CREATE VIEW lidé_bez_rc AS
SELECT id, jméno, příjmení, vzdělání
FROM lidé

Uživatelé budou pracovat s pohledem LIDÉ_BEZ_RC, jako by to byla normální tabulka. Takže pokud bude chtít uživatel zobrazit seznam všech vysokoškolsky vzdělaných lidí, zapíše následující dotaz:

SELECT jméno, příjmení
FROM lidé_bez_rc
WHERE vzdělání LIKE ‚VŠ‘

Dále lze view použít pro změnu názvů i formátů sloupců. Vytvoříme pohled, který bude mít stejnou strukturu jako tabulka LIDÉ, jediný rozdíl bude v tom, že JMÉNO a PŘÍJMENÍ bude jako jeden sloupec s názvem CELÉ_JMÉNO:

CREATE VIEW lidé2 (id, celé_jméno, rodné_číslo, vzdělání) AS
SELECT id, příjmení+‘,’+‘ ‚+jméno, rodné_číslo, vzdělání
FROM lidé

Pohledy nemusíme používat jen ve spojení se sloupci. Můžeme také využít restrikce a omezit tak i některé řádky. Např. sekretářka bude mít právo manipulovat pouze se záznamy středoškoláků. View, které pro ni vytvoříme, bude vypadat následovně:

CREATE VIEW středoškolák AS
SELECT *
FROM lidé
WHERE vzdělání LIKE ‚SŠ‘

Poslední příklad uvedu pro případ tvorby pohledu skrz více tabulek. V naší firmě bude skupina ekonomů, která má právo vědět, kolik má kdo základní plat, ale už nechceme, aby měli přístup k osobnímu ohodnocení. Mějme tabulku LIDÉ a dále tabulku PLATY, která obsahuje sloupce ID_OSOBA, ZÁKL_PLAT, OSOBNÍ, PŘÍPL_VEDENÍ. Pro ekonomy zkonstruujeme následující pohled:

CREATE VIEW osoby_platy AS
SELECT id, jméno, příjmení, zákl_plat
FROM lidé, platy
WHERE lidé.id = platy.id_osoba

Dále pohledy můžeme využít v souvislosti s vnořováním dotazů. Pokud máme v našem informačním systému několik SQL dotazů, ve kterých se nám nějaký subdotaz dost často opakuje, můžeme si pro něj vytvořit view, na které se budeme pak v dotazech odkazovat.