MySQL - podmíněný SELECT přes dvě tabulky

[Filip Jirsák]

Nejsou, SELECT * FROM tbl1 INNER JOIN tbl2 USING (id_data) WHERE tbl2.allowed > 0
vrátí absolutně to samé jako SELECT * FROM tbl1 LEFT JOIN tbl2 USING (id_data) WHERE tbl2.allowed > 0.
To druhé je ale čistší vzhledem k zadanému popisu dat (v pravé tabulce může a nemusí být odpovídající záznam).

Četl jste komentář, na který reagujete? Napíšu o SQL dotazech, ale o tom, co Racchek požaduje.

Vstupní data:

Kód: [Vybrat]

SELECT * FROM tbl1
-------
id_data
-------
100
101
102

SELECT * FROM tbl2
-------
id_data|allowed
100    |0
101    |1

Racchekovo zadání je sporné. Nevíme, zda pro výše uvedený příklad požaduje tuhle sadu výsledků („tbl2.id_data nemusí obsahovat všechny data v tbl1.id_data“):

Kód: [Vybrat]

-------
id_data
-------
101
102

Nebo tuhle sadu výsledků („vybrat pouze to, co v tbl2 je jako allowed >0“):

Kód: [Vybrat]

-------
id_data
-------
101

To první je OUTER JOIN, to druhé je INNER JOIN. Která varianta zápisu příslušného JOINu se použije je detail, podstatné je vědět, co Racchek chce. Už to chápete?

[Reklama]

[Miroslav Šilhavý]

Která varianta zápisu příslušného JOINu se použije je detail, podstatné je vědět, co Racchek chce. Už to chápete?

Ano, já to chápu celou dobu, ale v obou případech je vhodnější použít LEFT OUTER JOIN, protože to odpovídá struktuře dat.

Tj. oba dotazy bych psal takto:
SELECT * FROM tbl1 LEFT JOIN tbl2 USING (id_data) WHERE tbl2.allowed > 0
nebo
SELECT * FROM tbl1 LEFT JOIN tbl2 USING (id_data) WHERE tbl2.allowed > 0 OR tbl2.allowed IS NULL

Nechť si Racchek vybere podle požadavku, ale ať tam proboha necpe INNER JOIN, když tomu struktura neodpovídá. Myslím, že je důležité si utvořit správné návyky a určitou štábní kulturu.

[Filip Jirsák]

Nechť si Racchek vybere podle požadavku, ale ať tam proboha necpe INNER JOIN, když tomu struktura neodpovídá. Myslím, že je důležité si utvořit správné návyky a určitou štábní kulturu.

Ano, je důležité si utvořit správné návyky a určitou štábní kulturu. Takže pokud Racchek má požadavek vybrat z tbl1 ty záznamy, které jsou v tbl2 a mají tam allowed > 0 má krystalicky čistý učebnicový příklad na INNER JOIN, tak by ho tak také měl napsat. Tomu vašemu příkladu s INNER JOINem psaným pomocí OUTER JOINu, který je navíc špatně, ať se zdaleka vyhne. (Ten váš příklad by vyžadoval splnění jednoho předpokladu, který ale v zadání uveden není.)

Vážně by mne zajímalo, jakou ještě lepší strukturu, než tuhle, byste si pro INNER JOIN představoval.

[Miroslav Šilhavý]

Vážně by mne zajímalo, jakou ještě lepší strukturu, než tuhle, byste si pro INNER JOIN představoval.

Pokud psal, že v tbl2 může, ale nemusí být odpovídající záznam, zdá se, že obě situace jsou zcela validní. V takovém případě je lepší použít OUTER JOIN a podmínku psát do WHERE. Přesně tam patří podmínky, je to mnohem čitelnější než zahazovat řádky v rámci JOINU. Optimizér si s tím poradí úplně stejně. Vhodnější je to i kvůli tomu, že podmínka se může měnit podle vstupu (např. filtrovací formulář). V tu chvíli je šikovné měnit jen WHERE sekci a neměnit joinování.

