Inspiracja , wiedza , realizacja
Jsystems

W przebudowie

Login



Java

Oracle

Linux

Android

PostgreSQL

Microsoft SQL Server

Operatory zbiorowe i złączenia tabel

Rafał Czarkowski
Data dodania: Jun 21, 2016
Data aktualizacji: Jun 21, 2016

Wszystkich operatorów zbiorowych dotyczą te same zasady:

• Kolumny na tych samych pozycjach muszą być tego samego typu.

• Zapytania muszą zwracać tą samą liczbę kolumn.

• Nazwy kolumn w wyniku pobierane są z pierwszego zapytania.

• Sortować można tylko wynik całości



UNION ALL


powoduje, że w wyniku zapytania otrzymujemy sumę wszystkich wierszy, które znajdowały

się w obu zapytaniach składowych.


select ProductID, Name, ListPrice, ProductCategoryID

from SalesLT.Product where ProductID between 800 and 805

union all

select ProductID, Name, ListPrice, ProductCategoryID

from SalesLT.Product where ProductID between 803 and 808;




Utworzone zostały dwa zapytania, pierwsze zawierało produkty o identyfikatorze produktu w przedziale zamkniętym od 800 do 805, drugi natomiast przedziale zamkniętym od 803 do 808.

W wyniku zastosowania operatora union all otrzymaliśmy sumę wierszy wyświetloną przez oba zapytania. Widać to na produktach o identyfikatorze produktu 803, 804 i 805 – zostały one wyświetlone dwa razy.


UNION


Połączenie to powoduje, że w wyniku zapytania otrzymujemy sumę wszystkich wierszy, które znajdowały się w obu zapytaniach składowych, ale z wyniku końcowego eliminowane są wiersze,

które powtarzają się w obu zbiorach składowych.


select ProductID, Name, ListPrice, ProductCategoryID

from SalesLT.Product where ProductID between 800 and 805

union all

select ProductID, Name, ListPrice, ProductCategoryID

from SalesLT.Product where ProductID between 803 and 808;




Utworzone zostały dwa zapytania, pierwsze zawierało produkty o identyfikatorze produktu w przedziale zamkniętym od 800 do 805, drugi natomiast przedziale zamkniętym od 803 do 808.

W wyniku zastosowania operatora union otrzymaliśmy sumę wierszy wyświetloną przez oba zapytania, ale tym razem powtórzenia zostały wyeliminowane.


INTERSECT


Połączenie za pomocą operatora zbiorowego intersect wyświetla tylko te wiersze które wystąpiły w obu zbiorach - tj. część wspólna dwóch zbiorów.


select ProductID, Name, ListPrice, ProductCategoryID

from SalesLT.Product where ProductID between 800 and 805

intersect

select ProductID, Name, ListPrice, ProductCategoryID

from SalesLT.Product where ProductID between 803 and 808;




Utworzone zostały dwa zapytania, pierwsze zawierało produkty o identyfikatorze produktu w przedziale zamkniętym od 800 do 805, drugi natomiast przedziale zamkniętym od 803 do 808. 
W wyniku zastosowania operatora intersect otrzymaliśmy wiersze które występowały w obu zapytaniach.


EXCEPT


Połączenie przy pomocy minus eliminuje z pierwszego zbioru te rekordy, które wystąpiły w

drugim zbiorze.


select ProductID, Name, ListPrice, ProductCategoryID

from SalesLT.Product where ProductID between 800 and 805

except

select ProductID, Name, ListPrice, ProductCategoryID

from SalesLT.Product where ProductID between 803 and 808;




Utworzone zostały dwa zapytania, pierwsze zawierało produkty o identyfikatorze produktu w przedziale zamkniętym od 800 do 805, drugi natomiast przedziale zamkniętym od 803 do 808.

W wyniku zastosowania operatora except otrzymaliśmy wiersze z zapytania pierwszego, lecz z wyeliminowanymi wierszami które wystąpiły w drugim zapytaniu.


Złączenia tabel


Dane, które chcemy uzyskać z bazy danych, zazwyczaj znajdują się w więcej niż jednej tabeli bazodanowej. W takim przypadku nasze zapytanie musi się odpowiednio odwołać do więcej niż jednej tabeli. Mechanizm ten nazywa się w języku SQL ączeniami.


Połączenia INNER JOIN


