Złączenia w MySQL (joins)

Dane są dwie proste tabele:

t1: t2:

id imie id wiek

---- ---------- ---- ----------

1 Jan 1 20

2 Monika 2 35

3 14

4 Stefan

5 Paweł

6 78

7 Andrzej 7 24

8 46

Tabele te są powiązane ze sobą poprzez pole id i przechowują dane o wieku użytkowników o danym imieniu.

Część wspólna (INNER JOIN lub w skrócie JOIN)

Obsługiwane domyślnie przez MySQL: TAK

Jest to najprostsze złączenie. Pozwala pobrać wszystkie rekordy z tabeli A mające odpowiedniki w tabeli B. Innymi słowy jest to część wspólna tych tabel.

Forma zapisu:

SELECT * FROM t1 JOIN t2 ON t1.id = t2.id

Wynik:

id imie id wiek

---- -------- ---- -----

1 Jan 1 20

2 Monika 2 35

7 Andrzej 7 24

Część wspólna (NATURAL JOIN)

Obsługiwane domyślnie przez MySQL: TAK

Jest to najprostsze złączenie. Pozwala pobrać wszystkie rekordy z tabeli A mające odpowiedniki w tabeli B. Innymi słowy jest to część wspólna tych tabel.

Forma zapisu:

SELECT * FROM t1 NATURAL JOIN t2

Wynik:

id imie wiek

---- -------- -----

1 Jan 20

2 Monika 35

7 Andrzej 24

Inny przykład – sprawdź na nim działanie NATURAL JOIN

create table tabelaA (id int, opis1 text, opis2 text);

create table tabelaB (id int, opis3 text, opis4 text);

insert into tabelaA values (100, 'opis11', 'opis12'), (101, 'opis21', 'opis22'), (102, 'opis31', 'opis32');

insert into tabelaB values (101, 'opis41', 'opis42'), (103, 'opis51', 'opis52'), (105, 'opis61', 'opis62');

Lewe złączenie (LEFT OUTER JOIN lub w skrócie LEFT JOIN)

Obsługiwane domyślnie przez MySQL: TAK

Jest to najczęstsze z używanych złączeń. Złączenie pozwala pobrać wszystkie rekordy z tabeli A wraz z odpowiadającymi im rekordami z tabeli B nawet jak w tabeli B nie ma odpowiedników dla rekordów z A.

Forma zapisu:

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id

Wynik:

id imie id wiek

---- -------- ---- -----

1 Jan 1 20

2 Monika 2 35

4 Stefan NULL NULL

5 Paweł NULL NULL

7 Andrzej 7 24

Lewe wykluczające złączenie (LEFT OUTER JOIN lub w skrócie LEFT JOIN z warunkiem)

Obsługiwane domyślnie przez MySQL: TAK

Jest to szczególny przypadek LEFT JOIN. Złączenie pozwala pobrać wszystkie rekordy z tabeli A, które nie mają odpowiedników w tabeli B.

Forma zapisu:

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL

Wynik:

id imie id wiek

---- -------- ---- -----

4 Stefan NULL NULL

5 Paweł NULL NULL

Prawe złączenie (RIGHT OUTER JOIN lub w skrócie RIGHT JOIN)

Obsługiwane domyślnie przez MySQL: TAK

Jest to złączenie odwrotne do LEFT JOIN. Złączenie działa analogicznie jak LEFT JOIN z tym, że dla odwróconej kolejności tabel.

Forma zapisu:

SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id

Wynik:

id imie id wiek

---- -------- ---- -----

1 Jan 1 20

2 Monika 2 35

NULL NULL 3 14

NULL NULL 6 78

7 Andrzej 7 24

NULL NULL 8 46

Prawe wykluczające złączenie (RIGHT OUTER JOIN lub w skrócie RIGHT JOIN z warunkiem)

Obsługiwane domyślnie przez MySQL: TAK

Jest to złączenie odwrotne do LEFT JOIN z wykluczeniem. Złączenie działa analogicznie jak LEFT JOIN z tym, że dla odwróconej kolejności tabel.

Forma zapisu:

SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id WHERE t1.id IS NULL

