Klauzule JOIN w SQL - devszczepaniak.pl

Klauzule JOIN w SQL, mimo że są prostym zagadnieniem, dość długi czas sprawiały mi problem. Wykorzystanie klauzul JOIN w praktyce pozwoliło mi na lepsze zrozumienie jej działania, dzięki czemu czytasz ten artykuł. W tym artykule przedstawię Ci najprzydatniejsze typy klauzul JOIN. Każdy typ JOIN-a opatrzony jest praktycznym przykładem. Wszystkie przykłady, które przedstawiłem w tym artykule, przygotowane zostały w systemie zarządzania baz danych MySQL, więc jeśli korzystasz z innego dialektu, to mogą wystąpić drobne różnice.

Przygotowałem dla Ciebie prostą bazę danych, którą możesz wykorzystać do praktycznego sprawdzenia przykładów z tego artykułu. Możesz też skorzystać gotowej do użycia bazy danych przygotowanej przez W3Schools. Bazę możesz uruchomić z wykorzystaniem lokalnie uruchomionego serwera MySQL lub możesz wykorzystać dowolny sandbox dla MySQL’a. Od siebie mogę polecić DB Fiddle, z którego korzystałem, tworząc ten artykuł. W pole opatrzone etykietą Schema SQL należy wkleić zapytania związane z tworzeniem tabel oraz dodawaniem danych — operacje DDL (Data Definition Language). Z kolei zapytania z dalszej części artykułu należy wkleić w pole z etykietą Query SQL, a następnie kliknąć przycisk Run.

Sama baza jest bazą danych firmy zajmującej się wynajmem samochodów. Struktura bazy danych została przedstawiona na poniższym diagramie.

Natomiast kod niezbędny do utworzenia tabel wygląda następująco.


