SQL a příkaz LEFT JOIN

Obsah lekce:

LEFT JOIN

V této lekci se naučíme pokročilejší použití SQL dotazu SELECT s použitím propojení tabulek pomocí příkazu LEFT JOIN. SQL příkaz JOIN používáme pro získání dat ze dvou a více tabulek, které mají mezi sebou nějaký vztah - tzv. relaci.

Pro tuto lekci použijeme stejnou databázi, jako v předchozí lekci, kterou však musíme rozšířit o další tabulky, konkrétně renome_ccathegory a renome_treporter. Celou databázi si můžete stáhnout v souboru databaze.txt.

Struktura tabulky renome_ccathegory

V této lekci budeme realizovat několik SQL dotazů nad databází online studentských novin. První z tabulek, se kterou budeme propojovat databázi článků (renome_tarticle), se jmenuje renome_ccathegory. Tabulka obsahuje seznam kategorií článků a má, mimo jiné, následující atributy (neuvádíme všechy atributy, ale pouze ty podstatné pro naše cvičení):

• idtarticle - primární klíč tabulky - obsahuje číselnou hodnotu udávající identifikátor každého řádku (hodnota má vlastnost auto_increment pro automatické vložení o jedničku většího čísla než má poslední vložený záznam při vložení záznamu)
• name - název kategorie
• mysort - tento atribut udává pozici kategorie v menu stránek

Pro lepší představu slouží následující obrázek se strukturou tabulky.

Struktura tabulky renome_treporter

Druhou tabulkou, se kterou budeme propojovat databázi článků (renome_tarticle), se jmenuje renome_treporter. Tabulka obsahuje seznam autorů článků a má, mimo jiné, následující atributy (neuvádíme všechy atributy, ale pouze ty podstatné pro naše cvičení):

• idcreporter - primární klíč tabulky - obsahuje číselnou hodnotu udávající identifikátor každého řádku (hodnota má vlastnost auto_increment pro automatické vložení o jedničku většího čísla než má poslední vložený záznam při vložení záznamu)
• name - jméno
• surname - příjmení

Pro lepší představu slouží následující obrázek se strukturou tabulky.

Vztah mezi tabulkami

Pokud propojujeme v databázi více tabulek, je nutné určit, jak jsou propojeny, neboli jejich vztah - relaci. Pro relaci musí mít obě tabulky společnou hodnotu, přes kterou budou propojeny.

Tabulka renome_tarticle tedy obsahuje veškeré články a ve sloupci idccathegory číslo kategorie, do které patří. V tabulce renome_ccathegory jsou těmto číslům přiřazeny ve sloupci name názvy kategorie.

Dále tabulka renome_tarticle obsahuje ve sloupci idcreporter číslo autora článku. Seznam autorů článků se nachází v tabulce renome_treporter, kde jsou každému číslu autora přiřazeny jeho osobní údaje

Vztah mezi tabulkami v naší databázi popisuje následující obrázek.

Pokud tedy chceme z databáze například zjistit, jak se jmenuje kategorie, do které je tento článek zařazen, musíme příkazem SELECT získat data z obou tabulek. Z tabulky renome_tarticle vezmeme nadpis článku a z tabulky renome_ccathegory název kategorie. A k tomuto právě slouží příkazy JOIN.

Typy příkazu JOIN

V SQL rozlišujeme několik druhů příkazu JOIN. Mezi nejpoužívanější patří následující:

• INNER JOIN - tento příkaz vrátí řádky, pouze pokud je shoda v obou tabulkách
• LEFT JOIN - tento příkaz vrátí všechny řádky z tabulky, která je v zápise příkazu vlevo, i pokud tam není žádná shoda s pravou tabulkou
• RIGHT JOIN - tento příkaz vrátí všechny řádky z tabulky, která je v zápise příkazu vpravo, i pokud tam není žádná shoda s levou tabulkou

Základní princip jednotlivých příkazů JOIN si nejprve vysvětlíme na databázi menšího rozsahu (ke stažení zde), ve které jsou údaje o studentech a volitelných předmětech. Následně bude lekce věnována již pouze příkazu LEFT JOIN a jeho aplikaci na naši databázi studentských novin.

Naše databáze volitelných předmětu se skládá ze dvou tabulek. Její struktura je zobrazena na následujícím obrázku.

INNER JOIN

Prvním typem příkazu JOIN je příkaz INNER JOIN. Tento příkaz propojuje dvě tabulky tak, že zobrazí data pouze při shodě v obou tabulkách. V našem případě, kdy ke zkratce volitelného předmětu u studenta přiřazujeme jeho název, se zobrazí pouze ti studenti, kteří mají vybrán volitelný předmět a tento volitelný předmět existuje v tabulce volitelných předmětů.

SELECT
    studenti.id,
    studenti.jmeno, 
    studenti.vp, 
    predmety.nazev
FROM 
    studenti 
INNER JOIN 
    predmety ON studenti.vp = predmety.zkratka

Výsledek našeho dotazu zobrazuje následující obrázek. V tabulce se nezobrazila studentka Jana Nová, protože nemá vybrán žádný volitelný předmět. a student Michal Dvořák, protože má vybrán předmět, jehož zkratka není v tabulce volitelných předmětu.

LEFT JOIN

Dalším typem příkazu JOIN je příkaz LEFT JOIN. Tento příkaz propojuje dvě tabulky tak, že zobrazí všechna data z tabulky umístěné vlevo (před příkazem LEFT JOIN, tj. v našem případě studenti) a k těmto datům přiřadí data z tabulky vpravo. Pokud v této tabulce nejsou nalezena odpovídající data, jsou doplněny prázdné hodnoty (NULL). V našem případě, kdy ke zkratce volitelného předmětu u studenta přiřazujeme jeho název, se zobrazí všichni studenti. U studentů, u kterých není nalezen název předmětu se zobrazí hodnota NULL.

SELECT
    studenti.id,
    studenti.jmeno, 
    studenti.vp, 
    predmety.nazev
FROM 
    studenti 
LEFT JOIN 
    predmety ON studenti.vp = predmety.zkratka

Výsledek dotazu s použtím příkazu LEFT JOIN bude následující:

Použití příkazu LEFT JOIN - cvičení

Několik následujících dotazů dále demonstruje použití příkazu LEFT JOIN. Všechny následující dotazy se již týkají naší původní databáze studentských novin (ke stažení v úvodní části lekce). Výsledky jednotlivých dotazů můžete ověřit jejich provedením nad touto databází.

Dotaz 1

Dotaz vybere nadpisy všech článků, které jsou z kategorie 2 a přiřadí k nim název kategorie.

Pro propojení tabulek slouží příkaz LEFT JOIN. Navíc je použita podmínka WHERE, aby byly zobrazeny pouze články z kategorie č. 2.

SELECT
    renome_tarticle.heading,
    renome_ccathegory.name
FROM 
    renome_ccathegory
LEFT JOIN 
    renome_tarticle ON renome_tarticle.idccathegory = renome_ccathegory.idccathegory
WHERE 
    renome_ccathegory.idccathegory = '2'

Dotaz 2

Dotaz zobrazí nadpis, čas vytvoření, počet shlédnutí a název kategorie článků z kategorií č. 1, 3, 5 setříděné podle čtenosti.

Pro propojení tabulek opět slouží příkaz LEFT JOIN. Oproti předchozímu příkladu je zde rozšířen příkaz WHERE, který slouží pro nastavení podmínky. Protože je třeba vybrat více kategorií, je zde použit logický operátor OR (nebo). Poslední částí dotazu je příkaz ORDER BY, který seřadí data dle požadovaného sloupce (DESC nastavi sestupné řazení).

SELECT
    renome_tarticle.heading, renome_tarticle.createdate, renome_tarticle.viewedcount, renome_ccathegory.name
FROM
    renome_tarticle
LEFT JOIN
    renome_ccathegory ON renome_tarticle.idccathegory = renome_ccathegory.idccathegory
WHERE
    ((renome_tarticle.idccathegory = '1') OR (renome_tarticle.idccathegory = '3')
    OR (renome_tarticle.idccathegory = '5'))
ORDER BY
    renome_tarticle.viewedcount DESC

Dotaz 3

Dotaz zobrazí nadpis, název kategorie článků a celé jméno autora článku.

V tomto dotazu je příkaz LEFT JOIN použit celkem dvakrát. protože pro tabulku renome_tarticle definujeme dvě relace (propojení s tabulkou renome_ccathegory a propojení s tabulkou renome_treporter) dle struktury databáze, která je zobrazena v úvodu lekce). Z tabulky renome_ccathegory je přiřazen název kategorie a z tabulky renome_treporter je přiřazen autor článku.

SELECT
    renome_tarticle.heading, renome_ccathegory.name, renome_treporter.name, renome_treporter.surname
FROM
    renome_tarticle
LEFT JOIN
    renome_ccathegory ON renome_ccathegory.idccathegory = renome_tarticle.idccathegory
LEFT JOIN
    renome_treporter ON renome_treporter.idcreporter = renome_tarticle.idcreporter        

Další čtení

Otázky

1. K čemu slouží v jazyku SQL příkaz JOIN?
2. Popište rozdíl mezi příkazy LEFT a INNER JOIN.