Podle Vaší logiky by bylo přípustné i SELECT ... FROM tbl1 INNER JOIN tbl2 ON tbl1. id_data = tbl2.id_data AND allowed > 1. I to by dalo stejný výsledek, ale to už je ultraprasárna.

[e3k]

a zatím co Rachek ma uz dávno napsaný svůj úkol Filip a Miroslav dále diskutují.
Jak se jmenuje tvůj profesor Rachek! proč není na root.cz? jak máme zabezpečit pedagogisticky péče bez jeho komentáře?

[Reklama]

[Filip Jirsák]

Pokud psal, že v tbl2 může, ale nemusí být odpovídající záznam, zdá se, že obě situace jsou zcela validní.

To ovšem platí pro každý JOIN. Rozhodující pro volbu INNER/OUTER JOIN je to, zda neexistence záznamu v podřízené tabulce znamená, že záznamy ve výsledné sadě být nemají, nebo zda znamená, že jsou data pro ten záznam neznámá (a mají tedy být nahrazena NULL hodnotami).

V takovém případě je lepší použít OUTER JOIN a podmínku psát do WHERE. Přesně tam patří podmínky, je to mnohem čitelnější než zahazovat řádky v rámci JOINU.

Stará syntaxe skutečně psala všechny podmínky do WHERE. Nová syntaxe s klíčovým slovem JOIN ale rozlišuje podmínky na ty, které se používají pro spojení tabulek, a na ty, které filtrují výslednou sadu záznamů. Ta hranice není úplně ostrá, takže se najdou případy, kdy mohou být logické obě varianty. Což ale není tento případ, tady je podle zadání jistě podmínka omezující výslednou sadu jenom allowed > 0 – všimněte si, že je uvedena v zadání samostatně, žádná jiná podmínka u ní už není. Jenom jako poznámku na konec přidal Racchek informaci, že tbl2 nemusí obsahovat všechny záznamy z tbl1, což je informace o tom, že je nutné řešit, zda použít INNER JOIN nebo OUTER JOIN. Akorát už nenapsal, která varianta platí.

Vaše tvrzení, že je to mnohem čitelnější, myslím dobře vyvrací ta skutečnost, že jste právě kvůli té vaší variantě zápisu udělal v dotazu chybu. Výhoda zápisu INNER JOIN přes klíčové slovo (INNER) JOIN je v tom, že už nemusíte specifikovat, jak se pozná, že podřízený záznam neexistuje. Ta podmínka plyne přímo z INNER JOINu a databáze ji tam přidá sama – a na rozdíl od vás správně.

Podle Vaší logiky by bylo přípustné i SELECT ... FROM tbl1 INNER JOIN tbl2 ON tbl1. id_data = tbl2.id_data AND allowed > 1. I to by dalo stejný výsledek, ale to už je ultraprasárna.

Ne, nic takového jsem já nepsal. Nicméně pokud by zadání bylo formulované jako „vybrat z tbl1 všechny záznamy, kde v tbl2 existuje odpovídající záznam s allowed > 0“, považoval bych to za ten hraniční případ, kdy jsou možné obě varianty. Protože buď tabulky spojíte a pak sadu filtrujete, nebo můžete tabulku spojit s filtrovanou tabulkou – a v té mnou uvedené formulaci už je to spíš to druhé.

[Miroslav Šilhavý]

Protože buď tabulky spojíte a pak sadu filtrujete, nebo můžete tabulku spojit s filtrovanou tabulkou – a v té mnou uvedené formulaci už je to spíš to druhé.

Během optimalizace dotazu dojde query planner ke stejnému prováděcímu plánu. Jak už jsem psal výše, pro planner je totožné INNER JOIN a LEFT JOIN WHERE right.id IS NOT NULL (nebo right.id > 0, protože to implikuje IS NOT NULL). To je taková hodně základní optimalizace.

