Co to jest SQL JOINi jakie są różne typy?

Ilustracja z W3schools :

Co to jest SQL JOIN?

SQL JOIN to metoda pobierania danych z dwóch lub więcej tabel baz danych.

Jakie są różne SQL JOINs?

Istnieje w sumie pięć JOINsekund. Oni są :

  1. JOIN or INNER JOIN
  2. OUTER JOIN

     2.1 LEFT OUTER JOIN or LEFT JOIN
     2.2 RIGHT OUTER JOIN or RIGHT JOIN
     2.3 FULL OUTER JOIN or FULL JOIN

  3. NATURAL JOIN
  4. CROSS JOIN
  5. SELF JOIN

1. DOŁĄCZ lub DOŁĄCZ DO WEWNĘTRZNEGO:

W tego rodzaju a JOINotrzymujemy wszystkie rekordy, które pasują do warunków w obu tabelach, a rekordy w obu tabelach, które nie pasują, nie są raportowane.

Innymi słowy, INNER JOINopiera się na jednym fakcie, że: TYLKO pasujące pozycje ZARÓWNO MUSZĄ zostać wymienione tabele.

Zauważ, że JOINbez żadnych innych JOINsłów kluczowych (takich jak INNER, OUTER, LEFTitp) jest INNER JOIN. Innymi słowy, JOINjest cukrem syntaktycznym dla INNER JOIN(patrz: Różnica między JOIN a INNER JOIN ).

2. DOŁĄCZ DO ZEWNĘTRZNEJ:

OUTER JOIN pobiera

Albo dopasowane wiersze z jednej tabeli i wszystkie wiersze w drugiej tabeli Lub wszystkie wiersze we wszystkich tabelach (nie ma znaczenia, czy istnieje dopasowanie).

Istnieją trzy rodzaje łączenia zewnętrznego:

2.1 LEWE DOŁĄCZENIE ZEWNĘTRZNE lub LEWE DOŁĄCZENIE

To połączenie zwraca wszystkie wiersze z lewej tabeli w połączeniu z pasującymi wierszami z prawej tabeli. Jeśli w prawej tabeli nie ma pasujących kolumn, zwracane są NULLwartości.

2.2 PRAWE DOŁĄCZENIE DO ZEWNĄTRZ lub PRAWE DOŁĄCZENIE

Te JOINwszystkie powroty wiersze z prawej tabeli w połączeniu z wierszami pasujące Od lewej tabeli. Jeśli w lewej tabeli nie ma pasujących kolumn, zwracane są NULLwartości.

2.3 PEŁNE DOŁĄCZENIE ZEWNĘTRZNE lub PEŁNE DOŁĄCZENIE

Te JOINkombajny LEFT OUTER JOINi RIGHT OUTER JOIN. Zwraca wiersze z dowolnej tabeli, gdy warunki są spełnione, i zwraca NULLwartość, gdy nie ma zgodności.

Innymi słowy, OUTER JOINopiera się na fakcie, że: POWINIEN być wymienione TYLKO pasujące wpisy w JEDNEJ tabeli (PRAWO lub LEWO) lub OBIE tabele (PEŁNE).

Note that `OUTER JOIN` is a loosened form of `INNER JOIN`.

3. NATURALNE DOŁĄCZENIE:

Opiera się na dwóch warunkach:

1. JOINjest wykonana na wszystkich kolumn o tej samej nazwie na rzecz równości.
2. Usuwa zduplikowane kolumny z wyniku.

Wydaje się to mieć charakter bardziej teoretyczny, w wyniku czego (prawdopodobnie) większość DBMS nawet nie zadaje sobie trudu poparcia tego.

4. POŁĄCZENIE KRZYŻOWE:

Jest to iloczyn kartezjański dwóch zaangażowanych tabel. Wynik CROSS JOINnie ma sensu w większości sytuacji. Co więcej, nie będziemy tego wcale potrzebować (a przynajmniej potrzebują, by być precyzyjnym).

5. DOŁĄCZ DO:

Nie jest to inna forma JOIN, raczej jest to JOIN( INNER, OUTERitp) od stołu do siebie.

