9. diel - MS-SQL krok za krokom: Otázky 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 dopytovanie cez viacero tabuliek.

Konceptuálny model

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

Čo vidíte je tzv. konceptuálny model. Je vytvorený pomocou notácie (grafického jazyka) UML av praxi sa takéto diagramy veľmi často tvoria predtým, než začneme písať nejaký kód. Dobre si tak najskôr rozmyslíme, čo že to 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ážete predstaviť.

Príprava tabuliek a dát

Dnes sa zameriame na otázky cez viacero tabuliek. Poďme si najskôr nejaké tabuľky vytvoriť. Bohato nám budú stačiť Users a Articles.

Užívatelia

Pretože užívateľ bude vyzerať trochu inak, než nám vyzeral doteraz, založíme si tabuľku Users znova. Tú súčasnú teda dropneme:

DROP TABLE [Users];

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

CREATE TABLE [Users] (
    [Id] INT IDENTITY,
    [Nickname] NVARCHAR(155),
    [Email] NVARCHAR(155),
    [Password] NVARCHAR(255),
    PRIMARY KEY ([Id])
);

Pozn.: Môže chvíľu trvať, než sa nová tabuľka v Server Exploreri zobrazí.

Do užívateľov si rovno nejaké vložíme:

INSERT INTO [Users] ([Nickname], [Email], [Password]) VALUES
('Michael', '[email protected]', 'dGg#@$DetA53d'),
('David', '[email protected]', '$#fdfgfHBKBKS'),
('Denny', '[email protected]', 'Jmls_aSW2RFss'),
('Emma', '[email protected]', 'fw8QT32qmcsld');

Články

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

