Záludnosti spojování

Spojení jsou mocná, a proto jsou jejich chyby nebezpečné: většina z nich neskončí chybou, jen dá špatný výsledek, který vypadá věrohodně. Tady jsou tři nejčastější.

1. Zapomenuté ON. SQLite vás nechá napsat JOIN bez podmínky. Pak nic neříká, které řádky k sobě patří, a tak spáruje každý řádek levé tabulky s každým řádkem pravé:

SELECT COUNT(*) AS pocet_radku
FROM zakaznici AS z
JOIN objednavky AS o;

14 zákazníků krát 30 objednávek: 420 řádků, všechny možné kombinace. Takovému spojení se říká kartézský součin (anglicky cross join). Někdy je to přesně to, co chcete (třeba každý zákazník s každou hrou), ale obvykle je to zapomenuté ON. Když má výsledek mnohem víc řádků, než jste čekali, zkontrolujte ON u každého spojení. Můžete se setkat i se starším způsobem spojování, kdy jsou tabulky oddělené čárkami a podmínka je ve WHERE: FROM zakaznici z, objednavky o WHERE o.zakaznik_id = z.id. Když tam zapomenete WHERE, dostanete stejných 420 řádků.

2. Řádky, které se násobí. Spojení dá řádek pro každou dvojici. Když ke hrám připojíte dvě tabulky najednou, třeba recenze a položky objednávek, spáruje se každá recenze hry s každou její položkou. Dragon Forge má 4 recenze a byla ve 4 objednávkách:

SELECT h.nazev, COUNT(r.hvezdicky) AS pocet_recenzi, SUM(p.pocet) AS kusy
FROM hry AS h
JOIN recenze AS r ON r.hra_id = h.id
JOIN polozky AS p ON p.hra_id = h.id
WHERE h.id = 1
GROUP BY h.id, h.nazev;

16 recenzí a 16 kusů, čtyřikrát víc, než je pravda: každá ze 4 recenzí se potkala s každou ze 4 položek, a to je 16 řádků. Nic vám neřekne, že je něco špatně, čísla jsou prostě moc velká. Počet se dá zachránit pomocí COUNT(DISTINCT …) klíče: každá recenze patří jednomu zákazníkovi (klíč tabulky recenze je hra a zákazník), takže COUNT(DISTINCT r.zakaznik_id) započítá každou recenzi jednou, bez ohledu na to, kolikrát se její řádek opakoval. Součet se takhle zachránit nedá, protože dvě různé položky můžou mít stejný počet kusů. Spočítejte ho samostatným dotazem, nebo poddotazem, který se naučíte v příští lekci.

3. Podmínka v ON, nebo ve WHERE. U JOIN nezáleží na tom, jestli je podmínka v ON, nebo ve WHERE. U LEFT JOIN ano. Kolik pětihvězdičkových recenzí má která hra?

SELECT h.nazev, COUNT(r.hvezdicky) AS pet_hvezdicek
FROM hry AS h
LEFT JOIN recenze AS r ON r.hra_id = h.id
WHERE r.hvezdicky = 5
GROUP BY h.id, h.nazev
ORDER BY h.nazev;

Hry bez pětihvězdičkové recenze chybí, přestože právě kvůli nim tam LEFT JOIN je. WHERE se provádí až po spojení: řádky s NULL ani řádky ostatních recenzí podmínkou r.hvezdicky = 5 neprojdou a WHERE je vyhodí, i s jejich hrami. Podmínka patří do ON:

SELECT h.nazev, COUNT(r.hvezdicky) AS pet_hvezdicek
FROM hry AS h
LEFT JOIN recenze AS r ON r.hra_id = h.id AND r.hvezdicky = 5
GROUP BY h.id, h.nazev
ORDER BY h.nazev;

Podmínka v ON rozhoduje, které řádky pravé tabulky se počítají jako protějšek, a LEFT JOIN si každý řádek levé tabulky nechá tak jako tak: hra bez pětihvězdičkové recenze zůstane, s hodnotami NULL, a COUNT jí dá 0. U LEFT JOIN tedy podmínka na pravou tabulku patří do ON a podmínka na levou tabulku do WHERE.