DOŁĄCZENIA oparte na operatorach

W zależności od operatora użytego w JOINklauzuli mogą istnieć dwa typy JOINs. Oni są

1. Equi DOŁĄCZ
2. Theta DOŁĄCZ

1. Equi DOŁĄCZ:

Dla dowolnego JOINtypu ( INNER, OUTERitp.), Jeśli użyjemy TYLKO operatora równości (=), wtedy powiemy, że JOINjest to EQUI JOIN.

2. Theta DOŁĄCZ:

Jest to to samo, EQUI JOINale pozwala wszystkim innym operatorom, takim jak>, <,> = itd.

Wielu uważa, zarówno EQUI JOINi Theta JOINpodobna do INNER, OUTER etc JOINs. Ale głęboko wierzę, że jest to błąd i sprawia, że ​​pomysły są niejasne. Ponieważ INNER JOIN, OUTER JOINwszyscy etc są połączone z tabelami i ich danych, natomiast EQUI JOINi THETA JOINsą połączone tylko z operatorami, których używamy w tej pierwszej.

Znów jest wielu, którzy uważają NATURAL JOINza swego rodzaju „osobliwy” EQUI JOIN. W rzeczywistości jest to prawdą z powodu pierwszego warunku, o którym wspomniałem NATURAL JOIN. Nie musimy jednak ograniczać się do tego NATURAL JOINsamego. INNER JOINs, OUTER JOINs itp. też może być EQUI JOIN.

Definicja:

ŁĄCZENIA są sposobem na zapytanie danych połączonych jednocześnie z wielu tabel jednocześnie.

Rodzaje ŁĄCZNIKÓW:

W przypadku RDBMS istnieje 5 rodzajów sprzężeń:

• Equi-Join: Łączy wspólne rekordy z dwóch tabel w oparciu o warunek równości. Technicznie rzecz biorąc, łączenie wykonane za pomocą operatora równości (=) w celu porównania wartości klucza podstawowego jednej tabeli i wartości klucza obcego innej tabeli, dlatego zestaw wyników zawiera wspólne (dopasowane) rekordy z obu tabel. Wdrożenie patrz INNER-JOIN.

• Natural-Join: Jest to ulepszona wersja Equi-Join, w której operacja SELECT pomija duplikat kolumny. Wdrożenie patrz INNER-JOIN

• Non-Equi-Join: Jest odwrotnością Equi-join, gdzie warunkiem łączenia jest użycie operatora innego niż równy (=) np.! =, <=,> =,>, <Lub MIĘDZY itp. W celu uzyskania informacji na temat implementacji patrz WEWNĘTRZNE ŁĄCZENIE.

• Self-Join:: Dostosowane zachowanie złączenia, w którym stół łączy się ze sobą; Jest to zwykle potrzebne do tworzenia zapytań w tabelach z odnośnikami (lub jednostkową jednostką relacji). Aby zapoznać się z implementacją, zobacz WEWNĘTRZNE ŁĄCZENIA.

• Produkt kartezjański: krzyż łączy wszystkie rekordy obu tabel bez żadnych warunków. Technicznie zwraca zestaw wyników zapytania bez klauzuli WHERE.

Zgodnie z troską i postępem SQL istnieją 3 typy sprzężeń, a wszystkie sprzężenia RDBMS można uzyskać za pomocą tych rodzajów sprzężeń.

1. INNER-JOIN: Łączy (lub łączy) dopasowane wiersze z dwóch tabel. Dopasowywanie odbywa się na podstawie wspólnych kolumn tabel i ich operacji porównywania. Jeżeli warunek oparty jest na równości, wówczas: Wykonano EQUI-JOIN, w innym przypadku Non-EQUI-Join.

