SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Łączenie tabel – pobieranie danych z wielu źródeł

W klauzuli FROM, określamy przede wszystkim źródła (zbiory) z których chcemy pobierać dane. Możliwości w zakresie pobierania danych z jednego zbioru, opisuję w artykule dot. źródeł danych stosowanych we FROM.

W zapytaniu SQL możemy odwoływać się do jednego lub wielu zbiorów. Jeśli chcemy wybierać z przynajmniej dwóch, powinniśmy określić sposób ich połączenia oraz warunki.

W artykule tym, znajdziesz opis pełnego zakresu możliwości FROM – czyli wszystkie sposoby łączenia tabel, zbiorów oraz określania warunków złączeń. Pamiętaj, że mogą to być tabele, widoki, wspólne wyrażenia tablicowe (CTE), zmienne i funkcje tabelaryczne czy podzapytania. Dla uproszczenia, będę je nazywał wymiennie – tabele / zbiory – mając na myśli wszystkie obiekty tabelaryczne, do których możemy odwoływać się we FROM.

Łączenie tabel SQL – zasady ogólne

Dla przypomnienia, FROM jest pierwszym krokiem przetwarzania zapytania. Każdy kolejny, bazuje na pośredniej, wynikowej tabeli wirtualnej, poprzedniego. Zbiór otrzymany po przetworzeniu całego kroku (np. FROM) jest wejściem, następnego (WHERE).

Niezależnie od wybranego typu złączenia, w wyniku przetwarzania FROM, otrzymujemy zawsze zbiór elementów (virtual table VT), opisany za pomocą wszystkich kolumn tabel wejściowych. Nie ma znaczenia czy łączysz dwie czy więcej tabel połączeniem wewnętrznym, zewnętrznym. Elementy (rekordy, wiersze) tabeli wynikowej, będą określone zawsze przez wszystkie atrybuty (kolumny) łączonych zbiorów.

Przykładowo, zbiór wynikowy (VT) operacji łączenia trzech tabel złączeniem wewnętrznym INNER JOIN, będzie opisany przez wszystkie kolumny, trzech tabel wejściowych.
JOIN_02
Inną sprawą jest to, czy będziemy chcieli wszystkie z nich zwracać w kwerendzie. Pewnie nie, ale to określamy dopiero w SELECT. Mamy, więc wyjaśnioną pierwszą kwestię – strukturę zbioru wynikowego tabeli pośredniej.

Po wybraniu tabel, musimy określić jeszcze typ złączenia oraz ich warunki. Tutaj stosujemy w praktyce wiedzę na temat relacyjności bazy – czyli sposobów powiązań tabel między sobą.

Łączenie wielu zbiorów (trzech i więcej) sprowadza się do wielokrotnego wykonania operacji łączenia dwóch tabel
. W kolejnym rozdziale tego kursu, opisuję szczegóły przetwarzania zapytań do wielu tabel.

Zaprezentuję teraz po kolei wszystkie możliwe typy złączeń dwóch tabel, na przykładzie prostego scenariusza.

Istnieje firma X, której część pracowników (tabela EMP) posiada samochód służbowy (tabela CAR). W firmie samochody służbowe mogą być używane przez różne osoby, ale tylko jedna jest bezpośrednio przypisana i jest w pełni za niego odpowiedzialna.
Ponieważ firma dynamicznie się rozwija, część samochodów stoi na placu – nie przypisana jeszcze do nikogo. Obowiązkiem każdego pracownika, z wyjątkiem BOSS’a, jest prowadzenie comiesięcznego rozliczania użytkowanego pojazdu (tabela HIST)

BaseStructure
Kod źródłowy struktur na których prezentuję poszczególne przykłady, do pobrania tutaj.


INNER JOIN – łączenie wewnętrzne

W wyniku złączenia wewnętrznego (INNER JOIN) otrzymujemy tabelę wynikową (VT), składającą się ze wszystkich kolumn tabel wejściowych.

Tabela wynikowa zawierać będzie tylko takie elementy, dla których warunki złączenia wewnętrznego będą spełnione (w logice trójwartościowej, wynik musi być TRUE).
Klauzula FROM wraz z warunkami określonymi w ON jest pierwszym miejscem filtrowania rekordów. Wszystkie elementy dla których wynik nie będzie spełniony (FALSE oraz UNKNOWN), zostaną odrzucone.
INNER_JOIN

Logika łączenia tabel INNER JOIN

Zrozumienie zasad działania złączeń wewnętrzych jest kluczowe. Jest to część wspólna wszystkich typów złączeń INNER oraz OUTER JOIN.

W pierwszym kroku wykonywany jest iloczyn kartezjański obu tabel. Jest to połączenie każdego elementu zbioru A ze wszystkimi zbioru B.

Może to być dla Ciebie trudno do zaakceptowania :), ale uspokoję Cię – logiczne przetwarzanie zapytania a fizyczna jego realizacja to zupełnie co innego. Silnik relacyjny świetnie sobie z tym radzi. Nie oznacza to, że za każdym razem odwołując się do tabeli czytane są wszystkie jej rekordy. Jednak patrząc przez pryzmat zasad panujących w SQL, kroków logicznych – zakładamy, że tak właśnie jest.

Wiem że jeśli choć raz zdarzyło Ci się napisać lub spotkać z iloczynem kartezjańskim, tym trudniej będzie Ci ten fakt zaakceptować. Pod koniec tego akapitu – udowodnię tą zasadę za pomocą prostego przykładu, które napewno Cię przekona.

Po wyznaczeniu iloczynu kartezjańskiego, dla każdego wiersza obliczany jest wynik warunku (lub warunków) określonych w ON. Tu spotykamy się z logiką trójwartościową. Wynik może być spełniony = TRUE, niespełniony = FALSE lub nieznany czyli UNKNOWN (np. porównanie z NULL – więcej na ten temat w artykule o NULL).

Ostatnim krokiem jest odrzucenie wszystkich wierszy niespełniających warunków w ON. W zbiorze wynikowym zostają tylko te elementy, dla których wynik = TRUE.

Całość procesu łączenia wewnętrznego obrazuje poniższy diagram :
INNER_JOIN_01

Załóżmy więc, że w firmie X potrzebny jest raport z informacjami o pracownikach, którzy mają przypisany samochód służbowy.

SELECT * 
FROM dbo.EMP as e INNER JOIN dbo.CAR as c ON e.IdPrac=c.IdPrac

INNER_JOIN_02

INNER_JOIN_03

INNER JOIN jest złączeniem symetrycznym i nie ma specjalnego znaczenia czy łączymy tabelę A z B czy odwrotnie. Podobnie z warunkami w ON. Dla porządku, sensownie jest jednak zachować kolejność atrybutów w ON po tej samej stronie co określenie tabel źródłowych wobec operatora JOIN.

Poniższe warunki łączenia są równoważne :

FROM dbo.EMP e INNER JOIN dbo.CAR c ON e.IdPrac=c.IdPrac
 
FROM dbo.EMP e INNER JOIN dbo.CAR c ON c.IdPrac=e.IdPrac
 
FROM dbo.CAR c INNER JOIN dbo.EMP e ON e.IdPrac=c.IdPrac
 
FROM dbo.CAR c INNER JOIN dbo.EMP e ON c.IdPrac=e.IdPrac

Potencjalne problemy i błędy

To w jaki sposób zapiszemy warunki złączeń, podobnie jak w WHERE może mieć wpływ na wydajność (stosowanie funkcji etc.).

Generalnie INNER JOIN raczej nie stwarza kłopotów. Przyjrzyjmy się jednak tak zapisanemu warunkowi złączenia :

SELECT * 
FROM dbo.EMP e INNER JOIN dbo.CAR c ON e.IdPrac = 1

INNER_JOIN_04

Ponieważ tylko jeden wiersz tabeli dbo.EMP spełnia warunek określony w ON (IdPrac=1), zostaną zwrócone wszystkie wiersze będące wynikiem iloczynu kartzjańskiego tego wiersza z całym zbiorem rekordów tabeli dbo.CAR.