CREATE TABLE customers(
  id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(30) NOT NULL,
  last_name VARCHAR(40) NOT NULL,
  address VARCHAR(60) NOT NULL,
  phone VARCHAR(12) NOT NULL,
  postal_code VARCHAR(6) NOT NULL,
  city VARCHAR(40) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE cars(
  id INT NOT NULL AUTO_INCREMENT,
  brand VARCHAR(30) NOT NULL,
  model VARCHAR(30) NOT NULL,
  production_year CHAR(4),
  mileage INT NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE rentals(
  id INT NOT NULL AUTO_INCREMENT,
  customer_id INT NOT NULL,
  car_id INT NOT NULL,
  start_date DATETIME(3) NOT NULL,
  end_date DATETIME(3) NOT NULL,
  returned BOOLEAN DEFAULT false,
  PRIMARY KEY (id),
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (car_id) REFERENCES cars(id)
);

Sama struktura bazy danych jest dosyć prosta. Jest tabela z klientami, tabela z samochodami oraz tabela z wypożyczeniami. Ta ostatnia zawiera 2 klucze obce z referencjami do klienta oraz wynajmowanego pojazdu.

Mając już zdefiniowane tabele, pora wypełnić je danymi. Możesz wykorzystać dane przygotowane przeze mnie lub wstawić własne.


INSERT INTO
  customers(first_name, last_name, address, phone, postal_code, city)
VALUES
  ('John', 'Doe', 'address', '123456789', '12-345', 'Boston'),
  ('Tom', 'Smith', 'address', '123789456', '12-234', 'Boston'),
  ('Adam', 'Brown', 'address', '156723489', '60-001', 'New York'),
  ('Sam', 'Jones', 'address', '123789456', '34-298', 'Phoenix'),
  ('Ann', 'Gates', 'address', '412356789', '12-234', 'Boston'),
  ('Lisa', 'Kennedy', 'address', '127893456', '13-254', 'Denver'); 
    
INSERT INTO
  cars(brand, model, production_year, mileage)
VALUES
  ('Opel', 'Astra', '2013', 323456),
  ('Opel', 'Astra', '2015', 236368),
  ('Opel', 'Astra', '2018', 123450),
  ('Toyota', 'Yaris', '2019', 140923),
  ('Toyota', 'Yaris', '2019', 156988),
  ('Toyota', 'Yaris', '2019', 98003),
  ('Ford', 'Mustang', '2023', 0);
  
INSERT INTO
  rentals(customer_id, car_id, start_date, end_date, returned)
VALUES
  (1, 1, '2022-09-01', '2022-09-26', true),
  (1, 2, '2022-09-27', '2022-12-31', true),
  (1, 3, '2023-02-01', '2023-09-26', false),
  (2, 1, '2022-09-27', '2023-09-26', false),
  (3, 1, '2020-09-01', '2021-09-26', true),
  (4, 4, '2022-11-01', '2022-12-26', true),
  (3, 4, '2022-12-27', '2022-12-28', true),
  (4, 5, '2022-09-01', '2022-09-26', true),
  (6, 6, '2022-09-01', '2022-09-03', true);

Zanim jednak przejdę do omówienia, jakie klauzule JOIN mamy do dyspozycji, warto napisać kilka słów, czym jest JOIN i do czego służy.

Czym jest JOIN?

Klauzula JOIN jest opcjonalnym elementem klauzuli SELECT. Dzięki klauzuli JOIN możliwe jest łączenie danych z kilku (choć jak się później dowiesz, niekoniecznie) zbiorów. Klauzule JOIN opierają się na identyfikatorach zasobów, gdzie każdy zbiór wykorzystany w zapytaniu powinien zawierać kolumnę, po której będzie odbywać się łączenie. W przedstawionej bazie danych sytuacja jest dość prosta. Łączenia będą odbywać się na podstawie kluczy głównych i kluczy obcych przygotowanych tabel. Pamiętaj jednak, że łączenie nie musi odbywać się po kluczach. Zbiory można łączyć po dowolnych kolumnach. Najbardziej trywialne zastosowanie JOIN-a przedstawia poniższy przykład.


SELECT * FROM table JOIN another_table ON table.column_a = another_table.column_b;

Na tej składni bazują (prawie) wszystkie rodzaje JOIN-ów. W dalszej części artykułu, w celu uproszczenia, będę zakładał, że operujemy na dwóch zbiorach.

LEFT JOIN i RIGHT JOIN

Zapytanie SELECT wykorzystujące klauzulę LEFT JOIN zwróci wyniki dla lewego zbioru oraz część wspólną dla obu zbiorów. Lewym zbiorem nazywamy tabelę, która została zdefiniowana po słowie kluczowym FROM. Rezultat zapytania wykorzystującego LEFT JOIN przedstawia rysunek poniżej.

Z przygotowanej bazy chciałbym pozyskać wszystkie wypożyczenia wraz z informacjami o wypożyczonych pojazdach. Mogę tego dokonać, wywołując następujące zapytanie.


SELECT * FROM rentals LEFT JOIN cars ON rentals.car_id = cars.id;

Jeśli wywołasz zapytanie, to zobaczysz, że w rezultatach są dwie kolumny z nagłówkiem id. Jedno z id to identyfikator wypożyczenia, a drugi jest identyfikatorem samochodu. Jeśli zmodyfikujesz zapytanie do SELECT id FROM rentals LEFT JOIN cars ON rentals.car_id = cars.id; to otrzymasz błąd Column 'id' in field list is ambiguous. System zarządzania bazą danych nie jest w stanie się domyślić, które id chcemy uzyskać, dlatego należy mu pomóc. Wystarczy zadeklarować, z której tabeli powinno zostać wyświetlone id: SELECT cars.id FROM rentals LEFT JOIN cars ON rentals.car_id = cars.id;. Analogicznie działa to przypadku innych części zapytania SELECT.


SELECT DISTINCT cars.id FROM rentals LEFT JOIN cars ON rentals.car_id = cars.id WHERE cars.id < 6 ORDER BY cars.id DESC;

W analogiczny sposób działa RIGHT JOIN, z tym że zwrócona zostanie zawartość zboru dołączonego (prawego) oraz część wspólna. Dla przykładowego zapytania, wykorzystanie RIGHT JOIN zwróci dodatkowy wiersz z pojazdem, dla którego nie znaleziono żadnego wypożyczenia.

Wariantem LEFT i RIGHT JOIN wartym uwagi jest exclusive JOIN. Takie zapytanie pozwala na wykluczenie części wspólnej zbiorów, czyli w przypadku exclusive LEFT JOIN zostanie zwrócona zawartość zbioru lewego z wyłączeniem części wspólnej.

MySQL rezultat odpowiadający exclusive JOIN pozwala uzyskać poprzez wykorzystanie klauzuli WHERE, gdzie interesującymi rezultatami dołączonego zbioru (prawego) w tym przypadku są wartości NULL. Na przykładzie demonstracyjnej bazy, możemy w ten sposób uzyskać samochody, które nie zostały wypożyczone.


SELECT * FROM cars LEFT JOIN rentals ON rentals.car_id = cars.id WHERE rentals.car_id IS NULL;

Ostatnim aspektem, na który chciałbym zwrócić uwagę, jest LEFT i RIGHT OUTER JOIN. Otóż LEFT JOIN i LEFT OUTER JOIN są identyczne. Słowo OUTER jest opcjonalne w klauzuli LEFT JOIN. Analogicznie sytuacja wygląda dla RIGHT JOIN i RIGHT OUTER JOIN. Zachęcam do sprawdzenia przykładowych zapytań z wykorzystaniem słowa kluczowego OUTER i sprawdzenia, jaki otrzymasz rezultat.

INNER JOIN

Wykorzystanie klauzuli INNER JOIN powoduje, że zwrócona zostaje jedynie wspólna część zbioru.

Porównując rezultaty dla następujących zapytań:


SELECT * FROM cars LEFT JOIN rentals ON rentals.car_id = cars.id;

SELECT * FROM cars INNER JOIN rentals ON rentals.car_id = cars.id;

można zauważyć, że w przypadku INNER JOIN nie został zwrócony pojazd, dla którego nie ma żadnego wypożyczenia. Warto również podkreślić, że INNER JOIN jest domyślnym JOIN-em w MySQL, co oznacza, że poniższe klauzule są tożsame.


SELECT * FROM cars INNER JOIN rentals ON rentals.car_id = cars.id;

SELECT * FROM cars JOIN rentals ON rentals.car_id = cars.id;

FULL JOIN

Klauzula FULL JOIN powoduje złączenie całości zbiorów lewego oraz prawego.

W przypadku MySQL do dyspozycji nie ma bezpośredniej klauzuli FULL JOIN. Następujące zapytanie zwróci błąd.


SELECT * FROM rentals FULL JOIN cars ON rentals.id = cars.id;

W MySQL działanie FULL JOIN można zasymulować, wykorzystując klauzulę UNION.


SELECT * FROM rentals
  LEFT JOIN cars ON rentals.car_id = cars.id

UNION

SELECT * FROM rentals
  RIGHT JOIN cars ON rentals.car_id = cars.id;

Rezultatem będzie zwrócenie wszystkich rezerwacji wraz z odpowiadającymi im samochodami. Dodatkowo zostanie zwrócony samochód, dla którego nie ma żadnego wypożyczenia w bazie danych. Ciekawy rezultat otrzymasz, jeśli dołączysz trzeci zbiór.


SELECT * FROM rentals
  LEFT JOIN cars ON rentals.car_id = cars.id
  RIGHT JOIN customers ON rentals.customer_id = customers.id

UNION

SELECT * FROM rentals
  RIGHT JOIN cars ON rentals.car_id = cars.id
  LEFT JOIN customers ON rentals.customer_id = customers.id;

Rezultat będzie dodatkowo zawierał klienta, do którego przypisane jest wypożyczenie. Dodatkowo zostaną zwrócone dane jednej z klientek bez wypożyczeń.

Uzyskanie exclusive FULL JOIN wygląda analogicznie jak w przypadku LEFT i RIGHT JOIN. W przypadku trzech zbiorów zwrócone zostaną rezultaty, które nie mają odpowiadających elementów w innych zbiorach. Sytuację opisuje poniższy diagram.

Zapytanie dla opisywanej sytuacji wygląda następująco.


SELECT * FROM rentals
  LEFT JOIN cars ON rentals.car_id = cars.id
  RIGHT JOIN customers ON rentals.customer_id = customers.id
  WHERE customers.id IS NULL OR cars.id IS NULL

UNION

SELECT * FROM rentals
  RIGHT JOIN cars ON rentals.car_id = cars.id
  LEFT JOIN customers ON rentals.customer_id = customers.id
  WHERE customers.id IS NULL OR cars.id IS NULL;

Wynikiem zapytania będzie jeden samochód i jedna klientka bez żadnego wypożyczenia.

Podobnie jak w przypadku LEFT i RIGHT JOIN, słowo kluczowe OUTER jest opcjonalne a FULL JOIN i FULL OUTER JOIN są tożsame.

CROSS JOIN

CROSS JOIN jest iloczynem kartezjańskim zbiorów. Zalecam ostrożność w używaniu CROSS JOIN, ponieważ zapytanie może zwracać ogromne rezultaty. Uzyskanie CROSS JOIN-a w MySQL polega na pozbyciu się fragmentu mapującego identyfikatory ze zbiorów. Iloczyn kartezjański dla wypożyczeń i samochodów wynosi 7*9 = 63 elementy.


SELECT count(*) FROM cars JOIN rentals;

SELF JOIN

SELF JOIN jest typem JOIN-a, który wymaga tylko jednego zbioru. W SELF JOIN zbiór jest łączony sam ze sobą i znajduje zastosowanie, gdy elementy zbioru mają w sobie referencje do innych elementów z tego samego zbioru. W celu praktycznego przetestowania SELF JOIN przygotowałem inny przykładowy zbiór danych.


CREATE TABLE employees(
  id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(30) NOT NULL,
  last_name VARCHAR(40) NOT NULL,
  role VARCHAR(60) NOT NULL,
  supervisor_id INT,
  PRIMARY KEY (id)
);

INSERT INTO employees(first_name, last_name, role, supervisor_id) 
VALUES
  ('John', 'Doe', 'chairman', NULL),
  ('Tom', 'Smith', 'manager', 1),
  ('Adam', 'Brown', 'accountant', 2),
  ('Sam', 'Jones', 'office assistant', 1),
  ('Ann', 'Gates', 'accountant', 2)
;

Kolumna supervisor_id jest referencją do id innego pracownika z tej samej tabeli. Powyższy przykład jest klasycznym przykładem demonstracji SELF JOIN-a. Celem jest wyświetlenie wszystkich pracowników z dodatkową kolumną z imieniem i nazwiskiem przełożonego. W tym celu wykorzystałem LEFT JOIN gdzie zarówno lewy jak i prawy zbór to zawartość tabeli employees.


SELECT
  e.*,
  CONCAT(s.first_name, ' ', s.last_name) as supervisor
FROM
  employees e
LEFT JOIN
  employees s ON  s.id = e.supervisor_id;

Zapytanie zwróci wszystkich pracowników wraz z przypisanym przełożonym oraz prezesa, który nie ma przełożonego. Gdybyśmy chcieli wyświetlić tylko tych pracowników, którzy mają przełożonego, wystarczy LEFT JOIN zastąpić INNER JOINem.

Podsumowanie

Na koniec zostawię Ci kilka rad, które pozwolą Ci lepiej i wydajniej stosować klauzule JOIN:

Niektóre kolumny mogą zawierać kolumny o tych samych nazwach. Korzystaj z klauzuli AS w celu nadania nazw pozwalających jednoznacznie odróżniać kolumny.
Z punktu widzenia wydajności, lepiej jest do większego zbioru dołączać mniejszy.
Wykorzystanie klauzuli OR w zapytaniach z klauzulą JOIN może powodować problemy z wydajnością.
Z punktu widzenia wydajności, mając do wyboru klauzule JOIN i podzapytanie, zwykle lepszym wyborem będzie JOIN.
Staraj się unikać CROSS JOIN, jeśli to możliwe.

Mam nadzieję, że dowiedziałeś/aś się dzisiaj czegoś nowego. Zachęcam do pozostawienia komentarza, udostępnienia wpisu i zapoznania się z materiałami dodatkowymi.

Źródła i materiały dodatkowe

Czy wiesz, że devszczepaniak.pl jest na Facebooku?

Chcesz otrzymywać informację o nowych wpisach zaraz po publikacji?
Koniecznie zaobserwuj devszczepaniak.pl na Facebooku.

Udostępnij wpis

Szukasz taniego i dobrego VPS? Skorzystaj z Mikrusa i przy zakupie odbierz dodatkowy miesiąc usługi za darmo!

Chcesz docenić moją pracę? Postaw mi kawę!

Zapisz się na mailing i odbierz e-booka

Zapisując się na mój mailing, otrzymasz darmowy egzemplarz e-booka 106 Pytań Rekrutacyjnych Junior JavaScript Developer! Będziesz też otrzymywać wartościowe treści i powiadomienia o nowych wpisach na skrzynkę e-mail.