Dotaz: Maji se psat podminky do joinu

[FIX-MAN]

Ahoj,

vedeme takovou diskuzi v praci a nejsem schopni to rozseknout. Maji se psat podminky do join nebo ne? Kdyz to ukazu na dvou prikladech ktery je spravnejsi A nebo B prosim?

A: SELECT * FROM users JOIN services ON (services.user_id = users.id AND service.type = 1)

B: SELECT * FROM users JOIN services ON (services.user_id = users.id) WHERE service.type = 1

Diky moc

---

---

Odpovědi

[xkucf03]

Výsledek bude stejný, v podstatě jde jen o syntaktický cukr a stačil by ti kartézský součin a podmínky ve WHERE.

Ale z hlediska čitelnosti má smysl to rozlišovat:

• do ON dávat podmínky potřebné k propojení tabulek (budou odrážet cizí klíče – vztahy mezi tabulkami zadrátované v datovém modelu – budou stejné napříč různými dotazy)
• do WHERE dávat podmínky pro filtrování záznamů (budou odrážet požadavky daného dotazu tzn. budou se napříč různými dotazy lišit)

(toho bych se držel – aspoň v takto jednoduchých případech)

[xkucf03]

Viz také: Spojování tabulek

[EtDirloth]

S tymto suhlasim. Tiez to tak robim.

Len to zacne byt rozdiel pri pouziti OUTER JOINov. Vtedy moze ten "menej spravny" zapis pre INNER JOIN byt jedinym spravnym v OUTER JOIN.

[xkucf03]

Ano, proto jsem psal „aspoň v takto jednoduchých případech“ :-)

[drnest]

Kdysi jsme psávali SQL kde keyword JOIN neexistovalo a psalo se to všechno do WHERE. Řekl bych že JOIN se zavedlo právě kvůli tomu aby tam byly jen podmínky propojující tabulky a WHERE se mohlo použít na filtrování.

Takže za mě určtě B)

[OldFrog {Ondra Nemecek}]

100% se podmínky NEMAJÍ psát do JOIN klauzule. Takže @A není jen méně správné, ale je to špatně. A když to vidím, tak bych věšel autory za ... do průvanu.

Občas, v některých analytických dotazech může být podmínka v ON netriviální a může to mít hodně specifický smysl. Pokud ale programátor zapíše normální filtr do ON, tak trochu světa znalejší člověk v tom nebude hledat blbost programátora, ale nějaký úmysl, a dá to dost práce zjistit, že to byla jen blbost programátora.

Dnes standardní zápis JOINu je schválně navržený tak, aby bylo snadné měnit inner join na outer join (a obráceně). Do ON patří jen spojení relací.

[EtDirloth]

Lenze ak dam dalsiu podmienku do ON klauzuly OUTER JOINu, tak nou zaroven viem filtrovat len tie vonkajsie data. Takze ak chcem prijoinovat NULL aj v specifickom pripade, tak to viem vyriesit bez subselectu:

SELECT * FROM users LEFT JOIN services ON (services.user_id = users.id AND services.type = 1);

co je rozhodne citatelnejsie (hlavne pre planner) nez toto:

SELECT * FROM users LEFT JOIN (SELECT * FROM services WHERE services.type = 1) AS services ON (services.user_id = users.id);

[okbobcz]

Jsou výjimky potvrzující pravidlo. Nicméně téměř vždy to signalizuje divně navrženou databázi (problémy s normalizací) nebo divně zadanou úlohu.

Přiznám se, že netuším proč nenapíšete SELECT * FROM users LEFT JOIN servises ON selrvices.user_id = users.id WHERE services.type = 1?

A také netuším, co myslíte pod "citelnější pro planner". Tomu je to u moderních databází jedno, protože se před optimalizací snaží o flattening (takhle je to nazvané v Postgresu), tj redukci zanořených dotazů, tam kde to jde. Obě dvě ukázky mi přijdou dost obskurní.

