Inner Join Vs Outer Join: Připravte se na zkoumání přesných rozdílů mezi Inner Join a Outer Join

Než začneme zkoumat rozdíly mezi Inner Join Vs Outer Join, podívejme se nejprve, co je to SQL JOIN?

Klausule join se používá ke spojení záznamů nebo k manipulaci se záznamy ze dvou nebo více tabulek prostřednictvím podmínky join. Podmínka spojení udává, jak se sloupce z jednotlivých tabulek navzájem porovnávají.

Spojení je založeno na příbuzném sloupci mezi těmito tabulkami. Nejběžnějším příkladem je spojení dvou tabulek prostřednictvím sloupce primárního klíče a sloupce cizího klíče.

Předpokládejme, že máme tabulku, která obsahuje plat zaměstnance, a existuje další tabulka, která obsahuje údaje o zaměstnanci.

V tomto případě bude existovat společný sloupec jako ID zaměstnance, který tyto dvě tabulky spojí. Tento sloupec ID zaměstnance by byl primárním klíčem tabulky s údaji o zaměstnanci a cizím klíčem v tabulce Plat zaměstnance.

Je velmi důležité mít společný klíč mezi oběma entitami. Tabulku si můžete představit jako entitu a klíč jako společné pojítko mezi oběma tabulkami, které se používá pro operaci spojení.

Zásadně existují dva typy spojení v SQL, tj. vnitřní spojení a vnější spojení. Vnější spojení se dále dělí na tři typy, tj. levé vnější spojení, pravé vnější spojení a úplné vnější spojení.

V tomto článku si podrobně ukážeme rozdíl mezi vnitřním a vnějším spojením. Cross Join a Unequal Joins ponecháme mimo rozsah tohoto článku.

Co je Inner Join?

Vnitřní Join vrací pouze řádky, které mají shodné hodnoty v obou tabulkách (uvažujeme zde spojení provedené mezi dvěma tabulkami).

Co je Outer Join?

Vnější Join zahrnuje shodné řádky i některé neshodné řádky mezi oběma tabulkami. Vnější spojení se v podstatě liší od vnitřního spojení tím, jak zpracovává podmínku falešné shody.

Existují tři typy vnějšího spojení:

• Vnější levé spojení: Vrací všechny řádky z LEVÉ tabulky a odpovídající záznamy mezi oběma tabulkami.
• Pravé vnější spojení:
• Full Outer Join: Vrátí všechny řádky z tabulky RIGHT a odpovídající záznamy mezi oběma tabulkami:

Rozdíl mezi Inner a Outer Join

Jak ukazuje výše uvedený diagram, existují dvě entity, tj. tabulka 1 a tabulka 2, a obě tabulky sdílejí některá společná data.

Vnitřní spojení (Inner Join) vrátí společnou oblast mezi těmito tabulkami (zeleně stínovaná oblast ve výše uvedeném diagramu), tj. všechny záznamy, které jsou společné pro tabulku 1 a tabulku 2.

Vnější spojení (Left Outer Join) vrátí všechny řádky z tabulky 1 a pouze ty řádky z tabulky 2, které jsou společné i pro tabulku 1. Right Outer Join provede pravý opak. Poskytne všechny záznamy z tabulky 2 a pouze odpovídající shodné záznamy z tabulky 1.

Dále nám Full Outer Join poskytne všechny záznamy z tabulky 1 a tabulky 2.

Začněme příkladem, aby to bylo jasnější.

Předpokládejme, že máme dvě tabulky: EmpDetails a EmpSalary.

Tabulka EmPodrobnosti:

EmpSalary Table:

Provedeme Inner Join na tyto dvě tabulky a budeme pozorovat výsledek:

Dotaz:

SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalaryFROM EmpDetails INNER JOIN EmpSalaryON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Výsledek:

V uvedeném souboru výsledků, vidíte, že Inner Join vrátil prvních 6 záznamů, které se vyskytovaly v EmpDetails i EmpSalary a měly shodný klíč i.EmployeeID. Pokud jsou tedy A a B dvě entity, Inner Join vrátí množinu výsledků, která se bude rovnat „Záznamy v A a B“ na základě shodného klíče.

