MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS

Anonim

Co to są DOŁĄCZENIA?

Łączenia pomagają w pobieraniu danych z co najmniej dwóch tabel bazy danych. Tabele są wzajemnie powiązane za pomocą kluczy podstawowych i obcych.

Uwaga: JOIN jest najbardziej niezrozumianym tematem wśród osób uczących się SQL. Ze względu na prostotę i łatwość zrozumienia będziemy używać nowej bazy danych do ćwiczenia próbki. Jak pokazano niżej

ID Imię nazwisko movie_id
1 Adam Kowal 1
2 Ravi Kumar 2
3 Susan Davidson 5
4 Przędzarka Adrianna 8
6 Zawietrzny Pong 10
ID tytuł Kategoria
1 KREACJA ZABÓJCY: EMBERS Animacje
2 Prawdziwa stal (2012) Animacje
3 Alvin i wiewiórki Animacje
4 Przygody Tin Tin Animacje
5 Bezpieczny (2012) Akcja
6 Bezpieczny dom (2012) Akcja
7 GIA 18+
8 Termin 2009 18+
9 Brudny obraz 18+
10 Marley i ja Romans

Rodzaje złączeń

Cross DOŁĄCZ

Cross JOIN to najprostsza forma łączenia, która dopasowuje każdy wiersz z jednej tabeli bazy danych do wszystkich wierszy innej.

Innymi słowy, daje nam kombinacje każdego wiersza pierwszej tabeli ze wszystkimi rekordami z drugiej tabeli.

Załóżmy, że chcemy uzyskać wszystkie rekordy członków ze wszystkimi rekordami filmowymi, możemy użyć poniższego skryptu, aby uzyskać pożądane wyniki.

SELECT * FROM `movies` CROSS JOIN `members`

Wykonanie powyższego skryptu w środowisku MySQL daje nam następujące wyniki.

 

 

 

 id title   id first_name last_name movie_id 


 

 1 ASSASSIN'S CREED: EMBERS Animations 1 Adam Smith 1 


 

 1 ASSASSIN'S CREED: EMBERS Animations 2 Ravi Kumar 2 


 

 1 ASSASSIN'S CREED: EMBERS Animations 3 Susan Davidson 5 


 

 1 ASSASSIN'S CREED: EMBERS Animations 4 Jenny Adrianna 8 


 

 1 ASSASSIN'S CREED: EMBERS Animations 6 Lee Pong 10 


 

 2 Real Steel(2012) Animations 1 Adam Smith 1 


 

 2 Real Steel(2012) Animations 2 Ravi Kumar 2 


 

 2 Real Steel(2012) Animations 3 Susan Davidson 5 


 

 2 Real Steel(2012) Animations 4 Jenny Adrianna 8 


 

 2 Real Steel(2012) Animations 6 Lee Pong 10 


 

 3 Alvin and the Chipmunks Animations 1 Adam Smith 1 


 

 3 Alvin and the Chipmunks Animations 2 Ravi Kumar 2 


 

 3 Alvin and the Chipmunks Animations 3 Susan Davidson 5 


 

 3 Alvin and the Chipmunks Animations 4 Jenny Adrianna 8 


 

 3 Alvin and the Chipmunks Animations 6 Lee Pong 10 


 

 4 The Adventures of Tin Tin Animations 1 Adam Smith 1 


 

 4 The Adventures of Tin Tin Animations 2 Ravi Kumar 2 


 

 4 The Adventures of Tin Tin Animations 3 Susan Davidson 5 


 

 4 The Adventures of Tin Tin Animations 4 Jenny Adrianna 8 


 

 4 The Adventures of Tin Tin Animations 6 Lee Pong 10 


 

 5 Safe (2012) Action 1 Adam Smith 1 


 

 5 Safe (2012) Action 2 Ravi Kumar 2 


 

 5 Safe (2012) Action 3 Susan Davidson 5 


 

 5 Safe (2012) Action 4 Jenny Adrianna 8 


 

 5 Safe (2012) Action 6 Lee Pong 10 


 

 6 Safe House(2012) Action 1 Adam Smith 1 


 

 6 Safe House(2012) Action 2 Ravi Kumar 2 


 

 6 Safe House(2012) Action 3 Susan Davidson 5 


 

 6 Safe House(2012) Action 4 Jenny Adrianna 8 


 

 6 Safe House(2012) Action 6 Lee Pong 10 


 

 7 GIA 18+ 1 Adam Smith 1 


 

 7 GIA 18+ 2 Ravi Kumar 2 


 

 7 GIA 18+ 3 Susan Davidson 5 


 

 7 GIA 18+ 4 Jenny Adrianna 8 


 

 7 GIA 18+ 6 Lee Pong 10 


 

 8 Deadline(2009) 18+ 1 Adam Smith 1 


 

 8 Deadline(2009) 18+ 2 Ravi Kumar 2 


 

 8 Deadline(2009) 18+ 3 Susan Davidson 5 


 

 8 Deadline(2009) 18+ 4 Jenny Adrianna 8 


 

 8 Deadline(2009) 18+ 6 Lee Pong 10 


 

 9 The Dirty Picture 18+ 1 Adam Smith 1 


 

 9 The Dirty Picture 18+ 2 Ravi Kumar 2 


 

 9 The Dirty Picture 18+ 3 Susan Davidson 5 


 

 9 The Dirty Picture 18+ 4 Jenny Adrianna 8 


 

 9 The Dirty Picture 18+ 6 Lee Pong 10 


 

 10 Marley and me Romance 1 Adam Smith 1 


 

 10 Marley and me Romance 2 Ravi Kumar 2 


 

 10 Marley and me Romance 3 Susan Davidson 5 


 

 10 Marley and me Romance 4 Jenny Adrianna 8 


 

 10 Marley and me Romance 6 Lee Pong 10