[EtDirloth]

Pochopitelne preto, ze to moze vratit iny vysledok, kedze WHERE services.type = 1 odfiltruje aj takych users, ktori nemaju servis, kdezto ON (... AND services.type = 1) ich vo vysledku ponecha.

Takze ak by sa mal dodrziavat tento sposob zapisu, musela by ta podmienka vyzerat nejak takto: WHERE (services.type IS NULL OR services.type = 1). A mam skusenost, ze si s tym "OR" uz nie kazdy planner vie efektivne poradit.

[okbobcz]

Jako workaround poslední naděje to beru. Nicméně pak je ten dotaz dost nečitelný, a dneska už většina databází by si s tím OR měla plus mínus poradit. Já osobně,kdybych měl tuto situaci řešit, tak bych raději použil UNION. Bude to mnohem názornější.

[ehmmm]

Obavam se, ze opis "WHERE (services.type IS NULL OR services.type = 1)" ti neda stejny vysledek, jak ten puvodni filtr v joinu. Co kdyz bude pro daneho usera existovat services.type=2? Left join ho najde, ale pres where neprojde. Zatimco v puvodni verzi left join nenajde nic a ty se to dozvis. Asi to pisu krikolomne, ale kod uz tohle parkrat resil, tak vi. Holt, nekdy se to musi naprasit primo do joinu.

[EtDirloth]

To je vlastne fakt. Dalsi argument pre tu ON klauzulu.

Ked na to niekedy znovu vo svojom kode narazim, tak si schvalne spravim test, ci ma flattening sancu vyuzit rozne druhy indexov pri WHERE services.type IS NULL OR services.type = 1.

[xkucf03]

Může se to týkat třeba tabulek, kde jsou verzovaná data a záznamy mají platnost od/do (ať už klasicky jako dva sloupečky nebo jako range). Můžu si chtít přiJOINovat záznamy z jiné tabulky – pokud existují – platné k určitému datu (což může být aktuální okamžik, datum nějakého jiného záznamu, datum zadané uživatelem pro daný dotaz…). A pak dám tu podmínku do ON, což je podle mého čitelnější, nebo ji dám do WHERE, ale tam musím přes OR/NULL ošetřit případy, kdy příslušné záznamy neexistují, což podle mého ten dotaz dost znepřehlední.

Přijde mi, že když je datový model navržený tak, že záznamy neodrážejí jen aktuální stav, ale verzují se (což je často nutnost), tak v té databázi vznikají souvislosti/pravidla, která moc nejde podchytit referenční integritou. Maximálně nějakým triggerem vynutit, že např. záznamy, které jsem provázal přes cizí klíč mají překrývající se platnost. Tzn. např. když zakládám žádost, tak ji nemůžu navázat na adresu, jejíž platnost už skončila.

Nicméně téměř vždy to signalizuje divně navrženou databázi (problémy s normalizací) nebo divně zadanou úlohu.

Je takový datový model tedy špatně? Jak se to dá dělat líp? Kdysi jsem se pokoušel podobná pravidla realizovat pomocí složených klíčů, ale bylo to dost šílené a navíc to vedlo na velkou duplikaci hodnot do více tabulek (ty dodatečné sloupce použité pro složené klíče), takže jsem od toho nakonec upustil. Prakticky všechny databáze, se kterými jsem se potkal, mají určité zákonitosti/pravidla, která nejsou explicitně vyjádřená referenční integritou a datovým modelem, takže DBMS o nich neví – řeší se to až na aplikační úrovni.

[okbobcz]

Temporální databáze, což je to o čem mluvíte jsou na hraně, a bez podpory systémem to může vést k těmto dotazům. Ale i ty temporální databáze jde implementovat různě.