Podívejme se nyní, co udělá Left Outer Join.

Dotaz:

SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalaryFROM EmpDetails LEFT JOIN EmpSalaryON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Výsledek:

V uvedeném souboru výsledků, vidíte, že levé vnější spojení vrátilo všech 10 záznamů z tabulky LEFT i.tj. tabulky EmpDetails, a protože prvních 6 záznamů je shodných, vrátil pro tyto shodné záznamy plat zaměstnance.

Jelikož ostatní záznamy nemají shodný klíč v PRAVÉ tabulce, tj. tabulce EmpSalary, vrátil pro ně odpovídající NULL. Protože Lily, Sita, Farah a Jerry nemají odpovídající ID zaměstnance v tabulce EmpSalary, jejich plat se ve výsledkové sadě zobrazí jako NULL.

Jsou-li tedy A a B dvě entity, pak levé vnější spojení vrátí výsledkovou sadu, která se bude rovnat „Záznamy v A NOT B“, a to na základě odpovídajícího klíče.

Nyní pozorujme, co dělá pravé vnější spojení.

Dotaz:

SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalaryFROM EmpDetails RIGHT join EmpSalaryON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Výsledek:

V uvedeném souboru výsledků, vidíte, že pravé vnější spojení (Right Outer Join) provedlo pravý opak levého spojení. Vrátilo všechny platy z pravé tabulky, tj. tabulky EmpSalary.

Jelikož však Rose, Sakshi a Jack nemají odpovídající ID zaměstnance v levé tabulce, tj. tabulce EmpDetails, získali jsme jejich ID zaměstnance a EmployeeName jako NULL z levé tabulky.

Jsou-li tedy A a B dvě entity, pak pravé vnější spojení vrátí množinu výsledků, která se bude rovnat „Záznamy v B NOT A“ na základě shodného klíče.

Podívejme se také, jaká bude množina výsledků, pokud provádíme operaci select na všechny sloupce v obou tabulkách.

Dotaz:

SELECT *FROM EmpDetails RIGHT JOIN EmpSalaryON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Výsledek:

Nyní, přejděme k funkci Full Join.

Plné vnější spojení se provádí tehdy, když chceme získat všechna data z obou tabulek bez ohledu na to, zda existuje shoda, nebo ne. Pokud tedy chci všechny zaměstnance, i když nenajdu shodný klíč, provedu dotaz podle následujícího obrázku.

Dotaz:

SELECT *FROM EmpDetails FULL JOIN EmpSalaryON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Výsledek:

V uvedeném souboru výsledků je vidět, že jako prvních šest záznamů se shoduje v obou tabulkách, získali jsme všechna data bez jakýchkoli NULL. Další čtyři záznamy existují v levé tabulce, ale ne v pravé, proto máme odpovídající údaje v pravé tabulce NULL.

Poslední tři záznamy existují v pravé tabulce a ne v levé, proto máme v odpovídajících údajích z levé tabulky NULL. Pokud jsou tedy A a B dvě entity, úplné vnější spojení vrátí množinu výsledků, která se bude rovnat „Záznamy v A A B“, bez ohledu na odpovídající klíč.

Teoreticky se jedná o kombinaci levého a pravého spojení.

Výkon

Porovnejme vnitřní spojení s levým vnějším spojením na serveru SQL. Mluvíme-li o rychlosti operace, je zřejmé, že levý vnější JOIN není rychlejší než vnitřní JOIN.

Podle definice musí vnější JOIN, ať už levý nebo pravý, provést veškerou práci vnitřního JOINu spolu s další prací null- rozšířením výsledků. Očekává se, že vnější spojení vrátí větší počet záznamů, což dále zvyšuje jeho celkovou dobu provádění právě kvůli větší množině výsledků.

Vnější spojení je tedy pomalejší než vnitřní spojení.

Může navíc nastat několik specifických situací, kdy bude levé spojení rychlejší než vnitřní spojení, ale nemůžeme pokračovat pro jejich vzájemné nahrazení, protože levé vnější spojení není funkčně ekvivalentní s vnitřním spojením.