WEWNĘTRZNE DOŁĄCZENIE

Wewnętrzna JOIN służy do zwracania wierszy z obu tabel, które spełniają podany warunek.

Załóżmy, że chcesz uzyskać listę członków, którzy wypożyczyli filmy, wraz z tytułami wypożyczonych przez nich filmów. Możesz po prostu użyć do tego INNER JOIN, które zwróci wiersze z obu tabel spełniające podane warunki.

SELECT members.`first_name` , members.`last_name` , movies.`title`FROM members ,moviesWHERE movies.`id` = members.`movie_id`

Wykonanie powyższego skryptu daje

 

 

 

 first_name last_name title 


 

 Adam Smith ASSASSIN'S CREED: EMBERS 


 

 Ravi Kumar Real Steel(2012) 


 

 Susan Davidson Safe (2012) 


 

 Jenny Adrianna Deadline(2009) 


 

 Lee Pong Marley and me

Zwróć uwagę, że powyższy skrypt wyników można również napisać w następujący sposób, aby osiągnąć te same wyniki.

SELECT A.`first_name` , A.`last_name` , B.`title`FROM `members`AS AINNER JOIN `movies` AS BON B.`id` = A.`movie_id`

Outer JOIN

Zewnętrzne JOIN MySQL zwracają wszystkie pasujące rekordy z obu tabel.

Może wykryć rekordy, które nie pasują do połączonej tabeli. Zwraca wartości NULL dla rekordów tabeli połączonej, jeśli nie zostanie znalezione dopasowanie.

Brzmi myląco? Spójrzmy na przykład -

LEFT JOIN

Załóżmy teraz, że chcesz otrzymywać tytuły wszystkich filmów wraz z nazwiskami członków, którzy je wypożyczyli. Oczywiste jest, że niektóre filmy nie zostały przez nikogo wypożyczone. W tym celu możemy po prostu użyć LEFT JOIN .

LEFT JOIN zwraca wszystkie wiersze z tabeli po lewej stronie, nawet jeśli w tabeli po prawej stronie nie znaleziono pasujących wierszy. Jeśli w tabeli po prawej stronie nie znaleziono żadnych dopasowań, zwracana jest wartość NULL.

SELECT A.`title` , B.`first_name` , B.`last_name`FROM `movies` AS ALEFT JOIN `members` AS BON B.`movie_id` = A.`id`

Wykonanie powyższego skryptu w środowisku MySQL daje. Możesz zobaczyć, że w zwróconym wyniku, który jest wymieniony poniżej, dla filmów, które nie są wypożyczane, pola nazwy członka mają wartości NULL. Oznacza to, że żaden pasujący członek nie znalazł tabeli członków dla tego konkretnego filmu.

 

 

 

 title first_name last_name 


 

 ASSASSIN'S CREED: EMBERS Adam Smith 


 

 Real Steel(2012) Ravi Kumar 


 

 Safe (2012) Susan Davidson 


 

 Deadline(2009) Jenny Adrianna 


 

 Marley and me Lee Pong 


 

 Alvin and the Chipmunks NULL NULL 


 

 The Adventures of Tin Tin NULL NULL 


 

 Safe House(2012) NULL NULL 


 

 GIA NULL NULL 


 

 The Dirty Picture NULL NULL 


 

 


Note: Null is returned for non-matching rows on right

DOŁĄCZ DOŁĄCZ

