PHP Majster: [SQL] Aliasy i złączenia wielu tabel za pomocą INNER JOIN, UNION, LEFT JOIN i RIGHT JOIN

W SQL-u zazwyczaj pracuje się na wielu tabelach połączonych ze sobą relacjami. Bardzo często podczas pracy z bazą przychodzi nam więc odwoływać się w jednym zapytaniu do więcej niż jednej tabeli. Prawidłowe używanie mechanizmów operujących na kilku tabelach w jednym zapytaniu wymaga poznania kilku kluczowych rozwiązań jakie język SQL tutaj oferuje. W tym artykule opiszę dokładnie na czym polega tworzenie aliasów dla tabel i kolumn, które to aliasy następnie wykorzystamy podczas łączenia tabel w zapytaniach. Nauczymy się następnie jak działają i czym w ogóle są złączenia oraz przeanalizujemy na przykładach różne rodzaje złączeń, takie jak UNION, INNER JOIN oraz złączenia zewnętrzne, czyli LEFT JOIN i RIGHT JOIN. Do zobrazowania przykładów tutaj opisywanych wykorzystamy prostą bazę z relacjami, którą stworzyłem specjalnie na potrzeby tego i kolejnych artykułów.

Testowa baza danych

Jak wspomniałem wyżej, przygotowałem prostą bazę dla MySQL, która zostanie tutaj wykorzystana w przykładach. Baza składa się z 5 tabel i polecam je sobie zaimportować, tak aby wszystko można było testować na swoim serwerze. Kod SQL tworzący tabele i dodający przykładowe rekordy znajduje się kilka akapitów niżej. Struktura naszej bazy wygląda tak:

A poniżej lista rekordów, jakimi nasza baza będzie w celach edukacyjnych wypełniona:

Tabela: movie

movie_id	title	year	imdb_rating	category_id	oscars	director_id
1	Alien	1979	8.5	4 /horror/	1	9 /Scott/
2	Avatar	2009	7.9	5 /science-fiction/	3	2 /Cameron/
3	Batman	1989	7.6	1 /akcja/	1	1 /Burton/
4	Casino	1995	8.2	2 /dramat/	0	8 /Scorseze/
5	Desperado	1995	7.2	1 /akcja/	0	7 /Rodriguez/
6	Godfather	1972	9.2	2 /dramat/	3	4 /Coppola/
7	Hobbit	2012	8.0	3 /fantasy/	0	5 /Jackson/
8	Interstellar	2014	8.7	5 /science-fiction/	1	6 /Nolan/
9	Kill Bill	2003	8.1	1 /akcja/	0	10 /Tarantino/
10	Terminator	1984	8.1	5 /science-fiction/	0	2 /Cameron/
11	The Thing	1982	8.2	4 /horror/	0	3 /Carpenter/
12	Titanic	1997	7.7	2 /dramat/	11	2 /Cameron/
13	Titanic 2	2030	0	5 /science-fiction/	0	0

Tabela: actor_in_movie

id	actor_id	movie_id
1	1 /Schwarzenegger/	10 /Terminator/
2	2 /Biehn/	10 /Terminator/
3	3 /Weaver/	1 /Alien/
4	3 /Weaver/	2 /Avatar/
5	4 /DiCaprio/	12 /Titanic/
6	5 /Winslet/	12 /Titanic/
7	6 /Holm/	1 /Alien/
8	6 /Holm/	7 /Hobbit/
9	7 /Thurman/	9 /Kill Bill/
10	8 /Keaton/	3 /Batman/
11	9 /Nicholson/	3 /Batman/
12	10 /McKellen/	7 /Hobbit/
13	11 /Banderas/	5 /Desperado/
14	12 /Hayek/	5 /Desperado/
15	13 /McConaughey/	8 /Interstellar/
16	14 /Russell/	11 /The Thing/
17	15 /Pacino/	6 /Godfather/
18	16 /DeNiro/	4 /Casino/
19	17 /Pesci/	4 /Casino/

Tabela: actor

