Join (SQL)
terminy typu "theta join" nie występują w języku SQL; szybkie googlowanie wskazuje raczej na teorię zbiorów, co jest poza tematem tego artykułu,
→ usunąć/zweryfikować prawdopodobną twórczość własną.
Po wyeliminowaniu niedoskonałości należy usunąć szablon {{Dopracować}} z tego artykułu.
Join (SQL) – klauzula wykorzystywana do łączenia danych znajdujących się w różnych tabelach[1].
Typy złączeń
Wyróżniamy następujące typy złączeń:
- Wewnętrzne (inner) - domyślny typ złączeń. Wyniki tych zapytań zawierają jedynie wiersze spełniające podany warunek.
- Iloczyn kartezjański (Cross join)
- Samozłączenie (Self Join)
- Równozłączenie (Equi-join)
- Naturalne (Natural Join)
- Theta, nierównozłączenia (Theta Join)
- Antyzłączenia (Anti join)
- Częściowe (Semi Join)
- Zewnętrzne (outer)
- Left outer Join
- Right outer Join
- Full outer Join
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.
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 |
id | imie | nazwisko | id_dzialu | pensja | projekt |
---|---|---|---|---|---|
1 | Jan | Kowalski | 2 | 2600 | |
2 | Adam | Nowak | 3 | 3100 | it1 |
3 | Halina | Szańska | 4 | 5800 | |
4 | Anna | Ryś | 3 | 3000 | |
5 | Piotr | Lis | 4 | 5000 | p1 |
6 | Paweł | Lis | 4 | 5800 | p2 |
7 | Jan | Nowikowski | 1 | 2200 | it1 |
8 | Adam | Kot | 4 | 5900 | p2 |
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 |
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 |
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ń.
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 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 wymienionie 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ń niezbędne jest 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, które 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;
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 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. 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 system baz danych ma swój własny sposób poradzenia sobie z tym problemem).
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 imie, nazwisko, 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;
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';
nazwa | nazwa | 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;
imię | nazwisko |
---|---|
Jan | Nowikowski |
Adam | Kot |
Inny przykład:
SELECT p.imie, p.nazwisko FROM pracownicy 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;
id_dzialy | 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 |
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.
(inna wersja Oracle)
SELECT k.nazwa, p.imie, p.nazwisko FROM klienci k,pracownicy p WHERE k.prac_kontaktowy = p.id (+);
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.
(inna wersja Oracle)
SELECT k.nazwa, p.imie, p.nazwisko FROM klienci k,pracownicy p WHERE k.prac_kontaktowy (+) = p.id;
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);
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);
Przypisy
- ↑ Klauzula JOIN w zapytaniach SQL [online], Samouczek Programisty, 20 listopada 2018 [dostęp 2020-05-03] (pol.).
Bibliografia
- MarcinM. Lis MarcinM., PostgreSQL 8.3. Ćwiczenia, Gliwice: Helion, 2008, ISBN 978-83-246-0859-1, OCLC 749652976 .
- Marcin Lis: SQL. Ćwiczenia praktyczne, Gliwice, Helion, 2006 ISBN 83-246-0621-1
Zobacz też
- Język SQL
Linki zewnętrzne
- http://sql-joins.leopard.in.ua/ Graficzny wizualizator JOIN-ów