RIGHT JOIN jest oczywiście przeciwieństwem LEFT JOIN. RIGHT JOIN zwraca wszystkie kolumny z tabeli po prawej stronie, nawet jeśli w tabeli po lewej stronie nie znaleziono pasujących wierszy. Jeśli w tabeli po lewej stronie nie znaleziono żadnych dopasowań, zwracana jest wartość NULL.

W naszym przykładzie załóżmy, że potrzebujesz nazwisk członków i wypożyczonych przez nich filmów. Teraz mamy nowego członka, który nie wypożyczył jeszcze żadnego filmu

SELECT A.`first_name` , A.`last_name`, B.`title`FROM `members` AS ARIGHT JOIN `movies` AS BON B.`id` = A.`movie_id`

Wykonanie powyższego skryptu w środowisku roboczym MySQL daje następujące wyniki.

 

 

 

 first_name last_name title 


 

 Adam Smith ASSASSIN'S CREED: EMBERS 


 

 Ravi Kumar Real Steel(2012) 


 

 Susan Davidson Safe (2012) 


 

 Jenny Adrianna Deadline(2009) 


 

 Lee Pong Marley and me 


 

 NULL NULL Alvin and the Chipmunks 


 

 NULL NULL The Adventures of Tin Tin 


 

 NULL NULL Safe House(2012) 


 

 NULL NULL GIA 


 

 NULL NULL The Dirty Picture 


 

 


Note: Null is returned for non-matching rows on left

Klauzule „ON” i „USING”

W powyższych przykładach zapytań JOIN użyliśmy klauzuli ON, aby dopasować rekordy między tabelami.

Klauzula USING może być również używana w tym samym celu. Różnica polega na tym, że funkcja USING musi mieć identyczne nazwy dla dopasowanych kolumn w obu tabelach.

W tabeli „filmy” do tej pory używaliśmy jej klucza podstawowego o nazwie „id”. Odnieśliśmy się do tego samego w tabeli „członkowie” z nazwą „movie_id”.

Zmieńmy nazwę pola „filmy” tabeli „id” na „movie_id”. Robimy to, aby mieć identyczne dopasowane nazwy pól.

ALTER TABLE `movies` CHANGE `id` `movie_id` INT( 11 ) NOT NULL AUTO_INCREMENT;

Następnie użyjmy USING z powyższym przykładem LEFT JOIN.

SELECT A.`title` , B.`first_name` , B.`last_name`FROM `movies` AS ALEFT JOIN `members` AS BUSING ( `movie_id` )

Oprócz używania ON i USING z JOINs możesz używać wielu innych klauzul MySQL, takich jak GROUP BY, WHERE, a nawet funkcji, takich jak SUM , AVG itp.

Dlaczego powinniśmy używać złączeń?

Teraz możesz pomyśleć, dlaczego używamy JOIN, skoro możemy wykonać to samo zadanie, uruchamiając zapytania. Zwłaszcza jeśli masz doświadczenie w programowaniu baz danych, wiesz, że możemy uruchamiać zapytania pojedynczo, a wyniki każdego z nich wykorzystywać w kolejnych zapytaniach. Oczywiście, że jest to możliwe. Ale używając JOINs, możesz wykonać pracę, używając tylko jednego zapytania z dowolnymi parametrami wyszukiwania. Z drugiej strony MySQL może osiągnąć lepszą wydajność dzięki złączom JOIN, ponieważ może korzystać z indeksowania. Po prostu użycie pojedynczego zapytania JOIN zamiast uruchamiania wielu zapytań zmniejsza obciążenie serwera. Używanie wielu zapytań zamiast tego prowadzi do większej liczby transferów danych między MySQL a aplikacjami (oprogramowaniem). Ponadto wymaga więcej manipulacji danymi również w aplikacji.

Oczywiste jest, że dzięki złączom JOIN możemy osiągnąć lepszą wydajność MySQL i aplikacji.

Podsumowanie

  • SPRZĘŻENIA pozwalają nam łączyć dane z więcej niż jednej tabeli w jeden zestaw wyników.
  • JOINS mają lepszą wydajność w porównaniu do zapytań podrzędnych
  • INNER JOINS zwraca tylko wiersze, które spełniają podane kryteria.
  • OUTER JOINS może również zwracać wiersze, w których nie znaleziono żadnych dopasowań. Niedopasowane wiersze są zwracane za pomocą słowa kluczowego NULL.
  • Główne typy JOIN to wewnętrzne, lewe zewnętrzne, prawe zewnętrzne, krzyżowe itp.
  • Często używaną klauzulą ​​w operacjach JOIN jest „ON”. Klauzula „USING” wymaga, aby pasujące kolumny miały tę samą nazwę.
  • JOINS można również używać w innych klauzulach, takich jak GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS itp.