Dnes si na praktickém příkladu ukážeme, jak funguje množinový operátor UNION.

Naším cílem bude vypsat si seznam uživatelů, kteří vložili komentář na našem serveru a odkaz na jejich stránky. Na serveru používáme redakční systém Drupal a z jeho databáze získáváme data. Pracovat budeme nad databází MySQL.

Úkol je to dost jednoduchý, ale my musíme zohlednit rozdíl mezi neregistrovanými a registrovanými uživateli.

V případě neregistrovaných uživatelů je odkaz na jejich stránky uložen přímo v tabulce komentářů, zatímco registrovaní uživatelé mají odkaz na stránky ve svém profilu – tedy v jiné tabulce.

SELECT  komentatori.*,
	count(*) AS pocet
FROM (
    SELECT  name AS jmeno,
	    homepage AS url
    FROM    comments
    WHERE   uid = 0
    UNION ALL
    SELECT  name AS jmeno,
	    p.value AS url
    FROM    comments AS c
    JOIN    profile_values AS p ON (c.uid = p.uid AND p.fid = 1)
    WHERE   c.uid <> 0
) AS komentatori
GROUP BY jmeno, url
ORDER BY count(*) DESC
LIMIT 10;

UNION slouží ke sjednocení množin záznamů. Vyzkoušet si ho můžete online v demoverzi programu SQL Výuka. Záznamy ze všech množin musí mít stejný počet sloupečků (a slučitelných typů).

Všimněte si dvou variant: UNION a UNION ALL – při použití samotného UNIONu získáme jedinečné záznamy (což se nám nehodí, protože se nám tím ztratí informace o počtu jeho komentářů). Použijeme tedy UNION ALL, který při sjednocení množin zachovává vícenásobné výskyty stejných záznamů.

V příkladu jsem pomocí UNION ALL sjednotili dva poddotazy:

1. první vybírá jména a url nepřihlášených uživatelů (nepřihlášení komentátoři mají uživatelské ID = 0)
2. a druhý jména a url přihlášených – v tomto případě je potřeba získat url z druhé tabulky (připojené pomocí JOIN).
   fid = 1
   zde znamená, že url je uloženo v první položce profilu (bude se lišit podle nastavení našeho Drupalu).

Na závěr tyto dva poddotazy obalíme ještě jedním SELECTem, kterým docílíme seřazení podle počtu komentářů daného autora (ORDER BY a GROUP BY).

Optimalizace

Zapomeňme teď na UNION a udělejme to, co bychom měli udělat po napsání každého SQL dotaz – pokusme se o optimalizaci. Nejde tenhle SELECT napsat jinak, úsporněji?

Co takhle korelovaný vnořený dotaz?

SELECT  komentatori.*,
	count(*) AS pocet
FROM (
    SELECT  name AS jmeno,
    	    CASE
	    	WHEN c.uid = 0 THEN homepage
		ELSE (SELECT p.value FROM profile_values AS p WHERE c.uid = p.uid AND p.fid = 1)
	    END AS url    
    FROM    comments AS c
) AS komentatori
GROUP BY jmeno, url
ORDER BY count(*) DESC
LIMIT 10;

Napsali jsme SELECT jinak a dává stejné výsledky. Ten, kdo si pod optimalizací představuje snižování počtu řádků kódu, by z něj měl radost. To bychom tomu ale dali! Korelovaný vnořený dotaz (za ELSE) se totiž vykonává pro každý záznam tabulky komentářů, kde uid je různé od nuly. Toto řešení je tedy značně neefektivní.

Zkusme to jinak – LEFT JOIN a coalesce()

SELECT  komentatori.*,
	count(*) AS pocet
FROM (
    SELECT  name AS jmeno,
	     coalesce(p.value, homepage) AS url
    FROM    comments AS c
    LEFT JOIN    profile_values AS p ON (c.uid = p.uid AND p.fid = 1)
) AS komentatori
GROUP BY jmeno, url
ORDER BY count(*) DESC
LIMIT 10;

Zbavili jsme se UNIONu a připojili jsme si k tabulce komentářů tabulku hodnot profilu. Museli jsme použít levý JOIN, protože jinak by nám zde chyběli komentáře, které napsal nepřihlášený uživatel.

Pomocí funkce coalesce() získáme první (p.value – url přihlášeného uživatele z profilu) nebo druhý parametr (homepage – url nepřihlášeného uživatele z tabulky komentářů) pokud je první nulový.

Funkci můžeme zadat i více než dva parametry – výsledkem je první nenulový z nich:

SELECT coalesce(null, 1, 2, 3);

Funkce coalesce() je standardní (ANSI 92), ale v Oraclu máme k dispozici i velmi podobnou funkci nvl(). Ta je poněkud zastaralá a má následující nevýhody:

• může mít jen dva parametry
• vyhodnocuje druhý parametr i když je první nenulový (tedy zbytečně).

Více o problému nvl() vs. coalesce() si můžete přečíst na Slashdotu.

Závěr

Ukázali jsme si tři SELECty, které dávají stejný výsledek. Jeden těchto dotazů je ale vyloženě špatný – pomalý (vnořený korelovaný poddotaz).