Operator inner join jest jednym z najczęściej wykorzystywaych operatorów złączeń i w pewnym sensie stanowi on odpowiednik znaku równości pomiędzy tabelami. Przykładowo, jeśli w jednej tabeli mamy dane personalne osób (imiona, nazwiska) a w drugiej ich numery telefonów, to gdy chcemy wyświetlić w jedym wyświetleniu wyniku zapytania te informacje, musimy połączyć obie tabele, używając elementu wspólnego.


select ProductID, SalesLT.Product.Name, ListPrice, SalesLT.ProductCategory.Name 
from SalesLT.Product inner join SalesLT.ProductCategory on SalesLT.Product.ProductCategoryID=SalesLT.ProductCategory.ProductCategoryID;




W wyniku wyświetlenia otrzymaliśmy identyfikator produktu, nazwę produktu, cenę produktu, nazwę kategorii. Dane te otrzymaliśmy w wyniku złączenia dwóch tabel. Jako, że kolumny nazwy produktu i nazwy kategorii w swoich tabelach miały identyczne nazwy kolumn, należało wskazać jeszcze z której tabeli te kolumny pochodzą.


Słowo inner jest opcjonalne, możemy użyć samego słowa join i efekt będzie taki sam.

Dodatkowo, łączonym tabelom możemy nadać aliasy, by skrócić sobie pisanie przy wskazywaniu wyboru kolumn do połączenia i wyświetlenia. Należy jednak wtedy pamiętać, że jeśli już zaaliasowaliśmy tabelę, to potem już nie używamy jej nazwy, ale tylko i wyłącznie użytego aliasu.


select ProductID, T1.Name, ListPrice, T2.Name

from SalesLT.Product T1 join SalesLT.ProductCategory T2 
on T1.ProductCategoryID=T2.ProductCategoryID;




To samo wyświetlenie co poprzednio ale z użyciem aliasów.


Tabel możemy oczywiście łączyć kilka na raz.


select T1.Name NazwaProduktu, T2.Name NazwaKategorii, T3.Name NazwaModelu
from SalesLT.Product T1 join SalesLT.ProductCategory T2 
on T1.ProductCategoryID=T2.ProductCategoryID join SalesLT.ProductModel T3 on T1.ProductModelID=T3.ProductModelID;





Wyświetlenie które przedstawia dane pobrane z trzech różnych tabel. Kolumnom, które miały te same nazwy, zostały nadane aliasy, by było wiadomo jakie dane one zawierają.


Połączenia OUTER JOIN


Złączenie typu outer join, zwane inaczej złączeniami zewnętrznymi, pozwala nam na uwzględnienie w wyniku danych, które nie posiadają swoich odpowiedników w złączanych tabelach. Oznacza to, że jeśli w jednej tabeli pojawiają się wiersze, które nie posiadają odpowiedników w drugiej tabeli to zostaną wzięte pod uwagę podczas złączenia ale puste kolumny zostaną wypełnione wartościami null.


Aby wykonać takie złączenie musimy skorzystać z operatora złączenia zewnętrznego, czyli słowa outer, a także wskazać z której tabeli mają zostać pobrane wiersze, które nie posiadają dopasowania w innej tabeli. Tabelę taką skazujemy za pomocą słowa left, jeśli znajduje się ona na lewo od słów outer join w konstrukcji naszego zapytania lub right, jeśli tabela ta znajduje się po prawej stronie od tych słów. Opcjonalnie możemy zamiast słów left bądź right użyć słowa full, wtedy z obu tabel zostaną pobrane wszystkie rekordy, nawet jeśli nie będą one miały dopasowania.


select T1.Name, T1.ProductModelID, T2.Name, T2.ProductModelID

from SalesLT.Product T1 right outer join SalesLT.ProductModel T2

on T1.ProductModelID=T2.ProductModelID

where T2.ProductModelID between 37 and 42;




W tym przypadku dwie pierwsze kolumny zostały pobrane z tabeli SalesLT.Product, dwie pozostałe z SalesLT.Product. Wierszom 7,8 i 9, które zostały pobrane z „prawej” tabeli, w wyniku złączenia right outer join zostały przypisane wartości null w pierwszej i drugiej kolumnie w wyniku wyświetlenia.


Połączenia SELF JOIN


O złączeniu self join mówimy, gdy zachodzi potrzeba odwołania się w jednym zapytaniu dwukrotnie do tej samej tabeli a więc po lewej i prawej stronie złączenia występuje ta sama tabela. W tym typie złączeń za niezbędne uważa się użycie aliasów.


select T1.ProductCategoryID IdPodKategorii, T1.ParentProductCategoryId

IdKategorii, T1.Name Nazwapodkategorii, T2.Name NazwaKategorii

from SalesLT.ProductCategory T1 join SalesLT.ProductCategory T2

on T1.ParentProductCategoryID=T2.ProductCategoryID;




W tym zapytaniu nastąpiło połączenie złączenie tej samej tabeli. Wyświetlona została nazwa podkategorii, jej numer, nazwa tej podkategorii, i nazwa kategorii, która jest nad nią.


Połączenie CROSS JOIN


Tego typu złączenie zwróci iloczyn kartezjański wierszy z obu tabel. Złączenia cross join stosuje się bardzo rzadko, gdyż produkują bardzo dużą ilość wierszy, które nie zawierają logicznie spójnych informacji. Jeżeli w zapytaniu chcemy odwołać się do kolumny, która występuje w kilku tabelach, to, aby uniknąć niejednoznaczności, odwołanie to musi zawierać nazwę kolumny poprzedzoną nazwą tabeli.


select ProductID, T1.Name, T2.Name from SalesLT.Product T1

cross join SalesLT.ProductCategory T2;





select count(*) from SalesLT.Product;

select count(*) from SalesLT.ProductCategory;

select count(*) from SalesLT.Product cross join SalesLT.ProductCategory;




Jak widać na powyższym przykładzie, połączenie dwóch tabeli za pomocą cross join łączy każdy wiersz z pierwszej tabeli z każdym wierszem z drugiej tabeli.


Operator APPLY


Tak samo jak i inne operatory jest on używany w klauzuli from. Obsługiwane są dwa typy tego operatora – cross apply i outer apply. Operator cross apply implementuje tylko jedną logiczną fazę przetwarzania zapytania, natomiast operator outer apply stosuje dwie fazy. Operator ten działa na dwóch tabelach wejściowych, przy czym druga może być wyrażeniem tablicowym. Tak samo jak w przypadku złączeń, odwołuje się do nich jako tabeli „lewej” i „prawej”, z czego prawa tabela jest zazwyczaj tabelą pochodną.


CROSS APPLY


Operator cross apply stosuje jedną fazę logiczną przetwarzania zapytania – stosuje prawe wyrażenie tablicowe do każdego wiersza lewej tabeli i generuje tabelę wyników jako zunifikowany zbiór wyników.

Jeśli prawe wyrażenie tablicowe zawiera zbiór pusty, operator cross apply nie zwróci odpowiadającego lewego wiersza.


select T1.ProductID, T1.Name, T1.ListPrice, T2.SalesOrderID, T2.ProductID, T2.UnitPrice

from SalesLT.Product T1

inner join SalesLT.SalesOrderDetail T2 on T1.ProductID=T2.ProductID;




select T1.ProductID, T1.Name, T1.ListPrice, T3.* from SalesLT.Product T1

cross apply

(select SalesOrderID, ProductID, UnitPrice

from SalesLT.SalesOrderDetail T2 where T1.ProductID=T2.ProductID) T3;




OUTER APPLY


Operator outer apply w przeciwieństwie do operatora cross apply zwraca już wiersze z lewej tabeli, dla których prawe wyrażenie tablicowe zwraca zbiór pusty. Operator ten dodaje drugą fazę logicznego przetwarzania zapytanie, która identyfikuje wiersze z lewej strony, dla których prawe wyrażenie tablicowe zawiera pusty zbiór i dodaje te wiersze do tabeli wyników jako wiersze zewnętrzne – ze znacznikami null dla atrybutów prawej strony jako wypełniaczami. Tak więc faza ta jest podobna do fazy, która dodaje wiersze zewnętrzne w lewostronnym złączeniu zewnętrznym.


select T1.ProductID, T1.Name, T1.ListPrice, T2.SalesOrderID, T2.ProductID, T2.UnitPrice

from SalesLT.Product T1

left outer join SalesLT.SalesOrderDetail T2 on T1.ProductID=T2.ProductID;




select T1.ProductID, T1.Name, T1.ListPrice, T3.* from SalesLT.Product T1

cross apply

(select SalesOrderID, ProductID, UnitPrice

from SalesLT.SalesOrderDetail T2 where T1.ProductID=T2.ProductID) T3;