To jest jeden z artykułów w ramach praktycznego kursu SQL. Proszę zapoznaj się z pozostałymi częściami, mogą one być pomocne w zrozumieniu materiału z tego artykułu.
Każde zapytanie z kursu możesz wykonać samodzielnie. Potrzebujesz do tego środowiska opisanego w pierwszym artykule kursu. Bardzo mocno Cię do tego zachęcam. Moim zdaniem najwięcej nauczysz się samodzielnie eksperymentując z zapytaniami.
W tym artykule, jeśli wyraźnie nie zaznaczyłem inaczej, korzystam z bazy danych SamouczekJoin.sqlite.
Odrobina teorii
Zanim przejdę do omówienia klauzuli JOIN. Muszę powiedzieć Ci coś więcej na temat modelu relacyjnego. Jeśli nie udało Ci się przeczytać artykułu dotyczącego wprowadzenia do relacyjnych baz danych to najwyższy czas to nadrobić. Ten artykuł będzie wymagał materiału, który tam opisałem.
Poza materiałem z tamtego artykułu musisz poznać jeszcze jedno pojęcie. Mam na myśli iloczyn kartezjański.
Iloczyn kartezjański
Wyobraź sobie dwie tabele. Jedna zawiera imiona męskie:
SELECT *
FROM imiona_meskie;
id imie
---------- ----------
1 Piotr
2 Tomasz
3 Jan
SQLite pozwala na takie formatowanie wyników po użyciu .header on in .mode column
Druga żeńskie:
SELECT *
FROM imiona_zenskie;
id imie
---------- ----------
11 Anna
12 Monika
13 Zofia
Iloczynem kartezjańskim będzie zbiór, w którym każdy wiersz z pierwszej tabeli połączony będzie z każdym wierszem w drugiej tabeli. W związku z tym, że w każdej z tabel są 3 wiersze, wynikowa tabela1 będzie miała ich 9:
id imie id imie
---------- ---------- ---------- ----------
1 Piotr 11 Anna
1 Piotr 12 Monika
1 Piotr 13 Zofia
2 Tomasz 11 Anna
2 Tomasz 12 Monika
2 Tomasz 13 Zofia
3 Jan 11 Anna
3 Jan 12 Monika
3 Jan 13 Zofia
Taki iloczyn kartezjański możesz wyprodukować używając zapytania:
SELECT *
FROM imiona_meskie
,imiona_zenskie;
Nazwy kolumn
Jak widzisz nazwy kolumn są w nim powtórzone. Pierwsze dwie id i imie pochodzą z tabeli imiona_meskie. Dwie kolejne, o tych samych nazwach pochodzą z tabeli imiona_zenskie.
W takim przypadku aby wybrać kolumnę, z konkretnej tabeli konieczne jest poprzedzenie jej nazwą tabeli. Na przykład:
SELECT imiona_meskie.id AS id_m
,imiona_zenskie.imie AS imie_z
,imiona_zenskie.*
FROM imiona_meskie
,imiona_zenskie;
id_m imie_z id imie
---------- ---------- ---------- ----------
1 Anna 11 Anna
1 Monika 12 Monika
1 Zofia 13 Zofia
2 Anna 11 Anna
2 Monika 12 Monika
2 Zofia 13 Zofia
3 Anna 11 Anna
3 Monika 12 Monika
3 Zofia 13 Zofia
W przykładzie tym użyłem także konstrukcji imiona_zenskie.*, która zwraca wszystkie kolumny z tabeli imiona_zenskie.
Pobierz opracowania zadań z rozmów kwalifikacyjnych
Przygotowałem rozwiązania kilku zadań algorytmicznych z rozmów kwalifikacyjnych. Rozkładam je na czynniki pierwsze i pokazuję różne sposoby ich rozwiązania. Dołącz do grupy ponad 6147 Samouków, którzy jako pierwsi dowiadują się o nowych treściach na blogu, a prześlę je na Twój e-mail.
Typy złączeń
Złączenia będę omawiał na przykładzie dwóch tabel bajka i postac. bajka zawiera dwie kolumny:
ididentyfikator bajki, klucz główny,tytultytuł bajki.
SELECT *
FROM bajka;
id tytul
---------- --------------------
1 101 Dalmatyńczyków
2 Flinstonowie
3 Jetsonowie
4 Epoka lodowcowa
5 Rozbójnik Rumcajs
6 Muminki
7 Smerfy
postac to tabela z trzema kolumnami:
ididentyfikator postaci, klucz główny,bajka_ididentyfikator bajki, w której postać występuje, klucz obcy do tabelibajka,imieimię postaci.
SELECT *
FROM postac;
id bajka_id imie
---------- ---------- ----------
1 1 Czika
2 1 Pongo
3 2 Wilma
4 2 Fred
5 4 Elka
6 4 Maniek
7 6 Migotka
8 6 Muminek
9 Maja
10 Gucio
11 Fiona
12 Shrek
W złączeniach tabel istotne są klucze główne i klucze obce. Tutaj jeszcze raz odsyłam Cię do artykułu opisującego model relacyjny2.
Znasz już dwa sposoby łączenia danych z wielu tabel. Iloczyn kartezjański i klauzule UNION oraz UNION ALL. O ile wyniki uzyskiwane przez złączenie wyników kilku zapytań przy pomocy UNION lub UNION ALL mogą być przydatne to iloczyn kartezjański zbyt użyteczny nie jest. SQL daje możliwość łączenia danych z wielu tabel na kilka innych sposobów.
Do uzyskania wszystkich rodzajów złączeń podstawą jest iloczyn kartezjański. Z takiego iloczynu odrzucane są następnie wiersze, które “nie pasują” do złączenia danego typu3.
INNER JOIN
Podstawowym rodzajem złączenia jest INNER JOIN. Z iloczynu kartezjańskiego wybiera ono te wiersze, dla których warunek złączenia jest spełniony. W żadnej z łączonych tabel kolumna użyta do łączenia nie może mieć wartości NULL. Na przykład:
SELECT *
FROM bajka INNER JOIN postac
ON bajka.id = postac.bajka_id;
To zapytanie zwraca wszystkie kolumny z tabel bajka i postac. Zwrócone są tylko te wiersze, dla których wartość kolumn bajka.id i postac.bajka_id jest równa:
id tytul id bajka_id imie
---------- -------------------- ---------- ---------- ----------
1 101 Dalmatyńczyków 1 1 Czika
1 101 Dalmatyńczyków 2 1 Pongo
2 Flinstonowie 3 2 Wilma
2 Flinstonowie 4 2 Fred
4 Epoka lodowcowa 5 4 Elka
4 Epoka lodowcowa 6 4 Maniek
6 Muminki 7 6 Migotka
6 Muminki 8 6 Muminek
W zrozumieniu tego co się tutaj stało może pomóc rzucenie okiem na iloczyn kartezjański (jakie zapytanie wyprodukuje taki iloczyn kartezjański?):
id tytul id bajka_id imie
---------- -------------------- ---------- ---------- ----------
1 101 Dalmatyńczyków 1 1 Czika
1 101 Dalmatyńczyków 2 1 Pongo
1 101 Dalmatyńczyków 3 2 Wilma
1 101 Dalmatyńczyków 4 2 Fred
1 101 Dalmatyńczyków 5 4 Elka
1 101 Dalmatyńczyków 6 4 Maniek
1 101 Dalmatyńczyków 7 6 Migotka
1 101 Dalmatyńczyków 8 6 Muminek
1 101 Dalmatyńczyków 9 Maja
1 101 Dalmatyńczyków 10 Gucio
1 101 Dalmatyńczyków 11 Fiona
1 101 Dalmatyńczyków 12 Shrek
2 Flinstonowie 1 1 Czika
2 Flinstonowie 2 1 Pongo
2 Flinstonowie 3 2 Wilma
2 Flinstonowie 4 2 Fred
2 Flinstonowie 5 4 Elka
2 Flinstonowie 6 4 Maniek
...
Wynik złączenia, zawiera tylko cztery wiersze z tych, które pokazałem wyżej:
id tytul id bajka_id imie
---------- -------------------- ---------- ---------- ----------
1 101 Dalmatyńczyków 1 1 Czika
1 101 Dalmatyńczyków 2 1 Pongo
2 Flinstonowie 3 2 Wilma
2 Flinstonowie 4 2 Fred
...
Zwróć uwagę na wartości kolumn id (tej pierwszej, z tabeli bajka) i bajka_id (z tabeli postac). Jak widzisz zapytanie używające złączenia typu INNER JOIN zwraca wyłącznie te wiersze dla których kolumny użyte do złączenia mają tę samą wartość.
Innym sposobem na uzyskanie tego samego wyniku jest zapytanie:
SELECT *
FROM bajka
,postac
WHERE bajka.id = postac.bajka_id;
Wybieranie kolumn
Rzadko pobranie wszystkich kolumn po złączeniu tabel jest potrzebne. Bardzo często zwracane wiersze zawierają tylko niezbędny podzbiór kolumn:
SELECT bajka.tytul
,postac.imie
FROM bajka INNER JOIN postac
ON bajka.id = postac.bajka_id;
tytul imie
-------------------- ----------
101 Dalmatyńczyków Czika
101 Dalmatyńczyków Pongo
Flinstonowie Wilma
Flinstonowie Fred
Epoka lodowcowa Elka
Epoka lodowcowa Maniek
Muminki Migotka
Muminki Muminek
A co z brakującymi postaciami i bajkami? Zauważ, że wynik nie zawiera bajek takich jak “Jetsonowie”, “Rozbójnik Rumcajs” czy “Smerfy”. Brakuje także postaci “Maja”, “Gucio”, “Fiona” i “Shrek”. Z pomocą przychodzą złączenia typu OUTER JOIN.
OUTER JOIN
Istnieją trzy rodzaje złączeń typu OUTER:
LEFT OUTER JOIN,RIGHT OUTER JOIN,FULL OUTER JOIN.
LEFT OUTER JOIN
Tym razem zacznę od przykładu. Zapytanie używające złączenia tego typu może wyglądać tak:
SELECT *
FROM bajka LEFT OUTER JOIN postac
ON bajka.id = postac.bajka_id;
W wyniku działania tego zapytania zwrócone zostaną następujące wiersze:
id tytul id bajka_id imie
---------- -------------------- ---------- ---------- ----------
1 101 Dalmatyńczyków 1 1 Czika
1 101 Dalmatyńczyków 2 1 Pongo
2 Flinstonowie 3 2 Wilma
2 Flinstonowie 4 2 Fred
3 Jetsonowie
4 Epoka lodowcowa 5 4 Elka
4 Epoka lodowcowa 6 4 Maniek
5 Rozbójnik Rumcajs
6 Muminki 7 6 Migotka
6 Muminki 8 6 Muminek
7 Smerfy
W tym przypadku zapytanie zwróciło wszystkie bajki. Zarówno te, dla których istnieją odpowiadające im postaci jak i te, które nie mają odpowiadających wierszy w tabeli postac. Widzisz różnicę? W złączeniu typu LEFT OUTER JOIN znajdują się dodatkowe wiersze. Te wiersze zawierają wartości NULL w kolumnach tabeli postac. Pokazuje to tyle, że dla bajek “Jetsonowie”, “Rozbójnik Rumcajs” i “Smerfy” nie udało się znaleźć odpowiadających wierszy w tabeli postac.
Innymi słowy złączenie typu LEFT OUTER JOIN zwraca:
- wiersze dla których warunek złączenia jest spełniony,
- wiersze z “lewej tabeli” dla których nie ma odpowiedników w prawej (
*bajka* LEFT OUTER JOIN postac).
Wybieranie kolumn
Także tutaj ograniczenie liczby kolumn jest przydatne:
SELECT bajka.tytul
,postac.imie
FROM bajka
LEFT OUTER JOIN postac
ON bajka.id = postac.bajka_id;
To zapytanie zwróci następujące wiersze:
tytul imie
-------------------- ----------
101 Dalmatyńczyków Czika
101 Dalmatyńczyków Pongo
Flinstonowie Fred
Flinstonowie Wilma
Jetsonowie
Epoka lodowcowa Elka
Epoka lodowcowa Maniek
Rozbójnik Rumcajs
Muminki Migotka
Muminki Muminek
Smerfy
RIGHT OUTER JOIN
Niestety SQLite nie wspiera RIGHT OUTER JOIN. Wyniki, które tu pokazuję możesz uzyskać w innych silnikach baz danych lub obchodząc ograniczenia SQLite.
RIGHT OUTER JOIN jest złączeniem podobnym do LEFT OUTER JOIN. Złączenie typu RIGHT OUTER JOIN zwraca:
- wiersze dla których warunek złączenia jest spełniony,
- wiersze z “prawej tabeli” dla których nie ma odpowiedników w lewej (
bajka RIGHT OUTER JOIN *postac*).
SELECT *
FROM bajka RIGHT OUTER JOIN postac
ON bajka.id = postac.bajka_id;
id tytul id bajka_id imie
---------- -------------------- ---------- ---------- ----------
1 101 Dalmatyńczyków 1 1 Czika
1 101 Dalmatyńczyków 2 1 Pongo
2 Flinstonowie 3 2 Wilma
2 Flinstonowie 4 2 Fred
4 Epoka lodowcowa 5 4 Elka
4 Epoka lodowcowa 6 4 Maniek
6 Muminki 7 6 Migotka
6 Muminki 8 6 Muminek
9 Maja
10 Gucio
11 Fiona
12 Shrek
W tym przypadku zapytanie zwróciło wszystkie postaci. Zarówno te, dla których istnieją odpowiadające im bajki jak i te, które nie mają odpowiadających wierszy w tabeli bajka. W tym przypadku dla postaci “Maja”, “Gucio”, “Fiona” i “Shrek” nie udało znaleźć się odpowiadających bajek.
Jak uzyskać RIGHT OUTER JOIN w SQLite
Tutaj sprawa jest banalnie prosta bajka RIGHT OUTER JOIN postac zwraca te same wyniki co postac LEFT OUTER JOIN bajka :). Zatem zwykłe odwrócenie tabel wystarczy:
SELECT bajka.*
,postac.*
FROM postac LEFT OUTER JOIN bajka
ON bajka.id = postac.bajka_id;
FULL OUTER JOIN
Niestety SQLite nie wspiera FULL OUTER JOIN. Wyniki, które tu pokazuję możesz uzyskać w innych silnikach baz danych lub obchodząc ograniczenia SQLite.
FULL OUTER JOIN jest złączeniem które zwraca:
- wiersze dla których warunek złączenia jest spełniony,
- wiersze z “lewej tabeli” dla których nie ma odpowiedników w prawej (
*bajka* LEFT OUTER JOIN postac), - wiersze z “prawej tabeli” dla których nie ma odpowiedników w lewej (
bajka RIGHT OUTER JOIN *postac*).
SELECT *
FROM bajka FULL OUTER JOIN postac
ON bajka.id = postac.bajka_id;
id tytul id bajka_id imie
---------- -------------------- ---------- ---------- ----------
1 101 Dalmatyńczyków 1 1 Czika
1 101 Dalmatyńczyków 2 1 Pongo
2 Flinstonowie 3 2 Wilma
2 Flinstonowie 4 2 Fred
3 Jetsonowie
4 Epoka lodowcowa 5 4 Elka
4 Epoka lodowcowa 6 4 Maniek
5 Rozbójnik Rumcajs
6 Muminki 7 6 Migotka
6 Muminki 8 6 Muminek
7 Smerfy
9 Maja
10 Gucio
11 Fiona
12 Shrek
Jak uzyskać FULL OUTER JOIN w SQLite
FULL OUTER JOIN jest złączeniem, która zwraca wiersze z połączenia LEFT OUTER JOIN i RIGHT OUTER JOIN. Klauzula WHERE bajka.id IS NULL odpowiada za odfiltrowanie części wspólnej. Bez tego warunku wynik zawierałby powielone wiersze wspólne dla LEFT OUTER JOIN i RIGHT OUTER JOIN:
SELECT *
FROM bajka LEFT OUTER JOIN postac
ON bajka.id = postac.bajka_id
UNION ALL
SELECT bajka.*
,postac.*
FROM postac LEFT OUTER JOIN bajka
ON bajka.id = postac.bajka_id
WHERE bajka.id is NULL;
JOIN tu, JOIN tam
Pamiętam, że na początku mnogość pojęć robiła mi niezły mętlik w głowie. Do tego wszystkiego silniki bazy danych pozwalające na opuszczanie niektórych słów kluczowych nie pomagały. Lista niżej powinna Ci pomóc się w nich odnaleźć:
JOINto to samo coINNER JOIN,LEFT JOINto to samo coLEFT OUTER JOIN,RIGHT JOINto to samo coRIGHT OUTER JOIN,FULL JOINto to samo coFULL OUTER JOIN,CROSS JOINto to samo co iloczyn kartezjański.
Zadania do wykonania
Poniżej przygotowałem dla Ciebie kilka zadań do wykonania. Pomogą Ci one przećwiczyć sporą część materiału opisanego w kursie do tej pory. Wszystkie zapytania powinny być wywołane na bazie danych Chinook.
Jak zwykle zachęcam Cię do samodzielnego eksperymentowania zanim rzucisz okiem na przykładowe rozwiązania.
Napisz zapytanie, które zwróci:
- liczbę wierszy w iloczynie kartezjańskim tabel
track,invoiceiinvoiceline(UWAGA! to zapytanie może trochę potrwać), - tytuł albumu (kolumna
titlew tabelialbum) i nazwę artysty (kolumnanamew tabeliartist) dla wszystkich nazw artystów zaczynających się ods, - identyfikator i nazwę list utworów (tabela
playlist), które są puste, - nazwy trzech najczęściej występujących gatunków muzycznych (kolumna
namew tabeligenre) wraz z odpowiadającą im liczbą utworów (tabelatrack) posortowaną malejąco po liczbie utworów, - tytuły pięciu najdłuższych albumów (kolumna
titlew tabelialbum) posortowanych malejąco po ich długości (kolumnamillisecondsw tabelitrack), - tytuły albumów, na których występują utwory z gatunku “Reggae”,
- pięć nazw list utworów, które są najdroższe (suma cen wszystkich ścieżek jest największa),
Przykładowe rozwiązania zadań
SELECT COUNT(*)
FROM track
,invoice
,invoiceline;
SELECT album.title
,artist.name
FROM album JOIN artist
ON album.artistid = artist.artistid
WHERE artist.name LIKE 's%';
SELECT playlist.*
FROM playlist LEFT JOIN playlisttrack
ON playlisttrack.playlistid = playlist.playlistid
WHERE playlisttrack.trackid IS NULL;
SELECT genre.name
,count(*) AS how_many
FROM genre LEFT JOIN track
ON genre.genreid = track.genreid
GROUP BY genre.name
ORDER BY how_many DESC
LIMIT 3;
SELECT album.title
FROM track JOIN album
ON track.albumid = album.albumid
GROUP BY album.albumid
ORDER BY SUM(track.milliseconds) DESC
LIMIT 5;
SELECT DISTINCT album.title
FROM track JOIN genre
ON track.genreid = genre.genreid
JOIN album
ON track.albumid = album.albumid
WHERE genre.name = "Reggae";
SELECT playlist.name
FROM playlist JOIN playlisttrack
ON playlist.playlistid = playlisttrack.playlistid
JOIN track
ON playlisttrack.trackid = track.trackid
GROUP BY playlist.name
,playlist.playlistid
ORDER BY SUM(track.unitprice) DESC
LIMIT 5;
Podsumowanie
Po lekturze tego artykułu wiesz już czym są złączenia. Znasz wszystkie typy złączeń występujące w zapytaniach SQL. Po przerobieniu przykładowych zadań wiesz jak połączyć w całość wiedzę zdobytą do tej pory na kursie. Gratuluję!
Muszę, Ci powiedzieć, że moim zdaniem ta część materiału jest zdecydowanie najtrudniejsza dla początkujących, na pewno dla mnie taka była. Daj sobie trochę czasu na przetrawienie wiedzy z tego artykułu :). Jeśli nie wszystko jest dla Ciebie zrozumiałe daj znać w komentarzach, postaram się pomóc.
Jeśli uważasz, że artykuł może przydać się komuś z Twoich znajomych, proszę przekaż mu linka. W ten sposób pomożesz mi dotrzeć do nowych Czytelników, z góry dziękuję!
Jeśli nie chcesz ominąć kolejnych artykułów polub Samouczka na Facebooku i dopisz się do samouczkowego newslettera.
Trzymaj się!
-
W kontekście modelu relacyjnego powinienem raczej użyć pojęcia relacja. Mam wrażenie, że tabela jest jednak łatwiejsze do zrozumienia. ↩
-
Złączeń można dokonywać na tabelach, które nie są połączone między sobą kluczami obcymi. W praktyce nie zdarza się to za często. Upraszczając można powiedzieć, że klucze główne i klucze obce pomagają silnikom baz danych wykonywać złączenia, które są bardziej wydajne. ↩
-
Bazy danych mogą tu sporo optymalizować, dzięki czemu czasochłonne tworzenie iloczynu kartezjańskiego nie zawsze jest wykonywane. Jednak z punktu widzenia algebry relacji, podstawy modelu relacyjnego to właśnie iloczyn kartezjański jest punktem wyjścia. ↩
Pobierz opracowania zadań z rozmów kwalifikacyjnych
Przygotowałem rozwiązania kilku zadań algorytmicznych z rozmów kwalifikacyjnych. Rozkładam je na czynniki pierwsze i pokazuję różne sposoby ich rozwiązania. Dołącz do grupy ponad 6147 Samouków, którzy jako pierwsi dowiadują się o nowych treściach na blogu, a prześlę je na Twój e-mail.
Zostaw komentarz