actor_id	first_name	last_name	date_birth
1	Arnold	Schwarzenegger	1947
2	Michael	Biehn	1956
3	Sigourney	Weaver	1949
4	Leonardo	DiCaprio	1974
5	Kate	Winslet	1975
6	Ian	Holm	1931
7	Uma	Thurman	1970
8	Michael	Keaton	1951
9	Jack	Nicholson	1937
10	Ian	McKellen	1939
11	Antonio	Banderas	1960
12	Salma	Hayek	1966
13	Matthew	McConaughey	1969
14	Kurt	Russell	1951
15	Al	Pacino	1940
16	Robert	DeNiro	1943
17	Joe	Pesci	1943

Tabela: director

director_id	first_name	last_name	date_birth
1	Tim	Burton	1958
2	James	Cameron	1954
3	John	Carpenter	1948
4	Francis	Coppola	1939
5	Peter	Jackson	1961
6	Christopher	Nolan	1970
7	Robert	Rodriguez	1968
8	Martin	Scorseze	1941
9	Ridley	Scott	1937
10	Quentin	Tarantino	1963

Tabela: category

category_id	title
1	akcja
2	dramat
3	fantasy
4	horror
5	science-fiction

Kod SQL, który możecie zaimportować sobie do własnej bazy:
[code]CREATE TABLE IF NOT EXISTS `actor` (
`actor_id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) NOT NULL,
`last_name` varchar(255) NOT NULL,
`imdb_rating` float NOT NULL,
PRIMARY KEY (`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;

INSERT INTO `actor` (`actor_id`, `first_name`, `last_name`, `imdb_rating`) VALUES
(1, 'Arnold', 'Schwarzenegger', 1947),
(2, 'Michael', 'Biehn', 1956),
(3, 'Sigourney', 'Weaver', 1949),
(4, 'Leonrdo', 'DiCaprio', 1974),
(5, 'Kate', 'Winslet', 1975),
(6, 'Ian', 'Holm', 1931),
(7, 'Uma', 'Thurman', 1970),
(8, 'Michael', 'Keaton', 1951),
(9, 'Jack', 'Nicholson', 1937),
(10, 'Ian', 'McKellen', 1939),
(11, 'Antonio', 'Banderas', 1960),
(12, 'Salma', 'Hayek', 1966),
(13, 'Matthew', 'McConaughey', 1969),
(14, 'Kurt', 'Russell', 1951),
(15, 'Al', 'Pacino', 1940),
(16, 'Robert', 'DeNiro', 1943),
(17, 'Joe', 'Pesci', 1943);

CREATE TABLE IF NOT EXISTS `actor_in_movie` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`actor_id` int(11) NOT NULL,
`movie_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;

INSERT INTO `actor_in_movie` (`id`, `actor_id`, `movie_id`) VALUES
(1, 1, 10),
(2, 2, 10),
(3, 3, 1),
(4, 3, 2),
(5, 4, 12),
(6, 5, 12),
(7, 6, 1),
(8, 6, 7),
(9, 7, 9),
(10, 8, 3),
(11, 9, 3),
(12, 10, 7),
(13, 11, 5),
(14, 12, 5),
(15, 13, 8),
(16, 14, 11),
(17, 15, 6),
(18, 16, 4),
(19, 17, 4);

CREATE TABLE IF NOT EXISTS `category` (
`category_id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `category` (`category_id`, `title`) VALUES
(1, 'akcja'),
(2, 'dramat'),
(3, 'fantasy'),
(4, 'horror'),
(5, 'science-fiction');

CREATE TABLE IF NOT EXISTS `director` (
`director_id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) NOT NULL,
`last_name` varchar(255) NOT NULL,
`date_birth` int(11) NOT NULL,
PRIMARY KEY (`director_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

INSERT INTO `director` (`director_id`, `first_name`, `last_name`, `date_birth`) VALUES
(1, 'Tim', 'Burton', 1958),
(2, 'James', 'Cameron', 1954),
(3, 'John', 'Carpenter', 1948),
(4, 'Francis', 'Coppola', 1939),
(5, 'Peter', 'Jackson', 1961),
(6, 'Christopher', 'Nolan', 1970),
(7, 'Robert', 'Rodriguez', 1968),
(8, 'Martin', 'Scorseze', 1941),
(9, 'Ridley', 'Scott', 1937),
(10, 'Quentin', 'Tarantino', 1963);

CREATE TABLE IF NOT EXISTS `movie` (
`movie_id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`year` int(11) NOT NULL,
`imdb_rating` float NOT NULL,
`category_id` int(11) NOT NULL,
`oscars` int(11) NOT NULL,
`director_id` int(11) NOT NULL,
PRIMARY KEY (`movie_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

INSERT INTO `movie` (`movie_id`, `title`, `year`, `imdb_rating`, `category_id`, `oscars`, `director_id`) VALUES
(1, 'Alien', 1979, 8.5, 4, 1, 9),
(2, 'Avatar', 2009, 7.9, 5, 3, 2),
(3, 'Batman', 1989, 7.6, 1, 1, 1),
(4, 'Casino', 1995, 8.2, 2, 0, 8),
(5, 'Desperado', 1995, 7.2, 1, 0, 7),
(6, 'Godfather', 1972, 9.2, 2, 3, 4),
(7, 'Hobbit', 2012, 8, 3, 0, 5),
(8, 'Interstellar', 2014, 8.7, 5, 1, 6),
(9, 'Kill Bill', 2003, 8.1, 1, 0, 10),
(10, 'Terminator', 1984, 8.1, 5, 0, 2),
(11, 'The Thing', 1982, 8.2, 4, 0, 3),
(12, 'Titanic', 1997, 7.7, 2, 11, 2),
(13, 'Titanic 2', 2030, 0, 5, 0, 0);[/code]

Zaczynamy naukę

Zaimportujmy sobie powyżśze tabele i rekordy do bazy (np. movies), następnie w konsoli SQL wyświetlmy sobie zapytaniem SELECT wszystkie wiersze z tabeli movie:
[code]SELECT * FROM movie;[/code]
[code]
mysql> use movies
Database changed
mysql> SELECT * FROM movie;
+----------+--------------+------+-------------+-------------+--------+-------------+
| movie_id | title | year | imdb_rating | category_id | oscars | director_id |
+----------+--------------+------+-------------+-------------+--------+-------------+
| 1 | Alien | 1979 | 8.5 | 4 | 1 | 9 |
| 2 | Avatar | 2009 | 7.9 | 5 | 3 | 2 |
| 3 | Batman | 1989 | 7.6 | 1 | 1 | 1 |
| 4 | Casino | 1995 | 8.2 | 2 | 0 | 8 |
| 5 | Desperado | 1995 | 7.2 | 1 | 0 | 7 |
| 6 | Godfather | 1972 | 9.2 | 2 | 3 | 4 |
| 7 | Hobbit | 2012 | 8 | 3 | 0 | 5 |
| 8 | Interstellar | 2014 | 8.7 | 5 | 1 | 6 |
| 9 | Kill Bill | 2003 | 8.1 | 1 | 0 | 10 |
| 10 | Terminator | 1984 | 8.1 | 5 | 0 | 2 |
| 11 | The Thing | 1982 | 8.2 | 4 | 0 | 3 |
| 12 | Titanic | 1997 | 7.7 | 2 | 11 | 2 |
| 13 | Titanic 2 | 2030 | 0 | 5 | 0 | 0 |
+----------+--------------+------+-------------+-------------+--------+-------------+
13 rows in set (0.00 sec)
[/code]

Aliasy

Każdej tabeli oraz każdej kolumnie możemy podczas pobierania danych z bazy przypisać alias, a więc nazwę zastępczą pod jaką dana tabela lub kolumna będzie widoczna. Używanie aliasów przydaje się podczas pobierania dużej ilości danych z wielu kolumn, gdy nazwy tych kolumn są dość długie lub się powtarzają. Skrócona nazwa w postaci aliasu pozwala nam wtedy na bardziej przejrzyste i krótsze długościowo zapytanie. To jedno, drugą sprawą jest to, iż podczas operacji na bardziej złożonych zapytaniach używanie aliasów jest wręcz absolutnie niezbędne.

Aliasy dla tabel - AS

Alias dla nazwy tabeli tworzymy poprzez podanie jego nazwy po rzeczywistej nazwie tabeli zaraz po klauzurze FROM. Opcjonalnie możemy poprzedzić go słowem AS, choć nie jest to wymagane. W dobrym tonie jednak jest podanie słowa AS, gdyż zapis taki staje się czytelniejszy.
Oba poniższe przykłady są poprawne:
[code]SELECT * FROM tabela alias_tabeli[/code]
[code]SELECT * FROM tabela AS alias_tabeli[/code]
Zdecydowanie jednak polecam sposób drugi i z takiego właśnie będziemy korzystać w tym artykule.
Wróćmy teraz do naszej tabeli. W przypadku listy filmów pobraliśmy wszystkie rekordy (*) z tabeli movie znajdującej się w bazie danych movies. Zapytanie stworzyliśmy najprostrze z możliwych:
[code]SELECT * FROM movie;[/code]
W przypadku tym nie było żadnej potrzeby dla utworzenia aliasu dla tabeli. Gdybyśmy jednak chcieli to zrobić, to utworzenie aliasu dla tabeli movie wyglądałoby tak:
[code]SELECT * FROM movie AS m[/code]
gdzie m stałoby się tutaj aliasem dla tabeli movie. Od chwii utworzenia takiego aliasu musielibyśmy posługiwać się nim, zamiast pełną nazwą tabeli. Przykładowo - jeśli chcelibyśmy wyświetlić teraz rekordy posortowane według roku produkcji to zamiast konstrukcji:
[code]SELECT movie.movie_id, movie.title, movie.year FROM movie ORDER BY movie.year;[/code]
która wyświetli nam:
[code]
mysql> SELECT movie.movie_id, movie.title, movie.year FROM movie ORDER BY movie.year;
+----------+--------------+------+
| movie_id | title | year |
+----------+--------------+------+
| 6 | Godfather | 1972 |
| 1 | Alien | 1979 |
| 11 | The Thing | 1982 |
| 10 | Terminator | 1984 |
| 3 | Batman | 1989 |
| 4 | Casino | 1995 |
| 5 | Desperado | 1995 |
| 12 | Titanic | 1997 |
| 9 | Kill Bill | 2003 |
| 2 | Avatar | 2009 |
| 7 | Hobbit | 2012 |
| 8 | Interstellar | 2014 |
| 13 | Titanic 2 | 2030 |
+----------+--------------+------+
13 rows in set (0.00 sec)
[/code]
użylibyśmy:
[code]SELECT m.movie_id, m.title, m.year FROM movie AS m ORDER BY m.year;[/code]
która zadziała identycznie, tyle że z użyciem aliasu.

Oczywiście w przypadku pracy na jednej tabeli nie istnieje potrzeba podawania nazwy tabeli przed nazwą kolumny, ale gdy tabel w jednym zapytaniu nagle zrobi się więcej sprawa zaczyna wyglądać inaczej.
I teraz bardzo ważna sprawa - w przypadku, gdy podamy alias dla tabeli, przestaje obowiązywać jej rzeczywista nazwa i wszystkie odwołania do tabeli muszą być stworzone za pomocą aliasu. Zobaczmy na przykład:

[code]mysql> SELECT movie.movie_id, movie.title, movie.year FROM movie AS m ORDER BY movie.year;
ERROR 1054 (42S22): Unknown column 'movie.movie_id' in 'field list'[/code]

Jak widać zapytanie takie zwróci błąd, gdyż tabela movie nie występuje już pod swoją oryginalną nazwą, w związku z czym odwołanie się do niej za pomocą jej oryginalnej nazwy zawsze zakończy się niepowodzeniem.

Aliasy dla kolumn - AS

Analogicznie jak w przypadku całej tabeli podajemy alias dla kolumn i analogicznie jak w tamtym przypadku kolumna staje się dostępna jedynie pod nazwą określoną jako alias. Tworząc alias dla kolumny ponownie używamy tutaj słowa kluczowego AS i podajemy nazwę aliasu, wg zasady:
[code]kolumna AS alias_kolumny[/code]
W przypadku aliasu dla kolumny od razu zobaczymy różnicę, gdyż w wyświetlanym zestawie zwróconych rekordów kolumna pojawi się pod nową nazwą. Rzućmy okiem na przykład, w którym zmienimy nazwy kolumn definiując im aliasy:
[code]
mysql> SELECT movie_id AS ID, title AS Tytul, year AS Rok_produkcji FROM movie ORDER BY year;
+----+--------------+---------------+
| ID | Tytul | Rok_produkcji |
+----+--------------+---------------+
| 6 | Godfather | 1972 |
| 1 | Alien | 1979 |
| 11 | The Thing | 1982 |
| 10 | Terminator | 1984 |
| 3 | Batman | 1989 |
| 4 | Casino | 1995 |
| 5 | Desperado | 1995 |
| 12 | Titanic | 1997 |
| 9 | Kill Bill | 2003 |
| 2 | Avatar | 2009 |
| 7 | Hobbit | 2012 |
| 8 | Interstellar | 2014 |
| 13 | Titanic 2 | 2030 |
+----+--------------+---------------+
13 rows in set (0.00 sec)
[/code]
Jak widzimy, zwrócone kolumny otrzymały teraz inne nazwy.
Zauważmy też, że podaliśmy jako wartość wg której sortujemy oryginalną nazwę kolumny i pomimo, iż przypisaliśmy tej kolumnie alias, to zapytanie takie nie zwróciło błędu:
[code]ORDER BY year[/code]
Podanie tutaj aliasu zamiast oryginalnej nazwy:
[code]ORDER BY Rok_produkcji[/code]
wyświetli ten sam zestaw wyników, obie wersje zapytania więc są tutaj równoważne.
Warto jednak tutaj pamiętać, iż zwrócone w zapytaniu kolumny (np. do skryptu PHP) będą obowiązywać jedynie pod nazwą aliasu, np. do pola z tytułem dostaniemy się w PHP jedynie za pomocą $row['Tytul'], natomiast $row['title'] nie będzie zawierać w sobie żadnych danych.

Własne kolumny

Aliasy dla kolumn pozwalają na bardzo ciekawe zachowanie. Otóż jako, iż alias jest tak jakby "wirtualną" reprezentacją zestawu rekordów z kolumny, to pozwala nam to w zwróconym zestawie rekordów na tworzenie pól, które w ogóle nie istnieją w bazie danych. Aby to zrozumieć, przeanalizujmy sobie poniższy przykład. Pobierzemy sobie z tabeli movie pola id, title oraz year, ale o ile pole id przedstawimy normalnie, tak zamiast pól title i year stworzymy nieistniejące pole o nazwie opis_filmu zawierające informacje pobrane z pól title i year wzbogacone o ciąg tekstowy. Finalnie, zamiast dwóch pól - title i year, chcemy otrzymać jedno pole o następującej składni:
[code]Film XYZ zostal wyprodukowany w roku XYZ. [/code]
Jak tego dokonamy? Wykorzystamy aliasy i funkcję CONCAT(), która służy do łączenia pobieranych danych w jeden ciąg tekstowy.

[code]SELECT id AS ID, CONCAT('Film ', title, ' zostal wyprodukowany w roku ', year) AS opis_filmu FROM movie ORDER by year;[/code]

Wynik:
[code]
mysql> SELECT movie_id AS ID, CONCAT('Film ', title, ' zostal wyprodukowany w roku ', year) AS opis_filmu FROM movie ORDER by year;
+----+----------------------------------------------------+
| ID | opis_filmu |
+----+----------------------------------------------------+
| 6 | Film Godfather zostal wyprodukowany w roku 1972 |
| 1 | Film Alien zostal wyprodukowany w roku 1979 |
| 11 | Film The Thing zostal wyprodukowany w roku 1982 |
| 10 | Film Terminator zostal wyprodukowany w roku 1984 |
| 3 | Film Batman zostal wyprodukowany w roku 1989 |
| 4 | Film Casino zostal wyprodukowany w roku 1995 |
| 5 | Film Desperado zostal wyprodukowany w roku 1995 |
| 12 | Film Titanic zostal wyprodukowany w roku 1997 |
| 9 | Film Kill Bill zostal wyprodukowany w roku 2003 |
| 2 | Film Avatar zostal wyprodukowany w roku 2009 |
| 7 | Film Hobbit zostal wyprodukowany w roku 2012 |
| 8 | Film Interstellar zostal wyprodukowany w roku 2014 |
| 13 | Film Titanic 2 zostal wyprodukowany w roku 2030 |
+----+----------------------------------------------------+
13 rows in set (0.00 sec)
[/code]
Sposobem takim możemy łączyć pola w bazie danych (również z dowolnym stringiem jak widać na przykładzie), a następnie zwracać tak złączone dane jako "wirtualną" kolumnę, która fizycznie nie istnieje.
Prawda, że ciekawe rozwiązanie?

Złączenie wyników - UNION

Złączenie wewnętrzne - INNER JOIN

Nazywane również "złączeniem naturalnym".
Jest to najczęściej wykorzystywane w praktyce złączenie. Wynikiem złączenia za pomocą INNER JOIN jest zbiór wierszy z łączonych przez nas tabel.

źródło: w3c

Klauzula ON

Klauzula ON pozwala na określenie warunku dla złączenia. Jej użycie wygląda następująco:
[code]
SELECT *
FROM tabela1 INNER JOIN tabela2
ON tabela1.kolumna = tabela2.kolumna;
[/code]
lub:
[code]
SELECT *
FROM tabela1 JOIN tabela2
ON tabela1.kolumna = tabela2.kolumna;
[/code]
W naszym przypadku będzie wyglądać to więc tak:
[code]
SELECT title, last_name
FROM movie INNER JOIN director
ON movie.director_id = director.director_id;
[/code]
Jak widzimy - najpierw za pomocą zapytania SELECT określamy jakie kolumny z łączonych tabel chcemy pobrać, następnie podajemy nazwę pierwszej tabeli, po czym używamy klauzuli:
[code]INNER JOIN[/code]
po której podajemy nazwę drugiej tabeli, a następnie za pomocą klauzuli:
[code]ON[/code]
określamy warunek złączenia, w którym podajemy, które pola z obu tabel muszą nam się zgadzać.

W naszym przypadku kolumna director_id w tabeli movie posiada klucz obcy z relacją do kolumny director_id w tabeli director. Powyższe zapytanie sprawi więc, że najpierw pobierzemy rekordy z tabeli pierwszej, czyli movie, a następnie te rekordy z tabeli drugiej - director, których pole director_id będzie odpowiadać polu director_id w tabeli pierwszej.
UWAGA: Złączenie to pobierze jedynie te rekordy, których warunek będzie pasował (posiadające reżysera), co za tym idzie, jeśli mielibyśmy tutaj jakiś film, dla którego nie określilibyśmy reżysera - to rekord z takim filmem nam się nie wyświetli! Tak działa złączenie naturalne - pobiera jedynie zestaw danych, dla których pasuje warunek złączenia. Zobaczmy jak zadziała to w praktyce:
[code]
mysql> SELECT title, last_name FROM movie INNER JOIN director ON movie.director_id = director.director_id;
+--------------+-----------+
| title | last_name |
+--------------+-----------+
| Alien | Scott |
| Avatar | Cameron |
| Batman | Burton |
| Casino | Scorseze |
| Desperado | Rodriguez |
| Godfather | Coppola |
| Hobbit | Jackson |
| Interstellar | Nolan |
| Kill Bill | Tarantino |
| Terminator | Cameron |
| The Thing | Carpenter |
| Titanic | Cameron |
+--------------+-----------+
12 rows in set (0.00 sec)
[/code]
Jak widzimy, o to właśnie chodziło!
Zauważmy tutaj, iż na liście brakuje filmu "Titanic 2". Stało się tak dlatego, iż nie pasuje do niego żadne złączenie (nie posiada żadnego reżysera).

Zatrzymajmy się teraz na chwilę i zauważmy, że może się zdarzyć tak, że będziemy mieli dwie kolumny o tych samych nazwach z łączonych tabel, np. kolumna last_name występuje u nas zarówno w tabeli actor, jak i w tabeli director. Aby zapobieć problemom i uczynić zapytania trochę bardziej przejrzystymi warto wyrobić w sobie nawyk, aby nawet przy mniejszych zapytaniach podawać przed nazwą kolumny nazwę tabeli, z jakiej dana kolumna pochodzi. I tak np. nasze powyższe zapytanie powinno wyglądać poprawnie tak:
[code]
SELECT movie.title, director.last_name
FROM movie INNER JOIN director
ON movie.director_id = director.director_id;
[/code]
lub z wykorzystaniem aliasów, gdzie tabelę movie zamieniamy na alias m, a tabelę director na alias d:
[code]
SELECT m.title AS Film, d.last_name AS Rezyser
FROM movie AS m INNER JOIN director AS d
ON m.director_id = d.director_id;
[/code]
Wynik zapyytania po zdefiniowaniu aliasów:
[code]
mysql> SELECT m.title AS Film, d.last_name AS Rezyser FROM movie AS m INNER JOIN director AS d ON m.director_id = d.director_id;
+--------------+-----------+
| Film | Rezyser |
+--------------+-----------+
| Alien | Scott |
| Avatar | Cameron |
| Batman | Burton |
| Casino | Scorseze |
| Desperado | Rodriguez |
| Godfather | Coppola |
| Hobbit | Jackson |
| Interstellar | Nolan |
| Kill Bill | Tarantino |
| Terminator | Cameron |
| The Thing | Carpenter |
| Titanic | Cameron |
+--------------+-----------+
12 rows in set (0.00 sec)
[/code]
Jak widać, przy okazji określiliśmy również aliasy dla kolumn, aby wynik był bardziej przejrzysty.

Klauzula USING

Istnieje skrócona, bardziej zwięzła metoda na podanie warunku złączenia zastępująca klauzulę ON. Jest to klauzula:
[code]USING[/code]
Posiada jednak ona jeden warunek, który musimy spełnić, aby zadziałała. Otóż w klauzuli USING podajemy nazwę kolumny, której nazwa musi być taka sama w obu tabelach, np.
[code]USING(director_id)[/code]
będzie równoważne z:
[code]ON movie.director_id = director.director_id[/code]
Jak widać zapis za pomocą USING jest krótszy, ale wymaga on, aby kolumny w obu tabelach posiadały tą samą nazwę.
Popatrzmy jeszcze raz na oba zapytania:

Z wykorzystaniem klauzuli ON:
[code]
SELECT title, last_name
FROM movie INNER JOIN director
ON movie.director_id = director.director_id;
[/code]
oraz z wykorzystaniem klauzuli USING:
[code]
SELECT title, last_name
FROM movie INNER JOIN director
USING(director_id);
[/code]
Oba powyższe zapytania są równoważne.

Dodatkowe warunki - WHERE

Jak teraz do takiego złączenia dodać jakieś dodatkowe warunki, aby np. wyświetlić jedynie filmy, które powstały po 1990 roku? Robimy to tak samo jak przy zwykłym zapytaniu, ale pamiętać musimy o jednej sprawie: podany warunek obowiązywać będzie jedynie dla pierwszej tabeli. Zobaczmy to na przykładzie:
[code]
SELECT year, title, last_name
FROM movie INNER JOIN director
ON movie.director_id = director.director_id
WHERE year > 1990
ORDER BY year;
[/code]
Wynik:
[code]
mysql> SELECT year, title, last_name FROM movie INNER JOIN director ON movie.director_id = director.director_id WHERE year > 1990 ORDER BY
year;
+------+--------------+-----------+
| year | title | last_name |
+------+--------------+-----------+
| 1995 | Casino | Scorseze |
| 1995 | Desperado | Rodriguez |
| 1997 | Titanic | Cameron |
| 2003 | Kill Bill | Tarantino |
| 2009 | Avatar | Cameron |
| 2012 | Hobbit | Jackson |
| 2014 | Interstellar | Nolan |
+------+--------------+-----------+
7 rows in set (0.00 sec)
[/code]
Jak widzimy, wyświetliliśmy jedynie filmy, które premierę miały po 1990 roku. Warunek WHERE dotyczył tutaj jedynie pierwszej tabeli, mimo iż był podany na końcu zapytania, już po definicji tabeli drugiej.

Łączenie więcej niż dwóch tabel

Złączenia zewnętrzne - OUTER JOIN

O ile złączenie wewnętrzne nie zwróci nam w wyniku rekordów, dla których nie pasuje warunek złączenia (a zatem nie uwzględni tutaj filmów nie posiadających reżysera), o tyle złączenie zewnętrzne takie rekordy uwzględni. Zasada działania złączenia zewnętrznego jest następująca: pobieramy pełen pakiet rekordów z jednej tabeli, a następnie pasujące rekordy z tabeli kolejnej. Mamy więc tutaj pewność, że nawet jeśli któryś z naszych filmów nie będzie posiadał reżysera, to i tak rekord z takim filmem zostanie wyświetlony w wyniku. Jedynie pole z reżyserem pozostanie puste.
Złączenia zewnętrzne dzielą się na dwa rodzaje:
LEFT JOIN - złączenie lewostronne, w którym tabela stojąca w zapytaniu po lewej jest pobierana w całosci
RIGHT JOIN - złączenie prawostronne, w którym tabela stojąca w zapytaniu po prawej pobierana jest w całości

Złączenie lewostronne - LEFT JOIN

Przyjrzyjmy się na początek złączeniu lewostronnemu. Chcemy wyświetlić listę WSZYSTKICH filmów jakie posiadamy w tabeli movie, oraz przypisać do nich reżyserów z tabeli director. Jak pamiętamy, podczas złączenia wewnętrznego jeden film nie był uwzględniany w zbiorze, gdyż nie posiadał reżysera. Tym razem zostanie on uwzgledniony, gdyż tabela po lewej (w tym przypadku movie) zostanie pobrana w całości.

źródło: w3c

Złączenie prawostronne - RIGHT JOIN

Analogicznie, ale w drugą stronę działa złączenie prawostronne - RIGHT JOIN. Zasada działania jest taka sama jak przy LEFT JOIN, tyle że w drugą stronę. Pobrane zostają wszystkie wyniki znajdujące się w tabeli drugiej.

źródło: w3c

Podsumowanie

Wszystkie podstawowe aspekty złączeń zostały tutaj opisane, więc wystarczy dokładnie je przeanalizować, aby nauczyć się pracy z SQL na łączonych tabelach. Ze złączeniami polecam następnie poeksperymentować we własnym zakresie, tworząc np. bardziej rozbudowane konstrukcje. Możecie pobawić się np. na przygotowanej tutaj bazie, pisząc sobie zapytania zwracające wyniki oparte na różnych warunkach i wyświetlające je w określony sposób. Baza jest wypełniona danymi takimi jak daty urodzin aktorów i reżyserów, liczby zdobytych Oscarów, czy średnie oceny z IMDB - można eksperymentować.

wtorek, 9 czerwca 2015

[SQL] Aliasy i złączenia wielu tabel za pomocą INNER JOIN, UNION, LEFT JOIN i RIGHT JOIN

Testowa baza danych

Zaczynamy naukę

Aliasy

Aliasy dla tabel - AS

Aliasy dla kolumn - AS

Własne kolumny

Złączenie wyników - UNION

Złączenie wewnętrzne - INNER JOIN

Klauzula ON

Klauzula USING

Dodatkowe warunki - WHERE

Łączenie więcej niż dwóch tabel

Złączenia zewnętrzne - OUTER JOIN

Złączenie lewostronne - LEFT JOIN

Złączenie prawostronne - RIGHT JOIN

Podsumowanie

5 komentarzy: