Vod do souvislostí / Habr

JOIN je jednou z nejdůležitějších operací prováděných systémy správy relačních databází (RDBMS). RDBMS používají spojení k přiřazení řádků z jedné tabulky k řádkům z jiné tabulky. Spojení lze například použít k přiřazení prodejů zákazníkům nebo knih k autorům. Bez propojení by existovaly samostatné seznamy prodejců a zákazníků nebo knih a autorů, ale nebylo by možné zjistit, kteří zákazníci co koupili nebo který autor byl objednán.

Dvě tabulky můžete spojit explicitně uvedením obou tabulek v klauzuli FROM dotazu. Můžete také spojit dvě tabulky pomocí různých poddotazů. Nakonec může SQL Server během optimalizace přidat spojení k plánu dotazů, aby sloužil svým vlastním účelům.

Toto je první ze série článků, které plánuji věnovat spojům. V tomto článku se budu zabývat základy spojení popisem účelu operátorů logického spojení podporovaných SQL Serverem. Tady jsou:

• Vnitřní spojení
• Vnější spoj
• Cross cross
• Křížové použití
• Polospojení
• Anti-polo-spojení

Pro ilustraci každého zapojení použiji jednoduché schéma a datovou sadu:

create table Customers (Cust_Id int, Cust_Name varchar(10)) insert Customers values (1, 'Craig') insert Customers values (2, 'John Doe') insert Customers values (3, 'Jane Doe') create table Sales (Cust_Id int, Item varchar(10)) insert Sales values (2, 'Camera') insert Sales values (3, 'Computer') insert Sales values (3, 'Monitor') insert Sales values (4, 'Printer')

Vnitřní připojení

Vnitřní spojení jsou nejběžnějším typem spojení. Vnitřní spojení jednoduše najde dvojice řádků, které jsou spojeny a splňují predikát spojení. Například níže uvedený dotaz používá predikát spojení „S.Cust_Id = C.Cust_Id“ k nalezení všech podrobností o prodeji a zákaznících se stejnými hodnotami Cust_Id:

select * from Sales S inner join Customers C on S.Cust_Id = C.Cust_Id Cust_Id Item Cust_Id Cust_Name ----------- ---------- ----------- ---------- 2 Camera 2 John Doe 3 Computer 3 Jane Doe 3 Monitor 3 Jane Doe

Poznámky:

Cust_Id = 3 zakoupili dvě položky, takže se zobrazí ve dvou řádcích sady výsledků.

Cust_Id = 1 nic nekoupil, a proto se neobjeví ve výsledku.

Pro Cust_Id = 4 byl produkt také prodán, ale protože takový zákazník v tabulce není, informace o takovém prodeji se ve výsledku neobjevila.

Vnitřní spoje jsou plně komutativní. „A vnitřní spojení B“ a „B vnitřní spojení A“ jsou ekvivalentní.

Externí připojení

Řekněme, že bychom rádi viděli seznam všech prodejů; i ty, které nemají odpovídající zákaznické záznamy. Můžete napsat dotaz s vnějším spojením, který zobrazí všechny řádky v jedné nebo obou spojených tabulkách, i když žádný řádek neodpovídá predikátu spojení. Například:

select * from Sales S left outer join Customers C on S.Cust_Id = C.Cust_Id Cust_Id Item Cust_Id Cust_Name ----------- ---------- ----------- ---------- 2 Camera 2 John Doe 3 Computer 3 Jane Doe 3 Monitor 3 Jane Doe 4 Printer NULL NULL

Všimněte si, že server vrací NULL místo zákaznických dat, protože pro prodaný produkt ‚Tiskárna‘ neexistuje žádný odpovídající záznam zákazníka. Všimněte si posledního řádku, kde jsou chybějící hodnoty vyplněny NULL.

Pomocí úplného vnějšího spojení můžete najít všechny zákazníky (bez ohledu na to, zda si něco zakoupili) a všechny prodeje (bez ohledu na to, zda se shodovali se stávajícím zákazníkem):

select * from Sales S full outer join Customers C on S.Cust_Id = C.Cust_Id Cust_Id Item Cust_Id Cust_Name ----------- ---------- ----------- ---------- 2 Camera 2 John Doe 3 Computer 3 Jane Doe 3 Monitor 3 Jane Doe 4 Printer NULL NULL NULL NULL 1 Craig

Následující tabulka ukazuje, které ze spojených tabulek budou mít řádky v sadě výsledků (zbývající tabulka může mít NULL substituce) a pokrývá všechny typy vnějších spojení:

Levý vnější spoj B

Pravý vnější spoj B

Úplný vnější spoj B

Všechny linky A a B

Úplná vnější spojení jsou komutativní. Také „A levé vnější spojení B“ a „B pravé vnější spojení A“ jsou ekvivalentní.

Křížové spoje

Křížové spojení provede úplný kartézský součin dvou tabulek. To znamená, že se jedná o shodu mezi každým řádkem jedné tabulky a každým řádkem jiné tabulky. U křížového spojení nemůžete definovat predikát spojení pomocí klauzule ON, ačkoli můžete použít klauzuli WHERE k dosažení v podstatě stejného výsledku jako s vnitřním spojením.

Křížové spoje se používají poměrně zřídka. Nikdy byste neměli protínat dvě velké tabulky, protože to zahrnuje velmi nákladné operace a výsledkem je velmi velká sada výsledků.

select * from Sales S cross join Customers C Cust_Id Item Cust_Id Cust_Name ----------- ---------- ----------- ---------- 2 Camera 1 Craig 3 Computer 1 Craig 3 Monitor 1 Craig 4 Printer 1 Craig 2 Camera 2 John Doe 3 Computer 2 John Doe 3 Monitor 2 John Doe 4 Printer 2 John Doe 2 Camera 3 Jane Doe 3 Computer 3 Jane Doe 3 Monitor 3 Jane Doe 4 Printer 3 Jane Doe

POUŽÍT KŘÍŽEM

V SQL Server 2005 jsme přidali operátor CROSS APPLY, který umožňuje připojit tabulku k funkci s hodnotou tabulky (TVF), kde TVF bude mít parametr, který se bude měnit pro každý řádek. Například dotaz níže vrátí stejný výsledek jako vnitřní spojení zobrazené dříve, ale pomocí TVF a CROSS APPLY:

create function dbo.fn_Sales(@Cust_Id int) returns @Sales table (Item varchar(10)) as begin insert @Sales select Item from Sales where Cust_Id = @Cust_Id return end select * from Customers cross apply dbo.fn_Sales(Cust_Id) Cust_Id Cust_Name Item ----------- ---------- ---------- 2 John Doe Camera 3 Jane Doe Computer 3 Jane Doe Monitor

Můžeme také použít dotaz OUTER APPLY, který nám umožňuje najít všechny zákazníky bez ohledu na to, zda si něco koupili nebo ne. Bude to vypadat jako vnější spojení.

select * from Customers outer apply dbo.fn_Sales(Cust_Id) Cust_Id Cust_Name Item ----------- ---------- ---------- 1 Craig NULL 2 John Doe Camera 3 Jane Doe Computer 3 Jane Doe Monitor

Polospoj a protipolo spoj

Polospojení vrátí řádky pouze z jedné ze spojených tabulek, aniž by provedlo celé spojení. Anti-semi-join vrátí ty řádky z tabulky, které nejsou vhodné pro spojení s jinou tabulkou; těch. vrátí hodnotu NULL v normálním vnějším spojení.

Na rozdíl od jiných operátorů spojení neexistuje žádná explicitní syntaxe pro specifikaci provádění semi-spojení, ale SQL Server používá ve svém plánu provádění v mnoha případech semi-spojení. Například semi-spojení lze použít v plánu poddotazů s EXISTS:

select * from Customers C where exists ( select * from Sales S where S.Cust_Id = C.Cust_Id ) Cust_Id Cust_Name ----------- ---------- 2 John Doe 3 Jane Doe

Na rozdíl od předchozích příkladů vrací semi-spojení pouze zákaznická data.

Plán dotazů ukazuje, že SQL Server skutečně používá semi-spojení:

|—Vnořené smyčky(Left Semi Join, WHERE:([S].[Cust_Id]=[C].[Cust_Id]))
|—Skenování tabulky(OBJECT:([Zákazníci] AS [C]))
|—Skenování tabulky(OBJECT:([Prodej] AS [S]))

Existují levé a pravé polospojení. Levé poloviční spojení vrátí řádky z levé (první) tabulky, které odpovídají řádkům z pravé (druhé) tabulky, zatímco pravé poloviční spojení vrátí řádky z pravé tabulky, které odpovídají řádkům z levé tabulky.

Podobně lze anti-semi-join použít ke zpracování poddotazu s NOT EXISTS.

Přidání

Všechny příklady uvedené v tomto článku používaly predikáty spojení, které porovnávaly, zda jsou oba sloupce každé ze spojených tabulek stejné. Tento typ predikátu spojení se obvykle nazývá „spojení podle ekvivalence“. Možné jsou i jiné predikáty souvětí (např. nerovnice), nejběžnější jsou však spojky podle ekvivalence. SQL Server poskytuje mnoho alternativních možností pro optimalizaci spojení ekvivalence a optimalizaci spojení se složitějšími predikáty.

SQL Server je flexibilnější při výběru pořadí spojení a jeho algoritmu při optimalizaci vnitřních spojení než při optimalizaci vnějších spojení a CROSS APPLY. Pokud tedy vezmete dva dotazy, které se liší pouze tím, že jeden používá pouze vnitřní spojení a druhý používá vnější spojení a/nebo CROSS APPLY, SQL Server bude moci najít lepší plán provádění pro dotaz, který používá pouze vnitřní spojení.

• SQL Server
• operátory v plánu dotazů

• SQL
• Microsoft SQL Server