Řeknu to možná lapidárně: pokud někde vidím INNER JOIN, očekávám vazbu 1:1-N. Pokud vidím LEFT JOIN, očekávám 1:0-N. Tato čitelnost je důležitá, zejména když se na SQL příkazy dívá někdo cizí, nebo i sám po nějaké době.

[gill]

proč left join?

Left join tam musí být, viz zadání úkolu: "S tím, že tbl2.id_data nemusí obsahovat všechny data v tbl1.id_data"

jak jste z toho tvrzení vyvodil, že výsledek ty řádky obsahovat má?

[Miroslav Šilhavý]

jak jste z toho tvrzení vyvodil, že výsledek ty řádky obsahovat má?

On je obsahovat nebude. Pokud dáte WHERE tbl2.allowed > 0, tak se ty řádky vynechají.

[Miroslav Šilhavý]

...výhodou LEFT JOINU je i to, že se dá dotaz invertovat jen změnou podmíny:
SELECT * FROM tbl1 LEFT JOIN tbl2 USING (id_data) WHERE allowed > 0

a k tomu je opakem:
SELECT * FROM tbl1 LEFT JOIN tbl2 USING (id_data) WHERE allowed = 0 OR allowed IS NULL

Jak vidíte, první část se nemění, mění se pak jen podmínka. Toho s INNER JOINEM nedosáhnete.

[gill]

jak jste z toho tvrzení vyvodil, že výsledek ty řádky obsahovat má?

On je obsahovat nebude. Pokud dáte WHERE tbl2.allowed > 0, tak se ty řádky vynechají.

potom nepotřebujete left join

[Miroslav Šilhavý]

potom nepotřebujete left join

Viz výše příklad s obrácením funkce. Left join je rozhodně lepší zápis, čitelnější a vyjadřuje strukturu dat. Výsledek bude stejný, ale kdokoliv se na to podívá, pochopí líp, co od toho očekávat.

[Filip Jirsák]

Během optimalizace dotazu dojde query planner ke stejnému prováděcímu plánu. Jak už jsem psal výše, pro planner je totožné INNER JOIN a LEFT JOIN WHERE right.id IS NOT NULL (nebo right.id > 0, protože to implikuje IS NOT NULL). To je taková hodně základní optimalizace.

O optimalizaci dotazu tady ale nikdo nemluví. Jde o sémantiku dotazu, o to, jak ho chápe vývojář.

Řeknu to možná lapidárně: pokud někde vidím INNER JOIN, očekávám vazbu 1:1-N. Pokud vidím LEFT JOIN, očekávám 1:0-N. Tato čitelnost je důležitá, zejména když se na SQL příkazy dívá někdo cizí, nebo i sám po nějaké době.

To je ale váš problém, že SQL dotazy čtete jinak, než všichni ostatní. Znamená to, že od zápisu INNER JOIN očekáváte něco, co vám nemůže zajistit, a když pro zápis INNER JOINu používáte OUTER JOIN, musíte správně napsat tu podmínku, která z toho udělá INNER JOIN. Což se vám zrovna tady v diskusi ještě nepovedlo. Řečeno s Cimrmanem – ten váš kód je sice hůř čitelný, ale zato v něm děláte chyby.

...výhodou LEFT JOINU je i to, že se dá dotaz invertovat jen změnou podmíny:
SELECT * FROM tbl1 LEFT JOIN tbl2 USING (id_data) WHERE allowed > 0

a k tomu je opakem:
SELECT * FROM tbl1 LEFT JOIN tbl2 USING (id_data) WHERE allowed = 0 OR allowed IS NULL

Jak vidíte, první část se nemění, mění se pak jen podmínka. Toho s INNER JOINEM nedosáhnete.

Až na to, že normální lidi považují k dotazu „dej mi všechny záznamy, které mají podřízený záznam allowed > 0“ za inverzní „dej mi všechny záznamy, které mají podřízený záznam s alowed = 0“ (za předpokladu, že alowed > 0 a alowed = 0 jsou k sobě inverzní).