Wynik:

id imie id wiek

---- -------- ---- -----

NULL NULL 3 14

NULL NULL 6 78

NULL NULL 8 46

Sumaryczne pełne złączenie (FULL OUTER JOIN lub w skrócie FULL JOIN)

Obsługiwane domyślnie przez MySQL: NIE

Jest to złączenie zwracające wszystkie rekordy z tabeli A i tabeli B odpowiednio ze sobą połączone. Jest to swoiste połączenie LEFT i RIGHT JOIN czyli zawiera ono wszystkie rekordy z A oraz wszystkie rekordy z B.

Forma zapisu w SQL:

SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id

Ponieważ MySQL nie wspiera tego typu złączenia należy je symulować na jeden z dwóch sposobów:

1. Wykorzystanie UNION z LEFT JOIN + RIGHT JOIN

Forma zapisu w MySQL:

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id UNION SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id

To zapytanie złącza wyniki dwóch zapytań eliminując powtórzenia. Jednak eliminacja powtórzeń wymaga sortowania, więc sposób ten nie jest zalecany przy dużych zbiorach rekordów.

2. Wykorzystanie UNION ALL z LEFT JOIN + RIGHT JOIN z wykluczeniem

Forma zapisu w MySQL:

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id UNION ALL SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id WHERE t1.id IS NULL

To zapytanie złącza wyniki dwóch zapytań nie eliminując powtórzeń lecz dodatkowy warunek zapewnia, że RIGHT JOIN pobierze jedynie te rekordy, które nie mają odpowiedników w tabeli A – czyli dokładnie to czego nie zawarł w wynikach LEFT JOIN.

Wynik:

id imie id wiek

---- -------- ---- -----

1 Jan 1 20

2 Monika 2 35

4 Stefan NULL NULL

5 Paweł NULL NULL

7 Andrzej 7 24

NULL NULL 3 14

NULL NULL 6 78

NULL NULL 8 46

Wadą tego, że MySQL nie wspiera FULL OUTER JOIN jest to, że sortowanie wyników przy wykorzystaniu sposobu 1 lub 2 staje się cięższe poprzez zastosowanie UNION. Można to jednak obejść tworząc widok (VIEW) na podstawie zapytań z sposobu 1 lub 2 i pracowaniu na tym widoku.

Sumaryczne wykluczające złączenie (FULL OUTER JOIN lub w skrócie FULL JOIN z warunkiem)

Obsługiwane domyślnie przez MySQL: NIE

Jest to złączenie zwracające wszystkie rekordy z tabeli A i tabeli B, które nie mają swoich odpowiedników w drugiej z tabel.

Forma zapisu w SQL:

SELECT * FROM t1 FULL OUTER t2 ON t1.id = t2.id WHERE t1.id IS NULL OR t2.id IS NULL

Ponieważ MySQL nie wspiera tego typu złączenia należy je symulować:

Forma zapisu w MySQL:

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL UNION ALL SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id WHERE t1.id IS NULL

To zapytanie złącza wyniki dwóch zapytań nie eliminując powtórzeń (jest to niepotrzebne gdyż powtórzenia nie mogą wystąpić).

Wynik:

id imie id wiek

---- -------- ---- -----

4 Stefan NULL NULL

5 Paweł NULL NULL

NULL NULL 3 14

NULL NULL 6 78

NULL NULL 8 46

Każdy z każdym (CROSS JOIN lub w skrócie JOIN)

Obsługiwane domyślnie przez MySQL: TAK

Jest to najciekawsze ze złączeń. W wyniku daje wszystkie możliwe kombinacje rekordów z tabeli A z rekordami z tabeli B. Należy o tym pamiętać jeśli chcielibyśmy wykonać takie złączenie na dwóch dużych tabelach. Liczba wyników to wówczas iloczyn liczby rekordów tabeli A i liczby rekordów tabeli B.

Forma zapisu:

SELECT * FROM t1 JOIN t2

Jak widać jest to po prostu JOIN bez warunku złączenia „ON”.

http://sephiro.boo.pl/index.php/2012/07/21/zlaczenia-w-mysql-mysql-joins/