Dowodzi to, że faktycznie wykonywany jest iloczyn kartezjański w logicznym przetwarzaniu złączeń. Najpierw A x B, potem dopiero filtrowanie. Możemy pójść dalej i zapisać warunek w tym przkłądzie jako ON 1=1 – wtedy pełen iloczyn kartezjański gwarantowany bo nic nie zostanie odfiltrowane.


OUTER JOIN – łączenie zewnętrzne

Realizacja dowolnych połączeń zewnętrznych jest wykonywana, w pewnym zakresie, dokładnie tak samo jak wewnętrzne. Trzy pierwsze kroki logicznego przetwarzania są identyczne.

  1. Najpierw wykonywany jest iloczyn kartezjański tabeli A oraz tabeli B (łączymy każdy z każdym).
  2. Dla każdego wiersza, określany jest wynik warunków połączeń (zdefiniowane w ON) – TRUE, FALSE lub UNKNOWN.
  3. Następnie usunięcie wszystkich elementów z pośredniego zbioru wynikowego, dla których wynik połączenia (z p.2) jest różny od TRUE/

W połączeniach wewnętrznych to było wszystko. W zewnętrznych dodany jest jeszcze jeden krok.

W zależności od typu – LEFT, RIGHT lub FULL JOIN, wykonywane jest dopełnienie zbioru, o wszystkie elementy tabeli występującej po LEWEJ, PRAWEJ lub OBYDWU operatora JOIN, dla których wynik warunków nie był spełniony (FALSE lub UNKNOWN).

Brzmi to może trochę zawile, ale jest naprawdę proste i jeśli wiesz już jak działa połączenie wewnętrzne – tutaj dojdzie tylko ten jeden, dodatkowy krok. Zerknij na poniższe przykłady i z pewnością wszystko stanie się jasne.

LEFT OUTER JOIN – połączenie lewostronne otwarte

Postępujemy identycznie jak w INNER JOIN. Na koniec uzupełniamy zbiór wynikowy (INNER JOIN to tylko element C) o wszystkie elementy tabeli stojącej po LEWEJ stronie operatora JOIN (będą to rekordy A oraz B).

Ponieważ wiersze dopełniające muszą być również opisane, przez wszystkie kolumny łączonych tabel.- wartości atrybutów w tym przypadku TabeliB (po prawej stronie JOINa) będą nieznane czyli będą po prostu NULLami.

LEFT_JOIN_01

W naszym scenariuszu, niech będzie to zapytanie wyciągające dane o wszystkich pracownikach pracujących w Firmie oraz informacja czy dany pracownik ma przypisany samochód służbowy.

SELECT e.Imie, e.Nazwisko, e.Stanowisko , c.Marka 
-- LEFT JOIN oraz LEFT OUTER JOIN oznaczają dokładnie to samo
FROM dbo.EMP e LEFT JOIN dbo.CAR c ON e.IdPrac=c.IdPrac

LEFT_JOIN_02

RIGHT OUTER JOIN – łączenie zewnętrzne prawostronne

Ta sama historia co z LEFT JOIN tylko w drugą stronę :). Łączone są najpierw wewnętrznie dwa zbiory (INNER JOIN), na koniec dodawane są wszystkie niedopasowane elementy tabeli po PRAWEJ stronie operatora JOIN (elementy D oraz E). Ponieważ wartości kolumn tabeli po lewej stronie są nieznane, będą NULLami.

RIGHT_JOIN_01

W naszym scenariuszu może to być pytanie o szczegóły wszystkich samochodów służbowych, wraz z dodatkową informacją o osobie przypisanej

select  c.Marka, c.NrRej, c.Rocznik, e.Imie + ' ' + e.Nazwisko as Pracownik
from dbo.EMP e RIGHT JOIN dbo.CAR c on e.IdPrac=c.IdPrac

RIGHT_JOIN_02

Jak łatwo zauważyć, złączenia zewnętrzne LEFT i RIGHT nie są symetryczne. Wynik zależy od pozycji tabel względem operatora JOIN. Nie ma znaczenia zapis warunków (czyli to co jest po ON). Powyższe zapytanie, moglibyśmy zapisać równie dobrze jako połączenie LEFT JOIN i wynik będzie identyczny.

-- zamieniłem tylko kolejność tabel CAR i EMP oraz użyłem LEFT JOIN
SELECT  c.Marka, c.NrRej, c.Rocznik, e.Imie + ' ' + e.Nazwisko as Pracownik
FROM    dbo.CAR c LEFT JOIN dbo.EMP e on e.IdPrac=c.IdPrac

FULL OUTER JOIN – pełne złączenie zewnętrzne

Jeśli wiesz już jak działają INNER, LEFT, RIGHT – to wiesz także jak działa FULL JOIN ! Dopełnieniem zbioru wynikowego są wszystkie elementy obydwu łączonych zbiorów. Podobnie jak poprzednio, wartości nieznanych nie wymyślimy. Elementy dopełniające zbioru A (stojącego po lewej stronie operatora JOIN), będą miały uzupełnione wartości atrybutów tabeli B NULLami. Analogicznie będzie z dopełnieniem drugiego zbioru.

FULL_JOIN_01
W naszym przykładzie będzie to zapytanie zwracające informacje o pełnej relacji – wszystkich pracownikach i samochodach, zgodnie z ich przypisaniem.

SELECT e.Imie, e.Nazwisko, e.Stanowisko , c.Marka 
-- FULL JOIN to skrót od FULL OUTER JOIN 
FROM dbo.EMP e FULL JOIN dbo.CAR c ON e.IdPrac=c.IdPrac

FULL_JOIN_02

CROSS JOIN – iloczyn kartezjański

Raczej rzadko stosowane połączenie zbiorów. Jego sposób działania jest banalny – łączy każdy wiersz tabeli A z każdym wierszem tabeli B. Jako jedyne nie ma możliwości utworzenia warunków połączenia w ON bo z założenia ma połączyć wszystko ze wszystkim.

Skutek łączenia dwóch tabel zawierających po 100 rekordów – to tabela z 10000 wierszami, opisanych za pomocą wszystkich kolumn. Więc jeśli chcesz przetestować wytrzymałość DBA, możesz śmiało spróbować połączyć kilka średniej wielkości tabel :)

Złączenie typu CROSS JOIN jest realizowane również wtedy, gdy wyszczególnimy tabele we FROM, separując je tylko przecinkiem.

SELECT *
FROM dbo.EMP, dbo.CAR

Dlatego powinniśmy unikać stosowania warunków połączeń w WHERE (bardzo stary sposób, niezgodny z ANSI SQL:92).

SELF JOIN – połączenie tabeli z samą sobą

Wszystkie do tej pory prezentowane przykłady, zakładały łączenie dwóch różnych zbiorów. Język SQL jest elastyczny i jeśli coś jest zbiorem, może być użyte we FROM wiele razy.

Połączenia typu SELF JOIN to zawsze jedno z już poznanych – INNER, CROSS lub OUTER JOIN, w T-SQL nie stosuje się zapisu SELF JOIN. W strukturze tabeli dbo.EMP firmy X mamy zdefiniowaną referencję pomiedzy kolumnami IdManager i IdPrac.

select IdPrac, Imie, Nazwisko, Stanowisko, IdManager 
from EMP

SELF_JOIN_00
Wyświetlmy podstawowe dane dla wszystkich pracowników wraz z informacją o bezpośrednim przełożonym.
Ponieważ jest jedna osoba (BOSS), która nie ma przełożonego, musimy w tym zadaniu zastosować połączenie zewnętrzne. Aby móc połączyć dwie te same tabele, koniecznie musimy zastosować aliasy (e1 i e2).

SELECT e1.Imie + ' ' + e1.Nazwisko as Pracownik, e1.Stanowisko, 
	e2.Imie + ' ' + e2.Nazwisko as Manager, e2.Stanowisko as ManStanowisko
FROM dbo.EMP e1 LEFT OUTER JOIN dbo.EMP e2 on e1.IdManager=e2.IdPrac

SELF_JOIN_01