Bez podpory databázovým enginem mi přijdou verzovaná data v tabulkách, že jdou proti NF. Přestane platit, že pro jednu fyz. entitu mám jeden řádek. A samozřejmě, SQL (případně relační model) je nejvíc elegantní právě v normalizovaných datech. Jakmile se ustupuje z normalizace, tak ta elegance se ztrácí. A občas se musí pracovat s denormalizovanými daty i z důvodů výkonu.

Já jsem viděl používat pro tyto účely spíše UNION a mně osobně to přijde čitelnější. U toho joinu pokud tam nemám mapování PK na FK, tak je to, alespoň pro mne, hodně obtížné pochopit, co tím autor chtěl říct a jak se to má chovat. https://wiki.postgresql.org/images/6/64/Fosdem20150130PostgresqlTemporal.pdf

[xkucf03]

Dneska to jde řešit líp, i v Postgresu, což mě těší. Díky za tu prezentaci, myslím, že už jsem ji někde zahlídnul, ale teď jsem ji nemohl najít, teď pro jistotu ukládám na disk a do záložek :-) Vypadá to super. Ne všude je tohle k dispozici, ale verzovat se musí, takže se to řeší tím, co je.¹

Já na tenhle problém narazil hlavně u různých bankovních systémů a jde o věci, které vznikaly před 20+ lety a od té doby jsou v produkci, průběžně se to sice rozvíjí, ale na nějaký přepis nebo větší změnu datového modelu v podstatě nikdo nemá odvahu. Naopak lidem straší v hlavě vzpomínky na pokusy, které nedopadly…

_{[1] většinou se tam narvou sloupečky valid_from a valid_to, případně se občas historie odlívá do samostatné tabulky, ale psát nad tím dotazy je ještě větší peklo}

[Ivan]

Zrovna tenhle pripad je "trivialni" a oba zapisy by mely mit stejny exec plan. Sub-select neni zadne zlo.

Existuji ale i slozitejsi pripady, jako je semi-cross join:

SELECT * 
FROM A
LEFT JOIN B on (A.ISIN_CODE = B.ISIN_CODE and A.TRANS_START_DT <= B.TRAS_END_DT)

[j]

Proc uz nevisis? Blabolis uplne z cesty.

Schvalne, co myslis ze se asi tak stane ...

Select a.x/b.y from a join b on b.id = a.id where b.y <> 0

vs

Select a.x/b.y from a join b on ... and b.y <> 0

Netusis vid? V prvnim pripade ti to samozrjeme cely chcipne na deleni nulou. Ve druhym pripade tam zaznamy obsahujici nulu uz nejsou. Pomijim prostej fakt, ze v prvnim pripade (i kdyz tam deleni nulou nebude) se nejdriv propoji vsechny zaznamy, a nad nima se pusti filtr, kdezto ve druhym pripade se propoji jen zaznamy splnujici podminku.

V takhle primitivnim pripade se to samozrejme bude jeste chovat v zavislosti na schopnostech databaze (planovac to muze poznat), ale u libovolne slozitejsiho query to muze delat rozdil hodin a funkcni vs nefunkcni.

Takze jestli tu nekdo zaslouzi povesit za koule, tak ses to predevsim ty, vis vo tom lautr hovno!

---

Dete s tim guuglem dopice!

[okbobcz]

Tak schválně:

create table a(id int, x int);
create table b(id int, y int);

insert into a values(1, 10);
insert into b values(1, 10);
insert into b values(1, 0);

postgres=# Select a.x/b.y from a join b on b.id = a.id where b.y <> 0;
┌──────────┐
│ ?column? │
╞══════════╡
│        1 │
└──────────┘
(1 row)

Tak proč to nespadlo?

Samozřejmě, že dnešní optimalizátory, tam kde to jde (sémanticky) napřed aplikují filtry z WHERE a teprve potom provedou spojení. Popravdě, řekl bych, že se tak chovají posledních 30 let.

