SQL/Typy złączeń

Typy złączeń, informacje wstępne

Złączenia możemy podzielić na wewnętrzne (INNER JOIN) oraz zewnętrzne (OUTER JOIN). Wewnętrzne złączenia (INNER JOIN) są domyślnym typem złączeń. Wyniki tych zapytań zawierają jedynie wiersze spełniające warunek.

Aby rozpocząć pracę z przykładami, zapoznajmy się z poniższymi tabelami, na podstawie których opisane są przykłady w dalszej części omawianego tematu.

Działy

id_działu	nazwa	adres
1	Marketing	Warszawa, Słoneczna 3
2	Księgowość	Warszawa, Słoneczna 3
3	Administracja IT	Warszawa, Słoneczna 3a
4	Programiści	Bydgoszcz, Kwiatowa 16

Pracownicy

id	imie	nazwisko	id_dzialu	pensja	projekt
1	Jan	Kowalski	2	2600
2	Adam	Nowak	3	3100	it1
3	Halina	Szańska	4	1800
4	Anna	Ryś	3	3000
5	Piotr	Lis	4	2000	p1
6	Paweł	Lis	4	1800	p2
7	Jan	Nowikowski	1	2200	it1
8	Adam	Kot	4	1900	p2

Projekty

p_id	szef_projektu	nazwa	termin_oddania
it1	2	Upgrade sprzętu	10.01.2009
p1	5	Program magazynowy	1.08.2009
p2	8	Statistica	20.02.2009

Klienci

k_id	nazwa	telefon	prac_kontaktowy
1	Acme S.A.	123-456-78-89	7
2	Społem S.A.	333-444-55-66	5
3	Józef Miałkowski	607-607-60-77	7
4	Zenon Kruk	(0-89) 545-03-43
5	Statics Sp.z o.o.	(0-22) 213-432-54	8

Wewnętrzne złączenia (INNER JOIN)

Notacja JOIN ON

W tej notacji w wersji podstawowej wpisujemy tabele w klauzuli FROM według schematu tabela1 JOIN tabela2 ON (warunek złączenia). W zależności od typu złączeń część ON (...) może zostać pominięta bądź zastąpiona wyrażeniem USING (warunek złączenia), słowo kluczowe JOIN może zostać rozszerzone do np. CROSS JOIN. Wszystkie modyfikacje tej notacji zostaną zaprezentowane przy okazji omawiania typów złączeń. UWAGA!!! Złączenie Natural Join oraz słowo kluczowe USING należą do Oracle i nie występują w MS SQL. Generalna składnia jest następująca:

SELECT tabela1.kolumna,tabela2.kolumna,...
FROM tabela1
[CROSS JOIN tabela2]
[NATURAL JOIN tabela2]
[JOIN tabela2 USING (nazwy_kolumn)]
[JOIN tabela2 ON (tabela1.kolumna = tabela2.kolumna)]
[LEFT|RIGHT|FULL OUTER JOIN tabela2 ON (tabela1.kolumna = tabela2.kolumna)];

Np.

SELECT p.nazwisko as Kierownik, p1.nazwa, p1.termin_oddania
FROM pracownicy p JOIN projekty p1
ON (p.id = p1.szef_projektu);

Złączenie typu INNER JOIN

Konstrukcja typu INNER JOIN jest złączeniem warunkowym o postaci:

SELECT kolumna1, kolumna2, ...,kolumnaN
FROM tabela1 [INNER] JOIN tabela2
ON wyrażenie_warunkowe;

W zapytaniu słowo INNER jest opcjonalne. W wyniku tego złączenia pojawią się tylko te wiersze z tabela tabela1 i tabela2, które spełnią warunki wymienione po klauzuli ON. Jest ona odpowiednikiem znanej instrukcji:

 SELECT kolumna1, kolumna2, ...,kolumnaN
 FROM tabela1, tabela2, ...,tabelaN
 WHERE warunki;

Złączenie typu Self JOIN

O złączeniu Self JOIN mówimy, gdy 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 d1.nazwa, d2.nazwa
 FROM dzialy d1 JOIN dzialy d2
 USING (adres)
 WHERE d1.nazwa != d2.nazwa;

 SELECT d1.nazwa, d2.nazwa
 FROM dzialy d1, dzialy d2
 WHERE d1.adres = d2.adres
 AND d1.nazwa != d2.nazwa;

Ostatnia linijka w kodzie zapobiega wyświetlaniu wierszy, w których w obu kolumnach będzie to samo (sensem kodu jest wyświetlanie par tych oddziałów, co mają ten sam adres)

Inny przykład

 SELECT k1.nazwa, k2.nazwa, k2.prac_kontakt
 FROM klienci k1 JOIN klienci k2
 ON (k1.prac_kontaktowy = k2.prac_kontaktowy)
 WHERE k1.nazwa != k2.nazwa;
 SELECT k1.nazwa, k2.nazwa, k2.prac_kontakt
 FROM klienci k1, klienci k2
 WHERE k1.prac_kontaktowy = k2.prac_kontaktowy
 AND k1.nazwa != k2.nazwa;