Ten przykład bazuje na istniejącej relacji, kluczu obcym tabeli dbo.EMP do samej siebie, jednak wcale nie musi ta referencja być jawnie i permanentnie określona.

Wszystkie do tej pory prezentowane przykłady, łączyły tabele w naturalny sposób ich powiązań. Po kolumnach będącymi jednocześnie kluczami obcymi/podstawowymi tabel.

Ogólną zasadą łączenia jest możliwość jej realizacji po dowolnych kolumnach. Musi być spełniony tylko jeden warunek – kompatybilność typów danych łączonych atrybutów. To jak zapiszemy warunek i czy będzie miał sens, zależy tylko od nas – język SQL nie ogranicza tu naszej wyobraźni.

Dodatkowo, na wartościach atrybutów po których łączymy, możemy wykonywać dowolne operacje. Przetwarzać je za pomocą funkcji skalarnych, wykonywać działania arytmetyczne, łączenia stringów etc…

Na koniec bardzie „zaawansowany” przykład, łączący różne techniki pisania zapytań prezentowane w tym rozdziale kursu. Będzie obrazował połączenie wewnętrzne typu SELF JOIN tabeli dbo.HIST w której przechowywane są informacje o przebiegu samochodów w postaci „logu”.

Scenariusz jest taki, że co miesiąc, każdy z pracowników, musi wpisać stan licznika swojego samochodu służbowego. Zapytanie ma za zadanie wyświetlenie raportu o przebiegach miesięcznych, każdego samochodu za okres wakacyjny. W tym celu posłużę się technikami wspólnych wyrażeń tablicowych, łączeniem wewnętrznym oraz funkcją ROW_NUMBER(). Zauważ, że wykonuję operacje arytmetyczne na atrybutach łączących (dodaje 1, aby uzyskać przesunięcie odczytów) oraz łączę kilka warunków w klauzuli ON.

WITH LogTab as (
   -- mozesz uruchomić testowo tylko zawartość CTE, 
   -- żeby sprawdzić co zwraca i co będzie sednem (tabela LogTab) której potem używam
   SELECT  * , ROW_NUMBER() OVER(partition by NrRej order by DtPomiaru) as IdUniqueRange  
   FROM  dbo.HIST
   where DtPomiaru between '2012-06-01' and '2012-08-31'
 
)
 
SELECT  l1.NrRej, YEAR(l1.DtPomiaru) as Rok,MONTH(l1.DtPomiaru) as Miesiac, 
	l1.Przebieg as PrzebiegStart,l2.Przebieg as PrzebiegEND,
        l1.Przebieg - l2.Przebieg as Delta
FROM  LogTab l1 INNER JOIN LogTab l2 
	   ON l1.IdUniqueRange = l2.IdUniqueRange+1 and l1.NrRej = l2.NrRej
ORDER BY l1.NrRej, l1.DtPomiaru

SELF_JOIN_02


Zgodność składni łączenia tabel ze standardami ANSI SQL

W praktyce można spotkać różne możliwości określania warunków złączeń.

Przedstawione w tym artykule (INNER, LEFT OUTER, CROSS, warunek ON) są zgodne ze standardem ANSI SQL:92 i powinny być stosowane w produkcyjnych bazach. Czysto informacyjnie, istnieją inne, starsze metody zapisu, które jednak nie powinny być już stosowane.

Łączenie wewnętrzne z warunkiem zamiast w ON – w WHERE.

-- równoważnik INNER JOIN - warunek połączenia dopiero w filtrowaniu w WHERE 
-- UNIKAĆ !!! bo łatwo o pomyłkę i cartesian product gotowy :)
SELECT e.Imie, e.Nazwisko, e.Stanowisko , ISNULL(c.Marka , '-') as Samochod
FROM dbo.EMP e , dbo.CAR c 
WHERE e.IdPrac = c.IdPrac

5 thoughts on “Łączenie tabel – pobieranie danych z wielu źródeł

Leave a comment

Twój adres e-mail nie zostanie opublikowany.


siedem × 7 =

Możesz użyć następujących tagów oraz atrybutów HTML-a: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>