Ilustracja z W3schools :

Definicja:

Połączenia są sposobem na odpytywanie danych, które zostały połączone z wielu tabel jednocześnie.

Rodzaje połączeń:

Troska o RDBMS jest 5-typów połączeń:

• Equi-Join: łączy wspólne rekordy z dwóch tabel opartych na warunku równości. Technicznie, Join made by using equality-operator ( = ) to compare values of PrimaryKey of one table and Foriegn Key values of anther table, stąd zestaw wyników zawiera wspólne (dopasowane) rekordy z obu tabel. Dla implementacji patrz INNER-JOIN.

• Natural-Join: jest to ulepszona wersja Equi-Join, w której SELECT operacja pomija zduplikowaną kolumnę. Do implementacji zobacz INNER-JOIN

• Non-Equi-Join: jest odwrotnością Equi-join, gdzie warunek łączenia jest inny niż operator równości (=) np. !=, =, >,

• Self-Join: : niestandardowe zachowanie join, w którym tabela jest połączona ze sobą; zwykle jest to potrzebne do odpytywania tabel samo-odwołujących się (lub jednostki jednoczącej). W celu implementacji patrz łączniki wewnętrzne.

• Iloczyn kartezjański: krzyżuje wszystkie rekordy obu tabel bez żadnego warunku. Technicznie, zwraca zestaw wyników zapytania bez klauzuli WHERE.

Jak na sql i zaawansowanie, istnieją 3-typy złączeń i wszystkie złączenia RDBMS można uzyskać za pomocą tych typów złączeń.

1. INNER-JOIN: łączy (lub łączy) dopasowane wiersze z dwóch tabel. Dopasowanie odbywa się na podstawie wspólnych kolumn tabel i ich operacji porównywania. If equaility based condition then: Equi-JOIN performed, otherwise Non-EQUI-Join.

2. **OUTER-JOIN: * * łączy(lub łączy) dopasowane wiersze z dwóch tabel i niepasowane wiersze z NULL wartości. 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: LEFT OUTER JOIN I RIGHT OUTER JOIN.

   2.1. LEFT Outer JOIN (aka, LEFT-JOIN): zwraca dopasowane wiersze tworzące dwie tabele i niezachowane tylko z lewej tabeli(tzn. pierwszej tabeli).

   2.2. RIGHT Outer JOIN (Alias RIGHT-JOIN): zwraca dopasowane wiersze z dwóch tabel i niepasowane tylko z prawej tabeli.

   2.3. FULL OUTER JOIN (aka OUTER JOIN): zwraca dopasowane i niezrównane wartości z obu tabel.

3. CROSS-JOIN: to połączenie nie łączy / łączy zamiast tego wykonuje produkt kartezjański.

Uwaga: Self-JOIN może być achived przez INNER-JOIN, OUTER-JOIN i CROSS-JOIN w zależności od wymagań, ale tabela musi połączyć się ze sobą.

Więcej informacji:

Przykłady:

1.1: INNER-JOIN: Equi-join implementacja

SELECT  *
FROM Table1 A 
 INNER JOIN Table2 B ON A.<PrimaryKey> =B.<ForeignKey>;

1.2: INNER-JOIN: implementacja Natural-JOIN

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

1.3: INNER-JOIN with NON - eqijoin implementation

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

1.4: INNER-JOIN with SELF-JOIN

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

2.1: zewnętrzne połączenie (pełne zewnętrzne połączenie)

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

2.2: LEFT JOIN

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

2.3: RIGHT JOIN

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

3.1: CROSS JOIN

Select *
 FROM TableA CROSS JOIN TableB;

3.2: CROSS JOIN-Self JOIN

Select *
 FROM Table1 A1 CROSS JOIN Table1 A2;

//lub / /

Select *
 FROM Table1 A1,Table1 A2;

Co ciekawe, większość innych odpowiedzi cierpi na te dwa problemy:]}

Skupiają się tylko na podstawowych formach łączenia]}
• używają diagramów Venna, które są niedokładnym narzędziem do wizualizacji połączeń (są znacznie lepsze dla połączeń) {67]}.

Niedawno napisałem artykuł na ten temat: prawdopodobnie niekompletny, Wyczerpujący przewodnik po wielu różnych sposobach łączenia tabel w SQL , który podsumuję tutaj.

Pierwszy i przede wszystkim: połączenia są produktami kartezjańskimi

To dlatego diagramy Venna wyjaśniają je tak niedokładnie, ponieważ połączenie tworzy iloczyn kartezjański pomiędzy dwiema 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 tabela:

Ź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 napiszemy listę tabel rozdzielonych przecinkami, otrzymamy to samo:

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

INNER JOIN (Theta-JOIN)

An INNER JOIN jest po prostu filtrowanym CROSS JOIN gdzie predykat filtra nazywa się Theta w 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

Zauważ, że słowo kluczowe INNER jest opcjonalne (z wyjątkiem MS Access).

(spójrz na artykuł dla wyniku przykłady )

EQUI JOIN

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 Sakila dla ilustracji, 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

Lub też w niektórych bazach danych:

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

Składnia USING() pozwala określić kolumnę, która musi być obecna po obu stronach łącznika tabele operacji i tworzy predykat równości na tych dwóch kolumnach.

NATURALNE POŁĄCZENIE

Inne odpowiedzi wymieniły ten" typ połączenia " osobno, ale to nie ma sensu. Jest to tylko forma sugar składni dla equi JOIN, która jest szczególnym przypadkiem THETA-JOIN lub INNER JOIN. Funkcja NATURAL JOIN po prostu zbiera wszystkie kolumny, które są wspólne dla obu połączonych tabel i join USING() tych kolumn. Co jest prawie zawsze przydatne, ze względu na przypadkowe dopasowania (jak LAST_UPDATE kolumny w bazie ).

Oto składnia:

SELECT *
FROM actor
NATURAL JOIN film_actor
NATURAL JOIN film

ZEWNĘTRZNE POŁĄCZENIE

Teraz OUTER JOIN jest nieco inna od INNER JOIN, ponieważ tworzy UNION kilka produktów kartezjańskich. Możemy napisać:

-- 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 optymalizowane przez bazy danych).

Podobnie jak INNER, słowo kluczowe {[31] } jest opcjonalne, tutaj.

OUTER JOIN występuje w trzech smakach:

• LEFT [ OUTER ] JOIN: The lewa tabela wyrażenia JOIN jest dodawana do Unii, jak pokazano powyżej.
• RIGHT [ OUTER ] JOIN: do Unii dodaje się prawą tabelę wyrażenia JOIN, Jak pokazano powyżej.
• FULL [ OUTER ] JOIN: obie tabele wyrażenia JOIN są dodawane do Unii, jak pokazano powyżej.

Wszystko to można połączyć ze słowem kluczowym USING() lub z NATURAL (w rzeczywistości miałem prawdziwy przypadek użycia dla NATURAL FULL JOIN ostatnio )

Składnia Alternatywna

Są pewne w 2008 roku firma SQL S. A. uruchomiła w 2009 roku System SQL Server, który obsługiwał system SQL Server.]}

-- 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 tak, nie używaj tej składni. Po prostu wymieniam to tutaj, abyś mógł go rozpoznać po starych postach na blogu / kodzie starszym.

OUTER JOIN

Niewiele osób to wie, ale standard SQL określa partycjonowane OUTER JOIN (i implementuje go Oracle). 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 wynik:

+--------+------------+------------+
| 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 wiersze z podzielonej strony łącznika zostaną wyświetlone w wyniku, niezależnie od tego, czy JOIN pasuje do czegokolwiek z "drugiej strony łącznika". Krótko mówiąc: ma to na celu wypełnienie rzadkich danych w raportach. Bardzo przydatne!

SEMI JOIN

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

Predykat WHERE a.actor_id = fa.actor_id działa jako predykat półzłączeniowy. Jeśli w to nie wierzysz, sprawdź plany realizacji, np. w Oracle. Zobaczysz, że baza danych wykonuje operację SEMI JOIN, a nie predykat EXISTS().

ANTY JOIN

Tutaj czy wszyscy aktorzy nie mają filmów:

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

Niektórzy ludzie (zwłaszcza ludzie MySQL) również piszą anty JOIN w ten sposób:

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

BOCZNE POŁĄCZENIE

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

Znajdzie 5 najlepszych filmów generujących dochód na aktora. Za każdym razem, gdy potrzebujesz zapytania TOP-N-per-something, LATERAL JOIN będzie twoim przyjacielem. Jeśli jesteś osobą SQL Server, wtedy wiesz, że JOIN wpisz 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, być może to oszustwo, ponieważ LATERAL JOIN lub APPLY wyrażenie jest naprawdę "skorelowanym podzbiorem", który tworzy kilka wierszy. Ale jeśli pozwolimy na "skorelowane podzakresy", możemy również mówić o...

MULTISET

Jest to tak naprawdę zaimplementowane tylko przez Oracle i Informix (według mojej wiedzy), ale może być emulowane w PostgreSQL za pomocą tablic i/lub XML oraz w SQL Server za pomocą XML.

MULTISET tworzy skorelowane zapytanie podrzędne i zagnieżdża Wynikowy zestaw wierszy w zapytaniu zewnętrznym. W 2007 roku film został wydany w wersji DVD, a w 2008 roku w wersji DVD.]}

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 łączenia niż tylko "nudne" INNER, OUTER, i CROSS JOIN, które są zwykle wymienione. więcej szczegółów w moim artykule . I proszę przestać używać diagramów Venna do ich zilustrowania.

W SQL server istnieją różne rodzaje złączeń.

1. CROSS JOIN
2. INNER JOIN
3. ZŁĄCZE ZEWNĘTRZNE

Połączenia zewnętrzne są ponownie podzielone na 3 typy

1. Left Join lub Left Outer Join
2. Right Join lub Right Outer Join
3. pełne połączenie lub pełne połączenie zewnętrzne

JOIN lub INNER JOIN

SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
INNER JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

OR

SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

LEFT JOIN lub LEFT OUTER JOIN

SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
LEFT OUTER JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

OR

SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
LEFT JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

RIGHT JOIN lub RIGHT OUTER JOIN

SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
RIGHT OUTER JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

OR

SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
RIGHT JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

Pełne połączenie lub pełne połączenie zewnętrzne

SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
FULL OUTER JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

OR

SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
FULL JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

Stworzyłem ilustrację, która wyjaśnia lepiej niż słowa, Moim zdaniem:

Zamierzam wcisnąć moje pet peeve: użycie słowa kluczowego.

Jeśli obie tabele po obu stronach JOIN mają poprawnie nazwane klucze obce (tj. ta sama nazwa, nie tylko "id"), to można użyć:

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