9. diel - MS-SQL krok za krokom: Dopyty cez viac tabuliek (JOIN)

V predchádzajúcom kvíze, Kvíz - Výber dát, radenie a dátové typy v MS-SQL, sme si overili nadobudnuté skúsenosti z predchádzajúcich lekcií.

Dnes v MS-SQL tutoriáli začneme pracovať na jednoduchom redakčnom systéme, ktorý môže pripomínať ten tu na ITnetwork. Ukážeme si dotazovanie cez viac tabuliek.

Konceptuálny model

V nasledujúcich dieloch si teda v databáze vytvoríme taký zjednodušený ITnetwork. Porozprávajme sa najprv o tom, ako to bude vyzerať. Dnes stihneme pochopiteľne len malú časť. Pretože obrázok niekedy povie viac než tisíc slov, začnime práve ním:

To, čo vidíme, je tzv. konceptuálny model. Je vytvorený pomocou notácie (grafického jazyka) UML a v praxi sa takéto diagramy veľmi často tvoria predtým, než začneme písať nejaký kód. Dobre si tak najprv rozmyslíme, čo vlastne chceme urobiť.

Vidíme, že v systéme figuruje používateľ, ktorý môže písať komentáre a články. Články spadajú do sekcií. Ide teda o databázu takého veľmi jednoduchého redakčného systému, ktorý si vďaka ITnetwork určite dokážeme predstaviť.

Príprava tabuliek a dát

Dnes sa zameriame na dotazy cez viac tabuliek. Poďme si najprv nejaké tabuľky vytvoriť. Úplne nám budú stačiť používatelia a články.

Používatelia

Pretože používateľ bude vyzerať trochu inak, než nám vyzeral doteraz, založíme si tabuľku Pouzivatelia v našej databáze databazaPreWeb z minulých lekcií znovu. Tú súčasnú tabuľku teda "dropneme":

DROP TABLE [Pouzivatelia];

Následne vytvoríme tabuľku novú. Používateľ tu bude mať (okrem Id) prezývku, email a heslo:

CREATE TABLE [Pouzivatelia] (
    [Id] INT IDENTITY,
    [Prezyvka] NVARCHAR(155),
    [Email] NVARCHAR(155),
    [Heslo] NVARCHAR(255),
    PRIMARY KEY ([Id])
);

Všimnime si, že sme použili alternatívny spôsob definície primárneho kľúča tabuľky. Po deklarácii všetkých stĺpcov sme uviedli kľúčové slová PRIMARY KEY a do zátvoriek stĺpec, ktorý má tvoriť primárny kľúč.

Do zátvoriek môžeme uviesť pokojne aj kombináciu viacerých stĺpcov oddelených čiarkami. V takom prípade ide o tzv. kompozitný primárny kľúč. Namiesto toho, aby bol každý riadok jednoznačne identifikovaný hodnotou v jedinom stĺpci, je identifikovaný unikátnou kombináciou hodnôt vo všetkých stĺpcoch, ktoré tvoria tento kľúč.

Do používateľov si rovno nejakých vložíme:

INSERT INTO [Pouzivatelia] ([Prezyvka], [Email], [Heslo]) VALUES
('Michaela', '[email protected]', 'dGg#@$DetA53d'),
('Ján', '[email protected]', '$#fdfgfHBKBKS'),
('Tomáš', '[email protected]', 'Jmls_aSW2RFss'),
('Ema', '[email protected]', 'fw8QT32qmcsld');

Články

Článok bude prepojený s používateľom, ktorý ho napísal, teda s jeho autorom. Tabuľky prepojíme tak, že do tabuľky Clanky pridáme stĺpec s Id autora. Tam bude hodnota Id používateľa (teda primárny kľúč z tabuľky Pouzivatelia), ktorý článok napísal.

Hovoríme o väzbe 1:N (1 používateľ má N (niekoľko) článkov a každý článok patrí práve jednému používateľovi). Časť (tu článok) má vždy uložené Id celku (tu používateľ), kam patrí.

Článok bude obsahovať (opäť okrem svojho Id) Id autora, krátky popis, URL, kľúčové slová, titulok, obsah a dátum publikácie. Založme si tabuľku Clanky:

CREATE TABLE [Clanky](
    [Id] INT IDENTITY,
    [AutorId] INT,
    [Popis] NVARCHAR(155),
    [Url] NVARCHAR(155),
    [KlucoveSlova] NVARCHAR(155),
    [Titulok] NVARCHAR(155),
    [Obsah] NVARCHAR(MAX),
    [Publikovane] DATETIME,
    PRIMARY KEY ([Id])
);

Za povšimnutie stojí asi len použitie typu NVARCHAR(MAX) pre text článku. Ten tak bude môcť mať v podstate neobmedzenú dĺžku.

Ďalej pridáme články a k nim priradíme používateľov ako autorov. Vzali sme 4 články tu z ITnetwork, ktoré sme značne skrátili a zjednodušili. Dotaz bude nasledujúci:

INSERT INTO [Clanky] ([AutorId], [Popis], [Url], [KlucoveSlova], [Titulok], [Obsah], [Publikovane]) VALUES
(1, 'Čo je to algoritmus? Ak to neviete, prečítajte si tento článok.', 'co-je-to-algoritmus', 'algoritmus, co je to, vysvetlenie', 'Algoritmus', 'Keď sa bavíme o algoritmoch, poďme sa teda zhodnúť na tom, čo ten algoritmus vôbec je. Jednoducho povedané, algoritmus je návod na riešenie nejakého problému. Keď sa na to pozrieme z ľudského pohľadu, algoritmus by mohol byť napríklad návod, ako ráno vstať. Aj keď to znie jednoducho, je to celkom problém. Počítače sú totiž stroje a tie nemyslia. Musíme teda dopodrobna opísať všetky kroky algoritmu. Tým sa dostávame k prvej vlastnosti algoritmu - musí byť elementárny (skladať sa z konečného počtu jednoduchých a ľahko zrozumiteľných krokov, teda príkazov). "Vstaň z postele" určite nie je algoritmus. "Otvor oči, odokry perinu, posaď sa, daj nohy na zem a postav sa" - to už znie celkom podrobne a išlo by teda o pravý algoritmus. My sa však budeme pohybovať v IT, takže budeme riešiť problémy ako zoraď prvky podľa veľkosti alebo vyhľadaj prvok podľa jeho obsahu. To sú totiž 2 základné úlohy, ktoré počítače robia najčastejšie a ktoré treba dokonale premýšľať a optimalizovať, aby trvali čo najkratší čas. Z ďalších príkladov algoritmov mi napadá napríklad vyrieš kvadratickú rovnicu alebo vyrieš sudoku.', '2012-3-21'),
(2, 'Baktérie sú obdoba bunkového automatu v kombinácii s hrou.', 'bakterie-bunkovy-automat', 'bakterie, automat, algoritmus', 'Baktérie', 'Baktérie sú obdoba bunkového automatu, ktorý vymyslel britský matematik John Horton Conway v roku 1970. Celú túto hru riadia štyri jednoduché pravidlá:/n/n
1. Živá baktéria s menej než dvoma živými susedmi umiera./n
2. Živá baktéria s viac než tromi živými susedmi umiera na premnoženie./n
3. Živá baktéria s dvomi alebo tromi susedmi prežíva bezo zmeny do ďalšej generácie./n
4. Mŕtva baktéria s presne tromi živými susedmi opäť ožíva./n
Tieto zdanlivo úplne primitívne pravidlá dokážu pri správnom počiatočnom rozmiestnení baktérií vytvoriť pochodujúce skupinky, zhluky "vystreľujúce" pochodujúce päťice, prekvapivo zložité súmerné explózie, oscilátory (periodicky kmitajúce skupinky) či nekonečnú podívanú na to, aké zložité a dokonalé obrazce dokážu tieto dve podmienky vytvoriť. Celý program je koncipovaný ako hra, máte za úlohu vytvoriť čo najdlhšie žijúcu kolóniu. <a href="soubory/bakterie.zip" ', '2012-2-14'),
(3, 'Cheese Mouse je oddychová plošinovka.', 'cheese-mouse-oddychova-plosinovka', 'mys, syr, hra', 'Cheese Mouse', 'Cheese Mouse je plošinovka s "horúcou ostrovnou atmosférou", kde ovládate myš a musíte sa dostať k syru. V tom vám však bránia rôzne nástrahy a nepriatelia ako hady, potkany, pirane, ale aj roboti, múmie a rôzna háveď. Hru s niekoľkými pestrými svetmi som robil ešte na základnej škole s Veisenom a môže sa pochváliť 2. miestom v Bonusweb game competition, kde vyhrala 5 000 Kč. Vznikala v Game makeri počas letných prázdnin, ešte v bezstarostnom detstve, čo značne ovplyvnilo jej grafickú stránku. Rád si ju občas zahrám na odreagovanie a zlepšenie nálady. <a href="soubory/cheesemouse.zip" />', '2004-6-22'),
(2, 'Pacman je remake kultovej hry.', 'pacman-remake', 'pacman, remake, pampuch, hra, zadarmo', 'Pacman', 'Ide o úplne základnú verziu tejto hry s editorom levelov, takže si môžete vytvoriť svoje vlastné kolá. Postupom času ju plánujem ešte trochu upraviť a pridať nejaké nové prvky, fullscreen a lepšiu grafiku. Engine hry bude tiež základom môjho nového projektu Geckon man, ktorý je zatiaľ vo fáze písania scenára. <a href="soubory/pacman.zip" />', '2011-6-3');

Dotazy cez viac tabuliek

Teraz máme v databáze články a k nim priradených používateľov. Poďme si urobiť dotaz cez tieto 2 tabuľky, získajme články a k nim pripojme prezývky ich používateľov. Slovo pripojme sme nepoužili náhodou, príkaz na spojenie 2 tabuliek sa totiž volá JOIN. Napíšme si dotaz a potom si ho vysvetlime. Dotazy už budeme písať na viac riadkov, aby sme sa v tom vyznali:

SELECT [Titulok], [Prezyvka]
FROM [Clanky]
JOIN [Pouzivatelia] ON [Clanky].[AutorId] = [Pouzivatelia].[Id]
ORDER BY [Prezyvka];

Výsledok:

Titulok	Prezyvka
Baktérie	Ján
Pacman	Ján
Algoritmus	Michaela
Cheese Mouse	Tomáš

Na prvom riadku príkazu SELECT pracujeme so stĺpcami úplne rovnako, ako keby boli v jednej tabuľke, jednoducho vymenujeme, čo nás zaujíma. Keďže vyberáme články a k nim pripájame používateľov, budeme vyberať z tabuľky Clanky. Pripojenie dát z inej tabuľky urobíme pomocou príkazu JOIN, kde uvedieme tabuľku, ktorú pripájame, a potom klauzulu ON. Ak majú v dvoch tabuľkách stĺpce rovnaké názvy, predsadíme stĺpec ešte názvom tabuľky, do ktorej patrí, a oddelíme bodkou. Klauzula ON je podobná ako WHERE, len platí pre pripájanú tabuľku a nie pre tú, z ktorej primárne vyberáme. V podmienke uvedieme, aby sa ku každému článku pripojil ten používateľ, ktorého Id je uvedené v stĺpci AutorId. Výsledok sme zoradili podľa prezývky používateľov. Keby sme chceli len nejaké články, normálne by sme pred ORDER BY uviedli ešte WHERE, ako sme zvyknutí.

INNER JOIN a OUTER JOIN

INNER (vnútorný) a OUTER (vonkajší) JOIN sú 2 typy príkazu JOIN. Fungujú úplne rovnako, jediný rozdiel je v tom, čo sa stane, keď položka, na ktorú sa väzba odkazuje, neexistuje.

INNER JOIN

Ak uvedieme v SQL dotaze iba JOIN, MS-SQL databáza ho považuje za tzv. INNER JOIN. Ak by v našom prípade neexistoval používateľ s Id, ktoré je pri článku uvedené, článok bez používateľa by vôbec nebol vo výsledkoch obsiahnutý. Väzba je nerozdeliteľná.

Poďme si to skúsiť, pridajme si článok, ktorý bude odkazovať na Id neexistujúceho používateľa:

INSERT INTO [Clanky] ([AutorId], [Popis], [Url], [KlucoveSlova], [Titulok], [Obsah], [Publikovane]) VALUES
(99, 'Článok s neexistujúcim používateľom slúži na vyskúšanie typov JOINov.', 'clanok-bez-autora', 'clanok, join, autor, chybajuci', 'Článok bez autora', 'Tento článok je priradený neexistujúcemu používateľovi s ID 99 a slúži na vyskúšanie rôznych typov JOINov v MS-SQL databáze.', '2012-10-21');

Vložený článok sa odkazuje na používateľa s Id 99, ktorý v databáze nie je. Spustime si teraz znovu náš SQL dotaz s JOINom. Pre prehľadnosť môžeme uviesť, že chceme INNER JOIN:

SELECT [Titulok], [Prezyvka]
FROM [Clanky]
INNER JOIN [Pouzivatelia] ON [Clanky].[AutorId] = [Pouzivatelia].[Id]
ORDER BY [Prezyvka];

Výsledok:

Titulok	Prezyvka
Baktérie	Ján
Pacman	Ján
Algoritmus	Michaela
Cheese Mouse	Tomáš

Výsledok je stále rovnaký, článok bez autora medzi výsledkami nie je.

LEFT OUTER JOIN

Vonkajšie JOINy umožňujú vyberať aj tie výsledky, ktoré sa nepodarilo spojiť z dôvodu chýbajúcich položiek. Skúsme si tzv. LEFT JOIN, ktorý výsledok uzná, ak existuje ľavá časť väzby (tu článok) a pravá (tá pripájaná, tu používateľ) neexistuje. Do hodnôt stĺpcov z pripájanej časti sa vloží NULL:

SELECT [Titulok], [Prezyvka]
FROM [Clanky]
LEFT JOIN [Pouzivatelia] ON [Clanky].[AutorId] = [Pouzivatelia].[Id]
ORDER BY [Prezyvka];

Výsledok:

Titulok	Prezyvka
Článok bez autora	NULL
Baktérie	Ján
Pacman	Ján
Algoritmus	Michaela
Cheese Mouse	Tomáš

Vidíme, že článok sa aj tak vybral, aj keď sa nepodarilo vybrať pravú časť (teda tú pripájanú, používateľa). Pred spájaním tabuliek je dobré sa zamyslieť, či nastane prípad, keď sa spojenie nepodarí, a čo v takom prípade chceme robiť. Pri článku by sa toto v reále stať asi nemalo.

RIGHT OUTER JOIN

Podobne ako ľavý vonkajší JOIN uznal väzbu v prípade, že ľavá časť existovala, pravý JOIN to urobí naopak. Ak bude existovať používateľ (pravá, pripájaná časť) a nebude k nemu existovať článok (ľavá časť), bude aj tak v tabuľke zahrnutý. Osobne som tento JOIN ešte nepoužil. V tabuľke jedného takého používateľa máme, je ním používateľka Ema. Skúsme si teda RIGHT JOIN:

SELECT [Titulok], [Prezyvka]
FROM [Clanky]
RIGHT JOIN [Pouzivatelia] ON [Clanky].[AutorId] = [Pouzivatelia].[Id]
ORDER BY [Prezyvka];

Výsledok:

Titulok	Prezyvka
NULL	Ema
Baktérie	Ján
Pacman	Ján
Algoritmus	Michaela
Cheese Mouse	Tomáš

Podľa očakávania zmizol Článok bez autora a objavila sa Ema.

Ešte nejaké JOINy by sme určite v MS-SQL našli, ale na naše účely nám toto úplne stačí.

Wherovanie

Teoreticky sa môžeme JOINom vyhýbať a používať namiesto nich jednoducho len klauzulu FROM a WHERE. Vo FROM uvedieme viac tabuliek oddelených čiarkami. Vo WHERE špecifikujeme podmienku spojenia tabuliek. Databáza si v ideálnom prípade takýto dotaz najprv prevedie na INNER JOIN a potom ho spracuje:

SELECT [Titulok], [Prezyvka]
FROM [Clanky], [Pouzivatelia]
WHERE [Clanky].[AutorId] = [Pouzivatelia].[Id]
ORDER BY [Prezyvka];

Výsledok je teda rovnaký ako pri INNER JOINe:

Titulok	Prezyvka
Baktérie	Ján
Pacman	Ján
Algoritmus	Michaela
Cheese Mouse	Tomáš

Nevýhoda wherovania je, že tak neurobíme všetky JOINy a v určitých prípadoch môžu byť dotazy menej optimalizované. Nikdy nevieme, ako dotaz databáza optimalizuje a optimalizácia sa bude líšiť podľa typu databázy.

Tento spôsob berme skôr ako zaujímavosť a nepoužívajme ho.

V nasledujúcej lekcii, MS-SQL krok za krokom: Ďalšie dopyty a väzba M:N, budeme pokračovať v dopytoch cez viac tabuliek a pridáme si do našej databázy ďalšiu časť redakčného systému.