postgres=# explain Select a.x/b.y from a join b on b.id = a.id where b.y <> 0 
;
┌───────────────────────────────────────────────────────┐
│                      QUERY PLAN                       │
╞═══════════════════════════════════════════════════════╡
│ Nested Loop  (cost=0.00..2.05 rows=1 width=4)         │
│   Join Filter: (a.id = b.id)                          │
│   ->  Seq Scan on a  (cost=0.00..1.01 rows=1 width=8) │
│   ->  Seq Scan on b  (cost=0.00..1.02 rows=1 width=8) │
│         Filter: (y <> 0)                              │
└───────────────────────────────────────────────────────┘
(5 rows)

Jinak, kdybych chtěl ošetřit skutečně bezpečně dělení nulou a podobné limitní případy, tak bych měl použít CASE.

Když se někde potkáme, tak za pivko (ve vašem případě za dvě), vám můžu vysvětlit, jak fungují databáze. Když byste měl zájem.

[podlesh]

Když se někde potkáme, tak za pivko (ve vašem případě za dvě), vám můžu vysvětlit, jak fungují databáze. Když byste měl zájem.

Samozřejmě že nemá, on to přece ví. JOIN je FOR cyklus, WHERE je IF podmínka.

[OldFrog {Ondra Nemecek}]

Stejně tak H2 databáze:

select a.x/b.y from a join b on b.id = a.id where b.y <> 0;
select a.x/b.y from a join b on (b.id = a.id and b.y <> 0);

explain select a.x/b.y from a join b on b.id = a.id where b.y <> 0;
explain select a.x/b.y from a join b on (b.id = a.id and b.y <> 0);

dává v obou případech

A.X / B.Y  
1

s plánem

SELECT
    ("A"."X" / "B"."Y")
FROM "PUBLIC"."B"
    /* PUBLIC.B.tableScan */
    /* WHERE B.Y <> 0 */
INNER JOIN "PUBLIC"."A"
    /* PUBLIC.A.tableScan */
    ON 1=1
WHERE ("B"."Y" <> 0)
    AND ("B"."ID" = "A"."ID")

[podlesh]

Wow...

Just... wow...

Raději se nebudu vyjadřovat k tomu co je v tomto přípěvku špatně a raději budu reagovat tu jedinou věc která je za určitých okolností i pravdivá (náhodná trefa? omyl?).

Některé databáze skutečně tyto dva dotazy zpracují různě, s různým query planem:

select a.x/b.y from a join b on b.id = a.id where b.y <> 0 
Select a.x/b.y from a join b on (b.id = a.id and b.y <> 0)

Takovým databázím doporučuji se vyhnout (a určitě nejsem jediný).

Některé "databáze" pak skutečně mohou i "vygenerovat" rozdílné výsledky, možná i dokonce "SORRY VOLE ERROR". V těchto případech je doporučeno dodržet bezpečnou vzdálenost a při dotyku provést očistu postižených míst na disku:

1. rm -rf
2. wipe
3. SAVO/Domestos

V případě opakované nákazy pak bude nejlepší obrátit se na nejbližší diecézi, oddělení exorcismu.

[Xerces]

No některé ty databáze, které se takto chovají, ale mají řádově větší výkon proti těm standardním (PostgreSQL, MySQL, OracleDB, MSQL), takže se vyplatí to respektovat. Každopádně je dobré si chování dané databáze v těchto případech vyzkoušet.

[okbobcz]

Mohu vědět, které to jsou?

[Xerces]

Narazil jsem na to v Sybase IQ, což je DB pro datové sklady a pracuje hodně s denormalizovanými daty o čemž se, jak jsem si přečetl v diskuzi výše, zřejmě zmiňujete. Ale možná není úplně fér ji srovnávat s klasickými relačními databázemi

[okbobcz]

Tohle je OLAP databáze. Ona má vyšší výkon, ale jen v agregacích. Pokud byste do toho pustil běžný OLTP provoz, tak to lehne.

