Praktické využití UNION a JOIN v MySQL

V tomto příspěvku naleznete dvě ukázky použití příkazů UNION a JOIN spolu s grafickým vyobrazením jak chápat variace JOIN příkazu.

UNION – navzájem neprovázané tabulky

Možnost praktického využití příkazu UNION pro sjednocení výsledků ze dvou a více tabulek jsem nechápal až do objevení následujícího postupu. Trik spočívá v použití NULL, jinak by všechny tabulky musely splňovat nereálné předpoklady (např. stejný počet sloupců). Výhodou sjednocení je možnost načíst všechna data v jednom příkazu a přitom nemusíme znát žádný společný klíč.

SELECT * FROM (
             (SELECT  NULL AS xp, NULL AS xp_za_co, hr_kredity.cr, hr_kredity.typ, hr_kredity.za_co, hr_kredity.kdy_ziskano 
              FROM hr_kredity WHERE username ='$uname')
         UNION ALL
             (SELECT hr_xp.xp, hr_xp.za_co, NULL AS cr, NULL AS typ, NULL AS cr_za_co, hr_xp.kdy_ziskano 
              FROM hr_xp WHERE username ='$uname')
         ) RESULTS ORDER BY kdy_ziskano DESC LIMIT 6

SELECT * FROM (

(SELECT NULL AS xp, NULL AS xp_za_co, hr_kredity.cr, hr_kredity.typ, hr_kredity.za_co, hr_kredity.kdy_ziskano

FROM hr_kredity WHERE username ='$uname')

UNION ALL

(SELECT hr_xp.xp, hr_xp.za_co, NULL AS cr, NULL AS typ, NULL AS cr_za_co, hr_xp.kdy_ziskano

FROM hr_xp WHERE username ='$uname')

) RESULTS ORDER BY kdy_ziskano DESC LIMIT 6

Nutnost vkládat NULL a vytvářet tím „stínové sloupce“ může být zdrojem chyb při pozdějších úpravách kódu.
Podobně jako UNION (sjednocení) se používají množinové operátory INTERSECT (průnik) a EXCEPT (rozdíl).

Data by šla zpracovat v php například takto:

list($xp, $xp_za_co, $cr, $typ, $cr_za_co_full, $kdy_ziskano ) = mysqli_fetch_array($dotaz);

1	list($xp, $xp_za_co, $cr, $typ, $cr_za_co_full, $kdy_ziskano ) = mysqli_fetch_array($dotaz);

nebo

$row = mysqli_fetch_assoc($dotaz);

1	$row = mysqli_fetch_assoc($dotaz);

(data pak máme pod indexy $row[‚xp‘], $row[‚xp_za_co‘], … )

JOIN – Sjednocení dotazů na provázané tabulky

Daleko běžnější, než výše uvedené UNION ALL dotazy, jsou dotazy s JOIN, kdy dvě nebo více tabulek mají společný klíč nebo klíče, které definují provázanost (či chcete-li – vzájemné vztahy). Jenomže dotazy s JOIN jsou zejména pro začínající programátory vyloženě databázovým peklem. Jak je nejsnáze pochopit? Vizuálně! Zjistíte, že množiny v první třídě ZŠ přece jenom nebyly úplně k ničemu…

Mějme tři provázané tabulky:

tabulka objednavka

id	id_zakaznik	id_produkt
1	1	1
2	2	1
3	1	2

tabulka zakaznik

id	jmeno
1	Eva Pilná
2	David Goldman

tabulka produkt

id	nazev
1	Mazda CX-7
2	Hyundai ix55
3	Škoda Yeti

INNER JOIN

Chceme například rozklíčovat tabulku objednavka:

SELECT objednavka.id, zakaznik.jmeno, produkt.nazev
FROM objednavka
INNER JOIN zakaznik ON objednavka.id_zakaznik = zakaznik.id
INNER JOIN produkt ON objednavka.id_produkt = produkt.id;

INNER JOIN jednoduše zahrnuje průnik množin. Na obrázku vpravo je screenshot výsledku dotazu.
INNER JOIN se ze všech variací JOIN používá zdaleka nejčastěji.

RIGHT JOIN

Co kdybychom však chtěli mít v přehledu i produkty, které nikdo nezakoupil? Zde by stačilo u druhého JOIN vyměnit INNER za RIGHT a díky tomu se vypíší VŠECHNY řádky z pravé tabulky bez ohledu na propojení.

SELECT objednavka.id, zakaznik.jmeno, produkt.nazev
FROM objednavka
INNER JOIN zakaznik ON objednavka.id_zakaznik = zakaznik.id
RIGHT JOIN produkt ON objednavka.id_produkt = produkt.id;

LEFT JOIN

LEFT JOIN vrací výsledek z celé levé tabulky bez ohledu na propojení.

Praktické využití LEFT JOIN pro výše uvedené tabulky mne však nenapadá.

JOIN V PRAXI

Kdybyste vyzkoušeli některé z výše uvedených příkladů na konkrétních tabulkách, došlo by vám záhy, že s tím nelze vystačit. Pokusíte se tedy upřesnit dotazy pomocí WHERE a dalších příkazů, ale většinou to bude hlásit chybu. Záleží totiž na pořadí. Pro zjednodušení použijeme zkrácený zápis tabulek (t, ci):

SELECT t.id, t.order_status, t.billing_name, t.billing_surname, t.order_at, ci.expressPrint  FROM tbl_order t 
    JOIN tbl_cart_item ci ON t.cart_id = ci.cart_id 
    WHERE (t.order_amount > 0 AND t.order_status < 120) 
    GROUP BY t.billing_name, t.billing_surname
    ORDER BY t.id DESC

SELECT t.id, t.order_status, t.billing_name, t.billing_surname, t.order_at, ci.expressPrint FROM tbl_order t

JOIN tbl_cart_item ci ON t.cart_id = ci.cart_id

WHERE (t.order_amount > 0 AND t.order_status < 120)

GROUP BY t.billing_name, t.billing_surname

ORDER BY t.id DESC

SELECT _ FROM tabulka1
JOIN tabulka2 ON _
WHERE _
GROUP BY _ (seskupit, aby se neopakovaly záznamy ve výpisu)
ORDER BY _ (třídit, aby se záznamy zobrazily přehledněji)

Čím více tabulek je ve hře (tzn. když potřebuji získat data z více tabulek), tím více JOIN se objevuje v dotazu na databázi:

    SELECT ci.id, p.sku, pi.image_url, f.nazev, fc.color_code,   
           item_price, quantity, personalization, completion, expressPrint 
        FROM tbl_cart_item ci
    JOIN tbl_product p ON p.id = ci.product_id
    JOIN tbl_product_image pi ON pi.product_id = ci.product_id
    JOIN tbl_font f ON f.id = ci.font_id
    JOIN tbl_font_color fc ON fc.id = ci.font_color_id
    WHERE ci.cart_id = $cart_id

SELECT ci.id, p.sku, pi.image_url, f.nazev, fc.color_code,

item_price, quantity, personalization, completion, expressPrint

FROM tbl_cart_item ci

JOIN tbl_product p ON p.id = ci.product_id

JOIN tbl_product_image pi ON pi.product_id = ci.product_id

JOIN tbl_font f ON f.id = ci.font_id

JOIN tbl_font_color fc ON fc.id = ci.font_color_id

WHERE ci.cart_id = $cart_id

Mohlo by vás zajímat:

mySQL - ON DELETE CASCADE

MySQL - složené dotazy na více tabulek najednou

Procedurální komunikace s databází v php

PHP Developer