Hovoríme o väzbe 1:N (1 užívateľ má N (niekoľko) článkov a každý článok patrí práve jednému užívateľovi). Časť (tu článok) má vždy uložené Id celku (tu uží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 Articles:

CREATE TABLE [Articles](
    [Id] INT IDENTITY,
    [AuthorId] INT,
    [Description] NVARCHAR(155),
    [Url] NVARCHAR(155),
    [KeyWords] NVARCHAR(155),
    [Title] NVARCHAR(155),
    [Content] NVARCHAR(MAX),
    [Published] DATETIME,
    PRIMARY KEY ([Id])
);

Za povšimnutie stojí asi len použitie typu NVARCHAR(MAX) pre text článku.

Ďalej pridáme články ak nim priradíme užívateľov ako autorov. Vzal som 4 články tu z ITnetwork, ktoré som značne skrátil a zjednodušil. Otázka bude nasledujúca:

INSERT INTO [Articles] ([AuthorId], [Description], [Url], [KeyWords], [Title], [Content], [Published]) VALUES
(1, 'What is an algorithm? If you do not know, read this article.', 'what-is-an-algorithm', 'algorithm, what is it, explanation', 'Algorithm', 'So while we are talking about algorithms, lets agree on what an algorithm is. Simply put, an algorithm is a guide to solving a problem. If we look at it from a human point of view, an algorithm could be, for example, instructions on how to get up in the morning. Although it sounds simple, it is quite a problem. Computers are machines and they do not think. So we have to describe all the steps of the algorithm in detail. This brings us to the first characteristic of an algorithm - it must be elementary (consist of a finite number of simple and easily understandable steps, i.e. commands). "Get out of bed" is definitely not an algorithm. "Open your eyes, take off the duvet, stretch yourself, put your feet on the ground and stand up" - that already sounds quite detailed and would therefore be a real algorithm. However, we will be moving in IT, so we will solve problems like sort elements by size or find an element by its content. These are the 2 basic tasks that computers do most often and which need to be perfectly thought out and optimized so that they last as little time as possible. Among other examples of algorithms, I can think of solving a quadratic equation or solving Sudoku.', '2012-3-21'),
(2, 'Bacteria are like a cellular automaton combined with a game.', 'bacteria-cell-automatic machine', 'bacteria, automaton, algorithm', '
Bacteria', 'Bacteria are similar to the cellular automaton invented by the British mathematician John Horton Conway in 1970. The whole game is governed by four simple rules:/n/n
1. A living bacterium with less than two living neighbors dies./n
2. A living bacterium with more than three living neighbors dies of overpopulation./n
3. A living bacterium with two or three neighbors survives unchanged into the next generation./n
4. A dead bacterium, with exactly three living neighbors, comes back to life./n
These seemingly completely primitive rules can create marching groups, clusters "shooting" marching fives, surprisingly complex symmetrical explosions, oscillators (periodically oscillating groups), or an endless spectacle of how complex and perfect shapes these two conditions can create with the correct initial distribution of bacteria . The whole program is conceived as a game, you have the task of creating a colony that lives as long as possible. <a href="files/bacteria.zip"', '2012-2-14'),
(3, 'Cheese Mouse is a relaxing platformer.', 'cheese-mouse-relaxation-plosinovka', 'mouse, cheese, game', 'Cheese Mouse', 'Cheese mouse is a platform game with a "hot island atmosphere" where you control the mouse and have to get to the cheese. However, various traps and enemies such as snakes, rats, piranhas, as well as robots, mummies and various vermin prevent you from doing so. I made a game with several petro-colored worlds back in elementary school with Veisen, and it can boast the 2nd place in the Bonusweb game competition, where it won 5,000 CZK. It was created in Game Maker during the summer holidays, still in a carefree childhood, which greatly influenced its graphic side. I like to play it sometimes to relax and improve my mood. <a href="files/cheesemouse.zip" />', '2004-6-22'),
(2, 'Pacman is a remake of the iconic game.', 'pacman-remake', 'pacman, remake, pampuch, game, free', 'Pacman', 'This is a very basic version of this game with a level editor so you can create your own wheels. Over time, I intend to modify it a little more and add some new elements, fullscreen and better graphics. The game engine will also be the basis of my new project Geckon man, which is currently in the scripting stage. <a href="files/pacman.zip" />', '2011-6-03');

Otázky cez viac tabuliek

Teraz máme v databáze články ak nim priradených užívateľov. Poďme si urobiť otázku cez tieto 2 tabuľky, získajme články ak nim pripojme prezývky ich užívateľov. Slovo pripojme som nepoužil náhodou, príkaz pre spojenie 2 tabuliek sa totiž volá JOIN. Napíšme si otázku a potom si ju vysvetlíme. Otázky už budeme písať na viac riadkov, aby sme sa v tom vyznali.

SELECT [Title], [Nickname]
FROM [Articles]
JOIN [Users] ON [Articles].[AuthorId] = [Users].[Id]
ORDER BY [Nickname];

Výsledok:

Title	Nickname
Bacteria	David
Pacman	David
Cheese Mouse	Denny
Algorithm	Michael

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 ak nim pripájame užívateľov, budeme vyberať z tabuľky Articles. Pripojenie dát z inej tabuľky urobíme pomocou príkazu JOIN, kde uvedieme tabuľku, ktorú pripájame, a potom klauzulu ON. Pokiaľ majú v dvoch tabuľkách stĺpce rovnaké názvy, predsadíme stĺpec ešte názvom tabuľky, do ktorej patria 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 uží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šie) 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

Pokiaľ uvedieme v SQL dotaze iba JOIN, pokladá ho MS-SQL databáza za tzv. INNER JOIN. Pokiaľ by v našom prípade neexistoval užívateľ s Id, ktoré je pri článku uvedené, článok bez uží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 [Articles] ([AuthorId], [Description], [Url], [KeyWords], [Title], [Content], [Published]) VALUES
(99, 'The article with a non-existent user is used to test the types of JOINs.', 'article-without-author', 'article, join, author, missing', 'Article without author', 'This article is assigned to a non-existent user with ID 99 and serves to test different types of JOINs in an MS-SQL database.', '2012-10-21');

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

SELECT [Title], [Nickname]
FROM [Articles]
INNER JOIN [Users] ON [Articles].[AuthorId] = [Users].[Id]
ORDER BY [Nickname];

Výsledok:

Title	Nickname
Bacteria	David
Pacman	David
Cheese Mouse	Denny
Algorithm	Michael

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á, pokiaľ existuje ľavá časť väzby (tu článok) a pravá (tá pripájaná, tu užívateľ) neexistuje. Do hodnôt stĺpcov z pripájanej časti sa vloží NULL.

SELECT [Title], [Nickname]
FROM [Articles]
LEFT JOIN [Users] ON [Articles].[AuthorId] = [Users].[Id]
ORDER BY [Nickname];

Výsledok:

Title	Nickname
Article without author	NULL
Bacteria	David
Pacman	David
Cheese Mouse	Denny
Algorithm	Michael

Vidíme, že článok sa rovnako 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 tom prípade chceme robiť. Pri článku by sa toto v reáli 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. Pokiaľ bude existovať užívateľ (pravá, pripájaná časť) a nebude k nemu existovať článok (ľavá časť), bude rovnako v tabuľke zahrnutý. Osobne som tento JOIN ešte nepoužil. V tabuľke jedného takéhoto užívateľa máme, je ním užívateľ Ema. Skúsme si teda RIGHT JOIN:

SELECT [Title], [Nickname]
FROM [Articles]
RIGHT JOIN [Users] ON [Articles].[AuthorId] = [Users].[Id]
ORDER BY [Nickname];

Výsledok:

Title	Nickname
Bacteria	David
Pacman	David
Cheese Mouse	Denny
NULL	Emma
Algorithm	Michael

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

Ešte nejaké JOINy by sme určite v MS-SQL našli, ale na naše účely nám toto bohato 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 otázku najprv prevedie na INNER JOIN a potom ju spracuje.

SELECT [Title], [Nickname]
FROM [Articles], [Users]
WHERE [Articles].[AuthorId] = [Users].[Id]
ORDER BY [Nickname];

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

Title	Nickname
Bacteria	David
Pacman	David
Cheese Mouse	Denny
Algorithm	Michael

Nevýhoda wherovania je, že tak neurobíme všetky JOINy av určitých prípadoch môžu byť otázky menej optimalizované. Nikdy nevieme, ako dopyt databázy optimalizuje a optimalizácia sa bude líšiť podľa typu databázy. Tento spôsob berte skôr ako zaujímavosť a nepoužívajte ho.

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