OLAP je specifická záležitost - tam se běžně pracuje s denormalizovanými daty, a vzhledem k velikosti dat se mnohem optimalizuje už na úrovni formátu - případně řešíte distribuci dat - tak aby se spojovala data na jednom serveru, atd. Sybase IQ, dnes SAP IQ je MPP databáze - masivně paralelní - a i optimalizace jsou úplně jiného charakteru. Je to něco podobného, jako když porovnáte pro výpočty C++ a optimalizovaný fortran pro vektorové operace.

Vždy je důležité rozlišovat jestli jste v OLTP světě nebo ve světě OLAPu. To jsou dva naprosto rozdílné světy s malým průnikem, a databáze navržené pro OLTP budou v OLAP zátěži o řád až dva (na velkých datech o několik řádů) pomalejší. A naopak. Resp. pokud byste pustili běžný (nijak extrémní) OLTP zátěž na OLAP databázi, tak to skoro vůbec nebude fungovat.

[okbobcz]

Další věc je, jak dobrý je tam optimalizátor. Např. u sloucových databází (v porovnání s řádkovými databázemi) většinou optimalizátor není nic moc, a mnohem více se spoléhá na hrubou sílu - paralelismus, distribuce výpočtu, efektivita načítání (díky kompresi) dat, adt. Je to ten rozdíl světů OLAP a OLTP.

[Xerces]

Ano je to přesně tak jak píšete (všechny 3 body). Díky podpoře SQL se ovšem z uživatelského hlediska jedná prostě o databázi a spousta z nich to opravdu nerozlišuje a mastí tam dotazy určené pro klasické OLTP .

[Ivan]

Poslednich par let delam uz jen Oracle, tak popisu jak se to chova tam.

V pripade Oracle existuje faze optimalizace ktere se rika "Algebraicke transformace", v praxi to vypada tak, ze se pokusi SQL dotaz transformovat na neco jineho a co se da dal optimalizovat. V tomhle pripade by se oba dotazy prepsaly na to same SQL, ktere by se pak optimalizovalo dal. Takze je "temer" zarucene ze exec plan by mel byt pro obe SQL stejny.

Obecne by melo platit, ze pokud maji dve SQL semanticky stejny vyznam, tak by mely mit i stejny exec plan. IMHO takhle se chova kazda rozumna databaze.

[Filip Jirsák]

Primárně se podmínky píšou do WHERE, tj. vaše varianta B. Variantu A výjimečně používám, pokud je ten dotaz složitější a je potřeba zdůraznit to, že spojuji se službami typu 1 – když to pomáhá pochopení dotazu.

[Josef Kufner]

Umístěním podmínky vyjadřuje autor SQL dotazu svůj záměr. Zda je lepší A či B záleží na kontextu a konvencích v okolním kódu.

Vztahuje se ta podmínka k dané relaci, nebo k výsledku dotazu?

Kdyby to byl LEFT/OUTER JOIN, napsal bys tu podmínku kam?

Ostatní podobné SQL dotazy v okolí mají tuto podmínku kde?

[fean]

Já bych si nevybíral podle toho co je jakože "správné". Domnívám se, že ty dva dotazy dělají každej tak trošku něco jiného. Že výsledek a dokonce execute plan jsou stejné je jen náhoda a chytrost optimalizeru. Stačí přidat join, jinej where, a může být všechno jinak.

Dotaz A: vezme relaci `users` a ke každému řádku se pokusí přiřadit řádek odpovídající podmínce. Výsledkem je relace, se kterou se dál pracuje u dalšího joinu, u selectu, u where, etc.

Dotaz B: se chová podobně, ale do té relace hodí i ty, které mají jiný než požadovaný typ, a následně se teprve aplikují další join, select, where. Teprve následně se vyhazuje typ.

Rozdíl je podobný jako mezi GROUP BY a DISTINC. V mnoha případech můžeš použít oboje. Ale má to své důsledky.

Jde tedy o významový rozdíl, který se v mnoha případech ztratí, ale o to víc tě vypeče, když bude ten dotaz složitější a nebudeš tomu rozumět.