Prodiskutujme příklad, kdy může být levé spojení rychlejší než vnitřní spojení. Pokud jsou tabulky zapojené do operace spojení příliš malé, řekněme, že mají méně než 10 záznamů a tabulky nemají dostatečné indexy pro pokrytí dotazu, je v takovém případě Left Join obecně rychlejší než Inner Join.

Vytvořme dvě níže uvedené tabulky a jako příklad mezi nimi proveďme INNER JOIN a LEFT OUTER JOIN:

CREATE TABLE #Table1(ID int NOT NULL PRIMARY KEY,Name varchar(50) NOT NULL)INSERT #Table1 (ID, Name) VALUES (1, 'A')INSERT #Table1 (ID, Name) VALUES (2, 'B')INSERT #Table1 (ID, Name) VALUES (3, 'C')INSERT #Table1 (ID, Name) VALUES (4, 'D')INSERT #Table1 (ID, Name) VALUES (5, 'E')CREATE TABLE #Table2(ID int NOT NULL PRIMARY KEY,Name varchar(50) NOT NULL)INSERT #Table2 (ID, Name) VALUES (1, 'A')INSERT #Table2 (ID, Name) VALUES (2, 'B')INSERT #Table2 (ID, Name) VALUES (3, 'C')INSERT #Table2 (ID, Name) VALUES (4, 'D')INSERT #Table2 (ID, Name) VALUES (5, 'E')SELECT *FROM #Table1 t1INNER JOIN #Table2 t2ON t2.Name = t1.Name

SELECT * FROM (SELECT 38 AS bah) AS foo JOIN (SELECT 35 AS bah) AS barON (55=55);

Jak je vidět výše, oba dotazy vrátily stejnou sadu výsledků. Pokud si v tomto případě zobrazíte plán provedení obou dotazů, zjistíte, že vnitřní spojení stálo více než vnější spojení. Je to proto, že u vnitřního spojení SQL server provádí hash match, zatímco u levého spojení provádí vnořené smyčky.

Hash match je obvykle rychlejší než vnořené smyčky. Ale v tomto případě, protože počet řádků je tak malý a neexistuje žádný index, který by se dal použít (protože provádíme spojení na sloupec name), se operace hash ukázala jako nejdražší dotaz pro vnitřní spojení.

Jestliže však změníte klíč shody v dotazu na spojení z Name na ID a pokud je v tabulce velký počet řádků, pak zjistíte, že vnitřní spojení bude rychlejší než levé vnější spojení.

Vnitřní a vnější spojení v MS Access

Pokud v dotazu v MS Access používáte více zdrojů dat, pak použijete spojení (JOIN) pro kontrolu záznamů, které chcete zobrazit, v závislosti na tom, jak jsou zdroje dat vzájemně propojeny.

Při vnitřním spojení se do jedné výsledkové množiny spojí pouze příbuzné z obou tabulek. Jedná se o výchozí spojení v aplikaci Access a také nejčastěji používané. Pokud použijete spojení, ale výslovně neuvedete, o jaký typ spojení se jedná, Access předpokládá, že se jedná o vnitřní spojení.

Při vnějším spojení jsou správně spojena všechna související data z obou tabulek a navíc všechny zbývající řádky z jedné tabulky. Při úplném vnějším spojení se kombinují všechna data, pokud je to možné.

Levé spojení vs. levé vnější spojení

V serveru SQL je při použití levého vnějšího spojení klíčové slovo outer nepovinné. Není tedy žádný rozdíl, pokud napíšete buď „LEFT OUTER JOIN“, nebo „LEFT JOIN“, protože obojí vám dá stejný výsledek.

A LEFT JOIN B je ekvivalentní syntaxe k A LEFT OUTER JOIN B.

Níže je uveden seznam ekvivalentních syntaxí v SQL serveru:

Left Outer Join vs Right Outer Join

Tento rozdíl jsme již v tomto článku viděli. Můžete se podívat na dotazy Left Outer Join a Right Outer Join a sadu výsledků, abyste viděli rozdíl.

Hlavní rozdíl mezi Left Join a Right Join spočívá v zahrnutí neshodných řádků. Left outer join zahrnuje neshodné řádky z tabulky, která je nalevo od spojovací klauzule, zatímco Right outer join zahrnuje neshodné řádky z tabulky, která je napravo od spojovací klauzule.