Viz výše příklad s obrácením funkce. Left join je rozhodně lepší zápis, čitelnější a vyjadřuje strukturu dat. Výsledek bude stejný, ale kdokoliv se na to podívá, pochopí líp, co od toho očekávat.

Když je to tedy lepší a čitelnější zápis, proč ho tu pořád píšete špatně?

[Miroslav Šilhavý]

O optimalizaci dotazu tady ale nikdo nemluví. Jde o sémantiku dotazu, o to, jak ho chápe vývojář.

(...)

Až na to, že normální lidi považují k dotazu „dej mi všechny záznamy, které mají podřízený záznam allowed > 0“ za inverzní „dej mi všechny záznamy, které mají podřízený záznam s alowed = 0“ (za předpokladu, že alowed > 0 a alowed = 0 jsou k sobě inverzní).

Ha! Tak jsme u podstaty věci. Pane kolego, tak toto je školácká chyba na úrovni druhé, třetí lekce práce se SQL.
Musíte s NULL počítat! (Pro začátečníky připomenu, že SELECT 1=1 navrátí TRUE, zatímco SELECT NULL=NULL nenavrátí TRUE, ale opět NULL.)

Kupř. když v tomto případě máme:
allowed > 0 znamená: přístup povolen,
allowed = 0: přístup zakázán,
allowed IS NULL: přístup neurčen,

pak SQL pak platí, že:
inverzní k"povolen" je "zakázán + neurčen",
inverzní k "zakázán" je "povolen + neurčen",
inverzní k "neurčen" je "povolen + zakázán".

Aby toto mohl vývojář opominout, musel by být sloupec allowed nastavený NOT NULL a nemohlo by platit že záznam v pravé tabulce může či nemusí existovat.

Tím bych naši diskusi uzavřel. Je zřejmé, že SQL rozumíte, doplníte si za domácí úkol studium NULL a to, jak je s ním potřeba v SQL počítat a co to znamená "sémantika".

[Filip Jirsák]

Ha! Tak jsme u podstaty věci. Pane kolego, tak toto je školácká chyba na úrovni druhé, třetí lekce práce se SQL.

Omlouvám se, nebylo mým cílem nachytat vás na švestkách. Ale chytil jste se krásně.

pak SQL pak platí, že:

Já jsem ale nepsal o SQL. Psal jsem o tom, jaké je obvykle zadání, jak to vnímají lidé, jak to obvykle plyne z logiky věci. Když budete mít zadání „vypište všechny uživatele, kteří bydlí v Praze“, bude k tomu obvykle inverzní zadání „vypište všechny uživatele, kteří bydlí mimo Prahu“, ne „vypište všechny uživatele, kteří bydlí mimo Prahu nebo nebydlí vůbec“. Abyste se v tom neztratil, tu podmínku jsem vám přímo slovně napsal.

Musíte s NULL počítat! […] Aby toto mohl vývojář opominout, musel by být sloupec allowed nastavený NOT NULL a nemohlo by platit že záznam v pravé tabulce může či nemusí existovat.

Výborně, takže to, že allowed může být nullable, vás napadlo.

Tím bych naši diskusi uzavřel. Je zřejmé, že SQL rozumíte, doplníte si za domácí úkol studium NULL a to, jak je s ním potřeba v SQL počítat.

Pane kolego, domácí úkol tady bude dělat někdo jiný, totiž vy. Když už teď víte, že allowed může být nullable, a také víte, že NULL hodnotám je potřeba věnovat zvláštní péči, projdete si všechny ty INNER JOINy zapsané pomocí OUTER JOINu, které jste do diskuse napsal. Teď už snad konečně uvidíte tu školáckou chybu, kterou jste ve všech svých příkladech udělal. Tu chybu opravíte a zapamatujete si, že je dobré používat nástroje k tomu, k čemu jsou určené – takže když je sémantika příkazu INNER JOIN, je rozumné to napsat pomocí INNER JOINu a ne to znepřehledňovat OUTER JOINem, zejména, když neumíte správně napsat podmínku, která ten INNER JOIN definuje.