2. OUTER-JOIN: Łączy (lub łączy) dopasowane wiersze z dwóch tabel i niepasujące wiersze z wartościami NULL. Można jednak dostosować wybór niepasujących wierszy, np. Wybierając niepasujący wiersz z pierwszej tabeli lub drugiej tabeli według podtypów: POŁĄCZENIE ZEWNĘTRZNE i POŁĄCZENIE ZEWNĘTRZNE.

   2.1 LEFT Outer JOIN (aka, LEFT-JOIN): Zwraca tylko dopasowane wiersze z dwóch tabel i niepasujące tylko z tabeli LEFT (tj. Pierwszej tabeli).

   2.2 RIGHT Outer JOIN (aka, RIGHT-JOIN): Zwraca dopasowane wiersze z dwóch tabel i niepasujące tylko z prawej tabeli.

   2.3 FULL OUTER JOIN (alias OUTER JOIN): Zwraca dopasowane i niepasujące z obu tabel.

3. CROSS-JOIN: To połączenie nie łączy się / nie łączy, lecz tworzy produkt kartezjański.

Uwaga: Self-JOIN można osiągnąć za pomocą INNER-JOIN, OUTER-JOIN i CROSS-JOIN na podstawie wymagań, ale tabela musi łączyć się ze sobą.

Po więcej informacji:

Przykłady:

1.1: INNER-JOIN: Implementacja Equi-join

SELECT  *
FROM Table1 A 
 INNER JOIN Table2 B ON A.<Primary-Key> =B.<Foreign-Key>;

1.2: INNER-JOIN: Implementacja Natural-JOIN

Select A.*, B.Col1, B.Col2          --But no B.ForeignKeyColumn in Select
 FROM Table1 A
 INNER JOIN Table2 B On A.Pk = B.Fk;

1.3: INNER-JOIN z implementacją NON-Equi-join

Select *
 FROM Table1 A INNER JOIN Table2 B On A.Pk <= B.Fk;

1.4: ŁĄCZENIE WEWNĘTRZNE z JAŁOWYM ŁĄCZENIEM

Select *
 FROM Table1 A1 INNER JOIN Table1 A2 On A1.Pk = A2.Fk;

2.1: DOŁĄCZENIE ZEWNĘTRZNE (pełne połączenie zewnętrzne)

Select *
 FROM Table1 A FULL OUTER JOIN Table2 B On A.Pk = B.Fk;

2.2: LEWE DOŁĄCZ

Select *
 FROM Table1 A LEFT OUTER JOIN Table2 B On A.Pk = B.Fk;

2.3: PRAWE DOŁĄCZ

Select *
 FROM Table1 A RIGHT OUTER JOIN Table2 B On A.Pk = B.Fk;

3.1: POŁĄCZENIE KRZYŻOWE

Select *
 FROM TableA CROSS JOIN TableB;

3.2: SKRZYDŁO DOŁĄCZ - Self JOIN

Select *
 FROM Table1 A1 CROSS JOIN Table1 A2;

//LUB//

Select *
 FROM Table1 A1,Table1 A2;

Co ciekawe, większość innych odpowiedzi ma te dwa problemy:

• Koncentrują się tylko na podstawowych formach łączenia
• Używają (ab) diagramów Venna, które są niedokładnym narzędziem do wizualizacji połączeń (są znacznie lepsze dla związków) .

Niedawno napisałem artykuł na ten temat: Prawdopodobnie niepełny, kompleksowy przewodnik po wielu różnych sposobach ŁĄCZENIA tabel w SQL , który streszczę tutaj.

Przede wszystkim: DOŁĄCZY to produkty kartezjańskie

Dlatego diagramy Venna wyjaśniają je tak niedokładnie, ponieważ JOIN tworzy iloczyn kartezjański między dwoma połączonymi tabelami. Wikipedia ładnie to ilustruje:

Składnia SQL dla produktów kartezjańskich to CROSS JOIN. Na przykład:

SELECT *

-- This just generates all the days in January 2017
FROM generate_series(
  '2017-01-01'::TIMESTAMP,
  '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day',
  INTERVAL '1 day'
) AS days(day)

-- Here, we're combining all days with all departments
CROSS JOIN departments

Który łączy wszystkie wiersze z jednej tabeli ze wszystkimi wierszami z drugiej tabeli:

Źródło:

+--------+   +------------+
| day    |   | department |
+--------+   +------------+
| Jan 01 |   | Dept 1     |
| Jan 02 |   | Dept 2     |
| ...    |   | Dept 3     |
| Jan 30 |   +------------+
| Jan 31 |
+--------+