Lidé se ptají, co je lepší použít, tj. Left join nebo Right join? V podstatě se jedná o stejný typ operací, pouze s obrácenými argumenty. Proto když se ptáte, který join použít, ptáte se vlastně, zda napsat a<b nebo b>a. Je to jen otázka preferencí.

Všeobecně platí, že lidé ve svých dotazech SQL raději používají levé spojení. Doporučoval bych, abyste zůstali důslední ve způsobu, jakým dotaz píšete, abyste se vyhnuli zmatkům při interpretaci dotazu.

Dosud jsme viděli vše o vnitřním spojení a všech typech vnějších spojení. Pojďme si rychle shrnout rozdíl mezi Inner Join a Outer Join.

Rozdíl mezi Inner Join a Outer Join v tabulkovém formátu

Vnitřní a vnější spojení vs. unie

Občas zaměňujeme Join a unii a je to také jedna z nejčastěji kladených otázek při pohovorech o SQL. Již jsme se seznámili s rozdílem mezi vnitřním a vnějším spojením . Nyní se podívejme, jak se JOIN liší od UNION.

UNION umisťuje řadu dotazů za sebe, zatímco join vytváří kartézský součin a rozděluje jej. UNION a JOIN jsou tedy zcela odlišné operace.

Pustíme si dva níže uvedené dotazy v MySQL a uvidíme jejich výsledek.

Dotaz UNION:

SELECT 28 AS bahUNIONSELECT 35 AS bah;

Výsledek:

JOIN dotaz:

SELECT * FROM(SELECT 38 AS bah) AS fooJOIN(SELECT 35 AS bah) AS barON (55=55);

Výsledek:

Operace UNION vloží výsledek dvou nebo více dotazů do jedné výsledkové množiny. Tato množina výsledků obsahuje všechny záznamy, které byly vráceny prostřednictvím všech dotazů zapojených do operace UNION. Operace UNION tedy v podstatě spojuje dvě výsledkové množiny dohromady.

Operace join načítá data ze dvou nebo více tabulek na základě logických vztahů mezi těmito tabulkami, tj. na základě podmínky join. Ve spojovacím dotazu jsou data z jedné tabulky použita k výběru záznamů z jiné tabulky. Umožňuje propojit podobná data, která se vyskytují nad různými tabulkami.

Pro pochopení lze velmi zjednodušeně říci, že UNION kombinuje řádky ze dvou tabulek, zatímco join kombinuje sloupce ze dvou nebo více tabulek. Obojí se tedy používá ke spojení dat z n tabulek, ale rozdíl spočívá ve způsobu spojení dat.

Níže jsou uvedena obrázková znázornění UNION a JOIN.

Výše uvedené je obrázkové znázornění operace Join zobrazující, že každý záznam v souboru výsledků obsahuje sloupce z obou tabulek, tj. tabulky A a tabulky B. Tento výsledek je vrácen na základě podmínky spojení použité v dotazu.

Spojení je obecně výsledkem denormalizace (opak normalizace) a používá cizí klíč jedné tabulky k vyhledání hodnot sloupců pomocí primárního klíče v jiné tabulce.

Výše uvedené je názorné zobrazení operace UNION znázorňující, že každý záznam v množině výsledků je řádek z jedné z obou tabulek. Výsledek operace UNION tedy spojil řádky z tabulky A a tabulky B.

Další čtení =>> MySQL UNION vysvětlený na příkladech

Závěr

V tomto článku jsme viděli hlavní rozdíly mezi Inner Join a Outer Join v SQL. Viděli jsme také klasifikaci Outer Join, tedy Left join, Right join a Full join. Viděli jsme, jak jednotlivé typy spojení fungují a jak se od sebe liší.

Provedli jsme také srovnání výkonu těchto typů spojení. Probrali jsme také, jak se join liší od union.

Přečtěte si také =>> Typy spojení MySQL

Doufám, že vám tento článek pomohl objasnit vaše pochybnosti ohledně rozdílů mezi jednotlivými typy spojení. Jsme si jisti, že vám skutečně pomůže rozhodnout se, který typ spojení zvolit na základě požadovaného souboru výsledků.