Šuplík Honzy Hučína

Spočítání neexistujících záznamů

Opět jsem řešil jeden drobný úkol v SQL a rozhodl jsem se s vámi o něj podělit. Podobně jako u předchozího článku očekávám, že databázoví profíci to znají. Ale třeba to pomůže některým méně zkušeným.

Máme dvě tabulky, řekněme ctenari a vypujcky. V tabulce ctenari jsou evidováni registrovaní čtenáři knihovny (co řádek, to jedna osoba) a má mimo jiné pole id_ctenar a jmeno. V tabulce vypujcky máme zaznamenány výpůjčku knihy čtenářem (co řádek, to jedna kniha půjčená jedné osobě) – obsahuje mimo jiné pole id_ctenar a kniha. Naším úkolem je vypsat u každého registrovaného čtenáře počet výpůjček.

Zdá se to být triviální. Jen spojíme tabulky přes pole id_ctenar, seskupíme podle tohoto pole a spočítáme počet takto propojených řádků:

select jmeno, count(*) as pocet
from ctenari join vypujcky on ctenari.id_ctenar=vypujcky.id_ctenar
group by ctenari.id_ctenar;

Jenže ouha. Takto nebudeme mít ve výstupu čtenáře, kteří si nevypůjčili žádnou knihu, protože ti v tabulce vypujcky nemají žádný záznam. A tudíž je klauzule JOIN nespojí.

Řešením je propojení tabulek pomocí LEFT JOIN. Toto spojení zajistí, že ve výstupu budou zahrnuty všechny záznamy z tabulky ctenari – pokud čtenář nemá žádný záznam v tabulce vypujcky, bude mít ve výstupu pouze jeden řádek a záznamy z tabulky vypujcky u něho budou mít hodnotu NULL.

Tedy takto?

select jmeno, count(*) as pocet
from ctenari left join vypujcky on ctenari.id_ctenar=vypujcky.id_ctenar
group by ctenari.id_ctenar;

To nám sice zahrne všechny registrované čtenáře, ale u každého, kdo si nevypůjčil žádnou knihu, bude pole pocet mít hodnotu 1. A to z toho důvodu, že spojení tabulek za něho poskytlo právě jeden řádek, i když s hodnotami NULL – funkce count počítá počet záznamů, na hodnotu nehledí.

Aby registrovaní čtenáři bez výpůjček měli nikoliv jedničku, ale očekávanou nulu, můžeme vzít v úvahu i pole s hodnotami NULL. Pomocí funkce Isnull() zjistíme, zda je hodnota NULL; operátorem Not to převedeme na zjištění, zda je hodnota různá od NULL; nakonec logické jedničky a nuly sečteme funkcí sum. A to už funguje.

select jmeno, sum(not(isnull(kniha))) as pocet
from ctenari left join vypujcky on ctenari.id_ctenar=vypujcky.id_ctenar
group by ctenari.id_ctenar;

Zdá se vám to komplikované? V tom případě je zde jiné řešení. Můžeme se vrátit na začátek k dotazu, který počítal počty výpůjček jen za čtenáře, kteří měli aspoň jednu. K tomuto dotazu přidáme pomocí sjednocovacího operátoru UNION výpis všech čtenářů, kteří si nevypůjčili nic – ty poznáme tak, že při spojení LEFT JOIN mají v poli kniha hodnotu NULL. Sjednocovací dotaz tak dává požadovaný přehled všech čtenářů a počtu výpůjček včetně nulových hodnot.

select jmeno, count(*) as pocet
from ctenari join vypujcky on ctenari.id_ctenar=vypujcky.id_ctenar
group by ctenari.id_ctenar
union
select jmeno, 0 as pocet
from ctenari left join vypujcky on ctenari.id_ctenar=vypujcky.id_ctenar
where kniha is null;