Wynik:

+--------+------------+
| day    | department |
+--------+------------+
| Jan 01 | Dept 1     |
| Jan 01 | Dept 2     |
| Jan 01 | Dept 3     |
| Jan 02 | Dept 1     |
| Jan 02 | Dept 2     |
| Jan 02 | Dept 3     |
| ...    | ...        |
| Jan 31 | Dept 1     |
| Jan 31 | Dept 2     |
| Jan 31 | Dept 3     |
+--------+------------+

Jeśli po prostu napiszemy listę tabel oddzieloną przecinkami, otrzymamy to samo:

-- CROSS JOINing two tables:
SELECT * FROM table1, table2

INNER JOIN (Theta-JOIN)

Jest INNER JOINto po prostu filtrowany, w CROSS JOINktórym predykat filtru jest wywoływany Thetaw algebrze relacyjnej.

Na przykład:

SELECT *

-- Same as before
FROM generate_series(
  '2017-01-01'::TIMESTAMP,
  '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day',
  INTERVAL '1 day'
) AS days(day)

-- Now, exclude all days/departments combinations for
-- days before the department was created
JOIN departments AS d ON day >= d.created_at

Pamiętaj, że słowo kluczowe INNERjest opcjonalne (z wyjątkiem MS Access).

( spójrz na artykuł, aby zobaczyć przykłady wyników )

DOŁĄCZ DO EQUI

Specjalnym rodzajem Theta-JOIN jest equi JOIN, którego używamy najczęściej. Predykat łączy klucz podstawowy jednej tabeli z kluczem obcym innej tabeli. Jeśli użyjemy bazy danych Sakila do zilustrowania, możemy napisać:

SELECT *
FROM actor AS a
JOIN film_actor AS fa ON a.actor_id = fa.actor_id
JOIN film AS f ON f.film_id = fa.film_id

Łączy to wszystkich aktorów z ich filmami.

Lub też w niektórych bazach danych:

SELECT *
FROM actor
JOIN film_actor USING (actor_id)
JOIN film USING (film_id)

USING()Składnia pozwala na określenie kolumny, które muszą być obecne na obu stronach łączenia tabel operacja i tworzy predykat równości w tych dwóch kolumnach.

NATURALNE DOŁĄCZENIE

Inne odpowiedzi wymieniały ten „JOIN type” osobno, ale to nie ma sensu. Jest to po prostu składniowa forma cukru dla equi JOIN, która jest specjalnym przypadkiem Theta-JOIN lub INNER JOIN. NATURAL JOIN po prostu zbiera wszystkie kolumny wspólne dla obu tabel, które są łączone i łączy USING()te kolumny. Co rzadko kiedy jest przydatne, z powodu przypadkowych dopasowań (takich jak LAST_UPDATEkolumny w bazie danych Sakila ).

Oto składnia:

SELECT *
FROM actor
NATURAL JOIN film_actor
NATURAL JOIN film

DOŁĄCZ DO ZEWNĘTRZNEJ

Teraz OUTER JOINróżni się nieco od INNER JOINtego, że tworzy UNIONkilka kartezjańskich produktów. Możemy pisać:

-- Convenient syntax:
SELECT *
FROM a LEFT JOIN b ON <predicate>

-- Cumbersome, equivalent syntax:
SELECT a.*, b.*
FROM a JOIN b ON <predicate>
UNION ALL
SELECT a.*, NULL, NULL, ..., NULL
FROM a
WHERE NOT EXISTS (
  SELECT * FROM b WHERE <predicate>
)

Nikt nie chce pisać tego drugiego, więc piszemy OUTER JOIN(co zwykle jest lepiej zoptymalizowane przez bazy danych).

Słowo INNERkluczowe OUTERjest tutaj opcjonalne.

OUTER JOIN występuje w trzech smakach:

• LEFT [ OUTER ] JOIN: Lewa tabela JOINwyrażenia jest dodawana do unii, jak pokazano powyżej.
• RIGHT [ OUTER ] JOIN: Prawa tabela JOINwyrażenia jest dodawana do unii, jak pokazano powyżej.
• FULL [ OUTER ] JOIN: Obie tabele JOINwyrażenia są dodawane do unii, jak pokazano powyżej.

Wszystkie one mogą być łączone za pomocą słowa kluczowego USING()lub NATURAL( ja faktycznie miał prawdziwy świat przypadków użycia dotyczący NATURAL FULL JOINniedawno )

Alternatywne składnie

Istnieje kilka historycznych, przestarzałych składni w Oracle i SQL Server, które były obsługiwane OUTER JOINjuż zanim standard SQL miał taką składnię:

-- Oracle
SELECT *
FROM actor a, film_actor fa, film f
WHERE a.actor_id = fa.actor_id(+)
AND fa.film_id = f.film_id(+)

-- SQL Server
SELECT *
FROM actor a, film_actor fa, film f
WHERE a.actor_id *= fa.actor_id
AND fa.film_id *= f.film_id

Powiedziawszy to, nie używaj tej składni. Po prostu wymienię to tutaj, abyś mógł rozpoznać go ze starych postów na blogu / starszego kodu.

Podzielony na partycje OUTER JOIN

Niewiele osób wie o tym, ale standard SQL określa partycjonowanie OUTER JOIN(a Oracle to implementuje). Możesz pisać takie rzeczy:

WITH

  -- Using CONNECT BY to generate all dates in January
  days(day) AS (
    SELECT DATE '2017-01-01' + LEVEL - 1
    FROM dual
    CONNECT BY LEVEL <= 31
  ),

  -- Our departments
  departments(department, created_at) AS (
    SELECT 'Dept 1', DATE '2017-01-10' FROM dual UNION ALL
    SELECT 'Dept 2', DATE '2017-01-11' FROM dual UNION ALL
    SELECT 'Dept 3', DATE '2017-01-12' FROM dual UNION ALL
    SELECT 'Dept 4', DATE '2017-04-01' FROM dual UNION ALL
    SELECT 'Dept 5', DATE '2017-04-02' FROM dual
  )
SELECT *
FROM days 
LEFT JOIN departments 
  PARTITION BY (department) -- This is where the magic happens
  ON day >= created_at

Części wyniku:

+--------+------------+------------+
| day    | department | created_at |
+--------+------------+------------+
| Jan 01 | Dept 1     |            | -- Didn't match, but still get row
| Jan 02 | Dept 1     |            | -- Didn't match, but still get row
| ...    | Dept 1     |            | -- Didn't match, but still get row
| Jan 09 | Dept 1     |            | -- Didn't match, but still get row
| Jan 10 | Dept 1     | Jan 10     | -- Matches, so get join result
| Jan 11 | Dept 1     | Jan 10     | -- Matches, so get join result
| Jan 12 | Dept 1     | Jan 10     | -- Matches, so get join result
| ...    | Dept 1     | Jan 10     | -- Matches, so get join result
| Jan 31 | Dept 1     | Jan 10     | -- Matches, so get join result

Chodzi o to, że wszystkie rzędy od podzielonej strony złączenia skończą się w wyniku, niezależnie od tego, czy JOINpasują coś po „drugiej stronie DOŁĄCZENIA”. Krótka historia: ma to na celu uzupełnienie rzadkich danych w raportach. Bardzo przydatne!

SEMI DOŁĄCZ

Poważnie? Nie ma innej odpowiedzi? Oczywiście nie, ponieważ nie ma natywnej składni w SQL, niestety (podobnie jak poniżej ANTI JOIN). Ale możemy użyć IN()i EXISTS()np. Znaleźć wszystkich aktorów, którzy grali w filmach:

SELECT *
FROM actor a
WHERE EXISTS (
  SELECT * FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

WHERE a.actor_id = fa.actor_idOrzecznik działa jako semi dołączyć orzeczenie. Jeśli w to nie wierzysz, sprawdź plany wykonania, np. W Oracle. Zobaczysz, że baza danych wykonuje operację SEMI JOIN, a nie EXISTS()predykat.

ANTI DOŁĄCZ

To jest po prostu przeciwieństwem SEMI JOIN ( należy uważać, aby nie używać NOT INchociaż , jak to ma istotne zastrzeżenie)

Oto wszyscy aktorzy bez filmów:

SELECT *
FROM actor a
WHERE NOT EXISTS (
  SELECT * FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

Niektórzy ludzie (szczególnie MySQL) również piszą ANTI JOIN w następujący sposób:

SELECT *
FROM actor a
LEFT JOIN film_actor fa
USING (actor_id)
WHERE film_id IS NULL

Myślę, że historycznym powodem jest wydajność.

POŁĄCZENIE BOCZNE

OMG, ten jest za fajny. Tylko ja o tym wspominam? Oto fajne zapytanie:

SELECT a.first_name, a.last_name, f.*
FROM actor AS a
LEFT OUTER JOIN LATERAL (
  SELECT f.title, SUM(amount) AS revenue
  FROM film AS f
  JOIN film_actor AS fa USING (film_id)
  JOIN inventory AS i USING (film_id)
  JOIN rental AS r USING (inventory_id)
  JOIN payment AS p USING (rental_id)
  WHERE fa.actor_id = a.actor_id -- JOIN predicate with the outer query!
  GROUP BY f.film_id
  ORDER BY revenue DESC
  LIMIT 5
) AS f
ON true

Znajduje TOP 5 filmów generujących dochód na aktora. Za każdym razem, gdy potrzebujesz zapytania TOP-N-per-coś, LATERAL JOINstanie się Twoim przyjacielem. Jeśli jesteś osobą korzystającą z programu SQL Server, znasz ten JOINtyp pod nazwąAPPLY

SELECT a.first_name, a.last_name, f.*
FROM actor AS a
OUTER APPLY (
  SELECT f.title, SUM(amount) AS revenue
  FROM film AS f
  JOIN film_actor AS fa ON f.film_id = fa.film_id
  JOIN inventory AS i ON f.film_id = i.film_id
  JOIN rental AS r ON i.inventory_id = r.inventory_id
  JOIN payment AS p ON r.rental_id = p.rental_id
  WHERE fa.actor_id = a.actor_id -- JOIN predicate with the outer query!
  GROUP BY f.film_id
  ORDER BY revenue DESC
  LIMIT 5
) AS f

OK, może to oszustwo, ponieważ wyrażenie LATERAL JOINlub APPLYjest tak naprawdę „skorelowanym podzapytaniem”, które tworzy kilka wierszy. Ale jeśli pozwolimy na „skorelowane podkwerendy”, możemy również mówić o ...

MULTISET

Jest to naprawdę zaimplementowane tylko przez Oracle i Informix (o ile mi wiadomo), ale może być emulowane w PostgreSQL za pomocą tablic i / lub XML oraz w SQL Server za pomocą XML.

MULTISETtworzy skorelowane podzapytanie i zagnieżdża wynikowy zestaw wierszy w zewnętrznym zapytaniu. Poniższe zapytanie wybiera wszystkich aktorów i dla każdego aktora gromadzi filmy w zagnieżdżonej kolekcji:

SELECT a.*, MULTISET (
  SELECT f.*
  FROM film AS f
  JOIN film_actor AS fa USING (film_id)
  WHERE a.actor_id = fa.actor_id
) AS films
FROM actor

Jak widać, istnieje więcej rodzajów JOIN niż tylko „Boring” INNER, OUTERi CROSS JOINktóre zazwyczaj są wymienione. Więcej szczegółów w moim artykule . I proszę, przestańcie używać diagramów Venna do ich zilustrowania.

Stworzyłem, moim zdaniem, ilustrację lepszą niż słowa:

Będę naciskać na mojego zwierzaka: słowo kluczowe USING.

Jeśli obie tabele po obu stronach JOIN mają poprawnie nazwane klucze obce (tj. Tę samą nazwę, a nie tylko „id”), można tego użyć:

SELECT ...
FROM customers JOIN orders USING (customer_id)

Uważam to za bardzo praktyczne, czytelne i niezbyt często używane.