Wynik przykładu

nazwa	nazwa
księgowość	marketing
marketing	księgowość

Złączenie typu Equi-JOIN

Tak naprawdę jest to prawie każde złączenie które do tej pory rozpatrywaliśmy. Charakteryzuje się tym, że w warunku złączenia (czy to występującym w klauzuli ON czy też w warunku WHERE) występuje zwykły znak równości.

 SELECT p.nazwisko, p1.nazwa
 FROM pracownicy p JOIN projekty p1
 ON p.id = p1.szef_projektu;

 SELECT p.nazwisko, p1.nazwa
 FROM pracownicy p, projekty p1
 WHERE p.id = p1.szef_projektu;

Złączenie typu Natural Join

Ze złączeniem naturalnym mamy do czynienia, gdy obie kolumny występujące w warunku łączącym są tej samej nazwy. W przypadku tego typu złączenia możemy stosować jedną z czterech możliwych notacji. Najczęściej polecaną notacją jest JOIN ... USING, zaś najbardziej odradzaną jest notacja „NATURAL JOIN” gdyż może się trafić sytuacja gdy będzie więcej dopasowań kolumn o tej samej nazwie niż jedna. Wówczas efekt jest nieokreślony (co w praktyce oznacza że każdy dostawca baz danych ma swój własny tajny sposób poradzenia sobie z tym problemem).

UWAGA!!! Złączenie Natural Join oraz słowo kluczowe USING występuje w Oracle i PostgreSQL, nie występują w MS SQL.

 SELECT p.imie, p.nazwisko, d.nazwa
 FROM pracownicy p JOIN dzialy d
 ON p.id_dzialu = d.id_dzialu;

 SELECT p.imie, p.nazwisko, d.nazwa
 FROM pracownicy p JOIN dzialy d
 USING (id_dzialu);

 SELECT p.imie, p.nazwisko, d.nazwa
 FROM pracownicy p NATURAL JOIN dzialy d;

 SELECT p.imie, p.nazwisko, d.nazwa
 FROM pracownicy p, dzialy d
 WHERE p.id_dzialu = d.id_dzialu;

Wynik przykładu

p.imie	p.nazwisko	d.nazwa
Jan	Kowalski	Księgowość
Adam	Nowak	Administracja IT
Halina	Szańska	Programiści
Anna	Ryś	Administracja IT
Piotr	Lis	Programiści
Paweł	Lis	Programiści
Jan	Nowikowski	Marketing
Adam	Kot	Programiści

Złączenie typu Theta Join

Drugą stroną medalu dla równozłączeń są nierównozłączenia. Tym określeniem oznacza się złączenia w których w warunku występuje inny symbol porównania wartości niż =, np. >, BETWEEN, !=

 SELECT p1.nazwa, p2.nazwa, p2.termin_oddania
 FROM projekty p1 JOIN projekty p2
 ON (p1.termin_oddania >= p2.termin_oddania)
 WHERE p1.nazwa = 'Statistica';

 SELECT p1.nazwa, p2.nazwa, p2.termin_oddania
 FROM projekty p1, projekty p2
 WHERE p1.termin_oddania >= p2.termin_oddania
 AND p1.nazwa = 'Statistica';

Wynik przykładu

p1.nazwa	p2.nazwa	p2.termin_oddania
Statistica	Upgrade sprzętu	10.01.2009
Statistica	Statistica	20.02.2009

Złączenie typu Anti Join

Jest to szczególny przypadek nierównozłączeń, w którym łącznikiem jest operator !=

 SELECT d1.nazwa, d2.nazwa
 FROM dzialy d1 JOIN dzialy d2
 ON d1.adres != d2.adres;

 SELECT d1.nazwa, d2.nazwa
 FROM dzialy d1, dzialy d2
 WHERE d1.adres != d2.adres;

Złączenie typu Semi Join

O złączeniu częściowym mówimy, kiedy w klauzuli SELECT danego złączenia wymieniamy kolumny tylko z jednej z tabel. Ten typ złączenia służy głównie jako sposób filtrowania informacji. Zamiast tego typu często w tym samym celu stosuje się podzapytania.

 SELECT p.imie, p.nazwisko
 FROM pracownicy p JOIN klienci k
 ON p.id = k.prac_kontaktowy
 WHERE k.k_id >= 3;

 SELECT p.imie, p.nazwisko
 FROM pracownicy p, klienci k
 WHERE p.id = k.prac_kontaktowy
 AND k.k_id >= 3;

Przykład

imię	nazwisko
Jan	Nowikowski
Adam	Kot

Inny przykład:

 SELECT p.imie, p.nazwisko
 FROM pracownicy p JOIN projekty p1
 ON p.id = p1.szef_projektu
 WHERE p1.termin_oddania >= '01-JUL-2008';

 SELECT p.imie, p.nazwisko
 FROM pracownicy p, projekty p1
 WHERE p.id = p1.szef_projektu
 AND p1.termin_oddania >= '01-JUL-2008';

Złączenie typu CROSS JOIN

Złączenie CROSS JOIN jest to tzw. złączenie krzyżowe, którego ogólna postać wygląda następująco:

 SELECT kolumna1, kolumna2, ..., kolumnaN
 FROM tabela1 CROSS JOIN tabela2

gdzie N oznacza ostatnią kolumnę tabeli.

Wykonuje ono iloczyn kartezjański na podstawie łączonych tabel, inaczej mówiąc łączy każdy wiersz tabeli tabela1 z każdym wierszem tabeli tabela2. Jest ona odpowiednikiem znanej instrukcji:

 SELECT kolumna1, kolumna2, ...,kolumnaN
 FROM tabela1, tabela2

Przykład

 SELECT d.id_dzialu, p.p_id
 FROM dzialy d CROSS JOIN projekty p;

 SELECT d.id_dzialu, p.p_id
 FROM dzialy d, projekty p;

Wynik przykładu

id_dzialu	p_id
1	it1
2	it1
3	it1
4	it1
1	p1
2	p1
3	p1
4	p1
1	p2
2	p2
3	p2
4	p2

Zewnętrzne złączenia (OUTER JOIN)

Złączenie typu LEFT OUTER JOIN

Złączenie typu LEFT OUTER JOIN 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 pierwszej 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.

Konstrukcja typu LEFT OUTER JOIN:

SELECT kolumna1, kolumna2, ..., kolumnaN
FROM tabela1 LEFT [OUTER] JOIN tabela2
ON wyrażenie_warunkowe

Słowo OUTER jest tutaj opcjonalne.

Przykład dla baz danych Oracle:

SELECT k.nazwa, p.imie, p.nazwisko
FROM klienci k,pracownicy p
WHERE  k.prac_kontaktowy = p.id (+);

Wynik przykładu

nazwa	imie	nazwisko
Acme S.A.	Jan	Nowikowski
Społem S.A.	Piotr	Lis
Józef Miałkowski	Jan	Nowikowski
Statics Sp.z o.o.	Adam	Kot
Zenon Kruk	NULL	NULL

Złączenie typu RIGHT OUTER JOIN

Złączenie typu RIGHT OUTER JOIN działa analogicznie do LEFT OUTER JOIN ale w tabeli wynikowej uwzględnia wiersze z drugiej tabeli, które nie posiadają odpowiedników w pierwszej.

Konstrukcja typu RIGHT OUTER JOIN:

SELECT kolumna1, kolumna2, ... , kolumnaN
FROM tabela1 RIGHT [OUTER] JOIN tabela2
ON wyrażenie_warunkowe

Słowo OUTER jest tutaj opcjonalne.

Przykład dla baz danych Oracle:

SELECT k.nazwa, p.imie, p.nazwisko
FROM klienci k,pracownicy p
WHERE  k.prac_kontaktowy (+) = p.id;

Wynik przykładu

nazwa	imie	nazwisko
Acme S.A.	Jan	Nowikowski
Społem S.A.	Piotr	Lis
Józef Miałkowski	Jan	Nowikowski
Statics Sp.z o.o.	Adam	Kot
NULL	Jan	Kowalski
NULL	Adam	Nowak
NULL	Halina	Szańska
NULL	Anna	Ryś
NULL	Paweł	Lis

Złączenie typu FULL OUTER JOIN

Złączenie obustronne jest w pewnym sensie sumą złączenia lewostronnego i prawostronnego. Zawiera wszystkie wiersze obu złączonych tabel, w tym te które nie mają swoich odpowiedników. W tym typie

SELECT k.nazwa, p.imie, p.nazwisko
FROM klienci k FULL OUTER JOIN pracownicy p
ON (k.prac_kontaktowy = p.id);

Przykład

nazwa	imię	nazwisko
Acme S.A.	Jan	Nowikowski
Społem S.A.	Piotr	Lis
Józef Miałkowski	Jan	Nowikowski
Statics Sp.z o.o.	Adam	Kot
Zenon Kruk	NULL	NULL
NULL	Jan	Kowalski
NULL	Adam	Nowak
NULL	Halina	Szańska
NULL	Anna	Ryś
NULL	Paweł	Lis

Inny przykład:

SELECT p.nazwa as projekt, p.szef_projektu, k.nazwa, k.prac_kontaktowy
FROM projekty p FULL OUTER JOIN klienci k
ON (p.szef_projektu = k.prac_kontaktowy);

Bibliografia

• Marcin Lis, PostgreSQL 8.3, Gliwice, Helion, 2008 ISBN 978-83-246-0859-1.