Korelovaný poddotaz - Correlated subquery
V SQL databáze dotaz, a korelovaný poddotaz (také známý jako synchronizovaný poddotaz) je poddotaz (dotaz vnořený do jiného dotazu), který používá hodnoty z vnějšího dotazu. Protože poddotaz může být vyhodnocen jednou pro každý řádek zpracovaný vnějším dotazem, může být pomalý.
Zde je příklad typického korelovaného poddotazu. V tomto příkladu je cílem najít všechny zaměstnance, jejichž plat je nad průměrem jejich oddělení.
VYBRAT číslo zaměstnance, název Z zaměstnanci emp KDE plat > ( VYBRAT AVG(plat) Z zaměstnanci KDE oddělení = emp.oddělení);Ve výše uvedeném dotazu je vnější dotaz
VYBRAT číslo zaměstnance, název Z zaměstnanci emp KDE plat > ...a vnitřní dotaz (korelovaný poddotaz) je
VYBRAT AVG(plat) Z zaměstnanci KDE oddělení = emp.odděleníVe výše uvedeném vnořeném dotazu musí být vnitřní dotaz znovu proveden pro každého zaměstnance. (Dostatečně chytrá implementace může výsledek vnitřního dotazu ukládat do mezipaměti na základě jednotlivých oddělení, ale i v tom nejlepším případě musí být vnitřní dotaz proveden jednou za každé oddělení. Viz „Optimalizace korelovaných poddotazů“ níže.)
Korelované poddotazy se mohou objevit jinde kromě Klauzule WHERE; například tento dotaz používá korelovaný poddotaz v souboru Klauzule SELECT vytisknout celý seznam zaměstnanců spolu s průměrnou mzdou za oddělení každého zaměstnance. Opět, protože poddotaz je korelován se sloupcem vnějšího dotazu, musí být znovu proveden pro každý řádek výsledku.[Citace je zapotřebí ]
VYBRAT číslo zaměstnance, název, (VYBRAT AVG(plat) Z zaměstnanci KDE oddělení = emp.oddělení) TAK JAKO oddělení_průměr Z zaměstnanci empObecně nemá smysl mít korelovaný poddotaz v klauzuli FROM, protože tabulka v klauzuli FROM je potřebná k vyhodnocení vnějšího dotazu, ale korelovaný poddotaz v klauzuli FROM nelze vyhodnotit před vyhodnocením vnějšího dotazu, což způsobí problém s kuřecím masem a vejcem. Konkrétně MariaDB uvádí toto omezení jako omezení ve své dokumentaci.[1]
V některých databázových systémech je však povoleno používat korelované poddotazy při připojování k klauzuli FROM, odkazování na tabulky uvedené před spojením pomocí zadaného klíčového slova, vytváření řady řádků v korelovaném poddotazu a jeho připojení k tabulce na vlevo, odjet. Například v PostgreSQL, přidáním klíčového slova LATERAL před pravý poddotaz,[2] nebo v SQL Server pomocí klíčového slova CROSS APPLY nebo OUTER APPLY namísto JOIN[3] dosáhne efektu.
Účinek korelovaných poddotazů lze v některých případech získat pomocí připojí se. Například výše uvedené dotazy (které používají neefektivní korelované poddotazy) lze přepsat následujícím způsobem.
- Tento poddotaz nekoreluje s vnějším dotazem, a proto je - provedeno pouze jednou, bez ohledu na počet zaměstnanců. VYBRAT zaměstnanci.číslo zaměstnance, zaměstnanci.název Z zaměstnanci VNITŘNÍ PŘIPOJIT SE (VYBRAT oddělení, AVG(plat) TAK JAKO oddělení_průměr Z zaměstnanci SKUPINA PODLE oddělení) tepl NA zaměstnanci.oddělení = tepl.oddělení KDE zaměstnanci.plat > tepl.oddělení_průměr;Pokud se vnitřní dotaz používá ve více dotazech, lze vnitřní dotaz uložit jako zobrazení a poté se k němu připojit:
VYTVOŘIT POHLED dept_avg TAK JAKO VYBRAT oddělení, AVG(plat) TAK JAKO oddělení_průměr Z zaměstnanci SKUPINA PODLE oddělení; - Seznam zaměstnanců, kteří vydělávají více, než je průměr jejich oddělení. VYBRAT zaměstnanci.číslo zaměstnance, zaměstnanci.název Z zaměstnanci VNITŘNÍ PŘIPOJIT SE dept_avg NA zaměstnanci.oddělení = dept_avg.oddělení KDE zaměstnanci.plat > dept_avg.oddělení_průměr; - Seznam zaměstnanců spolu s průměry jejich příslušných oddělení. VYBRAT zaměstnanci.číslo zaměstnance, zaměstnanci.název, dept_avg.oddělení_průměr Z zaměstnanci VNITŘNÍ PŘIPOJIT SE dept_avg NA zaměstnanci.oddělení = dept_avg.oddělení; POKLES POHLED dept_avg;Místo pohledu můžete také vytvořit a odkazovat na dočasnou tabulku.
Dalším způsobem, jak toho dosáhnout, který by měl stejný výkon jako řešení „zobrazení“, je použití CTE (Common Table Expression) následujícím způsobem. To má tu výhodu, že máte celou operaci v jednom dotazu pro případ, že je to požadavek. Všimněte si, že některé verze SQL, obvykle starší, nepodporují operaci „With ... CTE“.
S VYBRAT oddělení, AVG(plat) TAK JAKO oddělení_průměr Z zaměstnanci SKUPINA PODLE odděleníTAK JAKO dept_avg_CTE - libovolné jméno, nepotřebuje "CTE" - Seznam zaměstnanců, kteří vydělávají více, než je průměr jejich oddělení. VYBRAT zaměstnanci.číslo zaměstnance, zaměstnanci.název Z zaměstnanci VNITŘNÍ PŘIPOJIT SE dept_avg_CTE NA zaměstnanci.oddělení = dept_avg_CTE.oddělení KDE zaměstnanci.plat > dept_avg_CTE.oddělení_průměr;Implementace databáze, jako je Oracle, mohou automaticky zrušit korelaci poddotazu, pokud to optimalizátor založený na nákladech považuje za výnos lepšího plánu provádění.