Pytanie Jaka jest różnica między "INNER JOIN" i "OUTER JOIN"?


Również jak to zrobić LEFT JOIN, RIGHT JOIN i FULL JOIN pasować?


4018
2017-09-01 22:36


pochodzenie


Spośród odpowiedzi i komentarzy oraz ich odnośników poniżej tylko jeden w rzeczywistości wyjaśnia, w jaki sposób diagramy Venna reprezentują operatorów: Obszar przecięcia okręgu reprezentuje zbiór wierszy w A JOIN B. Obszar unikalny dla każdego kręgu reprezentuje zestaw wierszy otrzymanych przez jego wiersze tabeli, które nie uczestniczą w A JOIN B i dodawanie kolumn unikatowych do drugiej tabeli ustawionych na NULL. (I większość daje niejasną fałszywą korespondencję kół do A i B.) - philipxy
Wiele odpowiedzi zostało już dostarczonych, ale nie widziałem wspomnianego samouczka. Jeśli znasz diagramy Venna, jest to WIELKI poradnik: blog.codinghorror.com/a-visual-explanation-of-sql-joins  Dla mnie jest to wystarczająco zwięzłe, by być szybką lekturą, ale wciąż pojmuje całą koncepcję i bardzo dobrze działa we wszystkich przypadkach. Jeśli nie wiesz, czym są diagramy Venna, naucz się ich. Pięć do dziesięciu minut na zrobienie tego i pomoże ci, ilekroć potrzebujesz wizualizować pracę z zestawami i zarządzanie operacjami na zestawach. - DanteTheSmith
@DanteTheSmith Nie, który cierpi z powodu tych samych problemów, co w przypadku diagramów. Zobacz mój komentarz powyżej na pytanie i poniżej tego samego postu na blogu: "Jeff odrzuca swój blog kilka stron w komentarzach". Diagramy Venna pokazują elementy w zestawach. Po prostu spróbuj dokładnie określić, jakie są zestawy i jakie elementy znajdują się w tych diagramach. Zestawy nie są tabele i elementy nie są ich rzędy. Również dowolne dwie tabele mogą być łączone, więc PKs i FK są nieistotne. Wszystko podrobiony. Robisz to, co zrobili tysiące innych - masz niewyraźne wrażenie ty (niesłusznie) założyć ma sens. - philipxy
Czy ktoś mógłby odpowiedzieć na to pytanie w odniesieniu do danych indeksowanych z szeregów czasowych? Połączenia wewnętrzne i zewnętrzne często nie działają w sensie ludzkim / Venna, jeśli chodzi o znaczniki czasu, które różnią się o zaledwie milisekundy. - yeliabsalohcin
@yeliabsalohcin Przeczytaj moje uwagi na tej stronie (i moja odpowiedź). Jeśli nie ma duplikatów, to diagramy Venna ilustrują różnica między połączeniami zewnętrznymi i zewnętrznymi, aby zobrazować połączenia zewnętrzne pod względem wewnętrzne połączenia i odwrotnie jeśli wiesz, co robią. Ale diagramy Venna nie rób tego ilustrują, co zwraca zewnętrzne sprzężenie lub wewnętrzne sprzężenie pod względem wkładu z wyjątkiem sytuacji, gdy dane wejściowe nie mają zduplikowanych wierszy i mają te same kolumny. (Jeśli masz inne pytanie, opublikuj je jako pytanie. Aplikacje nie mają znaczenia. "Dopasowywanie" ma znaczenie, zależne od typów i trybów danych.) - philipxy


Odpowiedzi:


Zakładając, że dołączasz do kolumn bez duplikatów, co jest bardzo częstym przypadkiem:

  • Wewnętrzne sprzężenie A i B daje wynik przecięcia B, tj. Wewnętrznej części a Diagram Venna skrzyżowanie.

  • Zewnętrzne połączenie A i B daje wyniki A związku B, tj. Zewnętrznych części związku diagramu Venna.

Przykłady

Załóżmy, że masz dwie tabele z jedną kolumną i dane w następujący sposób:

A    B
-    -
1    3
2    4
3    5
4    6

Zauważ, że (1,2) są unikalne dla A, (3,4) są wspólne, a (5,6) są unikalne dla B.

Złączenie wewnętrzne

Sprzężenie wewnętrzne za pomocą jednego z równoważnych zapytań podaje przecięcie dwóch tabel, tj. Dwóch wspólnych wierszy.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Lewe sprzężenie zewnętrzne

Lewe sprzężenie zewnętrzne daje wszystkie wiersze w A, plus wszystkie wspólne wiersze w B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Prawe sprzężenie zewnętrzne

Prawe sprzężenie zewnętrzne da wszystkie wiersze w B plus dowolne wspólne wiersze w A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Pełne sprzężenie zewnętrzne

Pełne sprzężenie zewnętrzne daje połączenie A i B, tj. Wszystkie wiersze w A i wszystkie wiersze w B. Jeśli coś w A nie ma odpowiedniego odniesienia w B, to część B jest pusta, a wadą versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

5428
2017-09-01 22:59



Byłoby dobrze, aby wzmocnić przykład, dodając kolejny wiersz w tabeli B o wartości 4. To pokaże, że wewnętrzne sprzężenia nie muszą być równe zero wierszy. - softveda
Doskonałe wyjaśnienie, jednak to stwierdzenie: Zewnętrzne połączenie A i B daje wyniki A związku B, tj. Zewnętrznych części związku diagramu venna. nie jest sformułowane dokładnie. Łączenie zewnętrzne daje wyniki A przecinają się z jednym z następujących: wszystkie A (lewe połączenie), wszystkie B (prawe połączenie) lub wszystkie A i wszystkie B (pełne sprzężenie). Tylko ten ostatni scenariusz jest tak naprawdę Związkiem B. Nadal dobrze napisane wyjaśnienie. - Thomas
Pro odpowiedź! Lubiłem to. Btw: Wciąż nie mogę znaleźć różnicy pomiędzy LEFT JOIN i LEFT OUTER JOIN, itp. Jeśli to nie jest część pytania, mogę napisać nowy :) - YvesR
Czy mam rację, że FULL JOIN jest aliasem FULL OUTER JOIN, a LEFT JOIN jest aliasem LEFT OUTER JOIN? - Damian
@ Amme, dzięki. Join nie gwarantuje zamówienia, musisz dodać klauzulę ORDER BY. - Mark Harrison


Można również rozważyć następujący schemat dla różnych typów łączenia;

visual explanation of joins

Źródło: Wizualne reprezentowanie-SQL-Joins szczegółowo wyjaśnione przez C.L. Moffatt


2443
2018-05-16 23:03



Uwaga: W MySQL nie ma FULL OUTER JOIN. stackoverflow.com/questions/12473210/... - Michael Ozeryansky
Myślę, że ten diagram zakłada, że ​​nie ma duplikatu Key, co znaczy Key jest unikalny. Gdyby Key nie był wyjątkowy, myślę, że wynik byłby krzyżowy, a liczba zwrotów byłaby znacznie wyższa niż wielkość A. - AlikElzin-kilaka
W przykładzie FULL OUTER JOIN z wyłączeniem części przecięcia, nie użyje "A.Key IS NULL" wyklucza wszystkie wiersze z A, ponieważ jeśli klucz jest NULL, to z definicji ten wiersz nie istnieje? Nie widzę, jak ten mechanizm osiąga wyniki dla pokazanego diagramu VENN. - Kelly S. French
@ KellyS.French Zobacz moje komentarze na pytanie i inne odpowiedzi wyjaśniające, jak czytać diagramy Venna. Strefy nie obejmują wierszy z tabel A i B. Obejmują one pewne wiersze utworzone z parowania wiersza od A i wiersza od B. Które również nie są nawet wierszami wyjścia instrukcji SELECT. Odpowiedzi na diagram Venna są słabe. - philipxy
A reprezentuje wszystko potencjał wiersze pochodzące z tabeli A, B oznacza wszystkie potencjał wiersze pochodzące z tableB. Czerwone strefy na znaczniku diagramu, który z tych wierszy zostanie uwzględniony w zestawie wyników. Zauważ, że może być (i często jest) wiele, wiele więcej potencjał wiersze pochodzące z tabeli niż faktycznie wiersze w tej tabeli. A to dlatego, że każdy wiersz w tabeli A będzie potencjalnie Występuje raz dla każdego wiersza w tabeli B, która ją odwołuje. Nazywa się to produkt kartezjański. - Stijn de Witt


polecam Artykuł na blogu Jeffa. Najlepszy opis jaki kiedykolwiek widziałem, plus jest wizualizacja, np .:

Połączenie wewnętrzne:

enter image description here

Pełne połączenie zewnętrzne:

enter image description here


591
2017-08-30 11:52



Ten schemat jest nieco mylący dla koncepcji. Przeczytaj także komentarze w poście. - softveda
@ ya23: co rozumiesz przez pełne sprzężenie zewnętrzne? - ursitesion
Tylko że Jeff wyrzeka się jego blog kilka stron w komentarzach: "Komentatorzy, którzy wskazują, że diagramy się załamują w przypadku wielokrotnego lub podwójnego wyniku, mają absolutną rację, tak naprawdę myślałem o dołączeniu do klucza głównego, który wydaje się być unikalny przez Definicja, chociaż przykłady nie są wyrażane w ten sposób, podobnie jak produkt kartezjański lub krzyżowy, wszystko, co powoduje więcej wierszy niż początkowo, całkowicie łamie całą koncepcję diagramu venna. "Miej to na uwadze." - philipxy
@philipxy yes Analogia diagram Venna działa tylko dla klucza podstawowego po zignorowaniu NULL - często nie jest to określone. Diagramy Venna nie pomagają wyjaśnić, w jaki sposób pozostałe kolumny w wynikowym zestawie są wypełnione wartościami NULL. - Galax
@Galax Niestety, czy połączenia są na klawiszach (nie mówiąc już o kluczach podstawowych), nie pomagają w tworzeniu diagramów. (Chociaż cytowałem Jeffa mówiącego, że on tak uważa). (Nie rozumiem twoich "prac dla klucza podstawowego po zignorowaniu NULL" lub "pozostałe kolumny w wynikowym zbiorze są wypełnione NULL" .Wieszę więcej lub mniej sortować o rzeczach, które próbujesz powiedzieć, ale korespondencja, ale ta w moim komentarzu do pytania złożony i tak jest jasny wyjaśnienie. Chociaż chciałbym powitać jednego. Dokładnie co są zgrupowane w jaki sposób przez koła? - philipxy


Diagramy Venna tak naprawdę nie robią tego dla mnie.

Nie pokazują one na przykład żadnego rozróżnienia między łączeniem krzyżowym i łączeniem wewnętrznym, lub bardziej ogólnie pokazują rozróżnienie między różnymi typami orzeczników łączenia lub zapewniają ramy dla wnioskowania o tym, jak będą działać.

Nie ma substytutu dla zrozumienia logicznego przetwarzania i jest to względnie łatwe do uchwycenia.

  1. Wyobraź sobie połączenie krzyżowe.
  2. Oceń on do wszystkich wierszy z kroku 1, zachowując te, do których ocenia orzeczenie true
  3. (Tylko w przypadku połączeń zewnętrznych) dodaj z powrotem wszystkie zewnętrzne rzędy, które zostały utracone w kroku 2.

(Uwaga: w praktyce optymalizator zapytań może znaleźć bardziej wydajne sposoby wykonywania zapytania niż powyższy opis czysto logiczny, ale wynik końcowy musi być taki sam)

Zacznę od animowanej wersji a pełne sprzężenie zewnętrzne. Dalsze wyjaśnienia poniżej.

enter image description here


Wyjaśnienie

Tabele źródłowe

enter link description here

Najpierw zacznij od a CROSS JOIN (AKA Cartesian Product). To nie ma ON i po prostu zwraca każdą kombinację wierszy z dwóch tabel.

WYBIERZ A.Kolor, B.Kolor od połączenia KRZYŻ B

enter link description here

Połączenia wewnętrzne i zewnętrzne mają predykat klauzuli "ON".

  • Wewnętrzne dołączenie. Oceń warunek w klauzuli "ON" dla wszystkich wierszy w wyniku łączenia krzyżowego. Jeśli true, zwracaj połączony wiersz. W przeciwnym razie odrzuć to.
  • Left Outer Join. Tak samo, jak w przypadku łączenia wewnętrznego, a następnie dla dowolnych wierszy w lewej tabeli, które nie pasują do niczego, wyprowadzają je z wartościami NULL dla kolumn z prawej tabeli.
  • Prawe zewnętrzne połączenie. Tak samo jak w przypadku łączenia wewnętrznego, a następnie w przypadku wszystkich wierszy w prawej tabeli, które nie pasują do niczego, wyprowadzane są te wartości NULL dla lewej kolumny tabeli.
  • Pełne połączenie zewnętrzne. Podobnie jak w przypadku łączenia wewnętrznego, zachowaj lewe niezrównane rzędy, tak jak w przypadku lewych łączeń zewnętrznych i prawych niepasujących wierszy, jak w przypadku prawostronnego łączenia zewnętrznego.

Kilka przykładów

WYBIERZ A.Kolor, B.Kolor od WEWNĘTRZNEGO DOŁĄCZENIA B DO A. Kolor = B.Kolor

Powyższe jest klasycznym equi join.

Inner Join

Wersja animowana

enter image description here

WYBIERZ A.Kolor, B.Kolor od WEWNĘTRZNEGO DOŁĄCZENIA B DO A. Kolor NIE IN ("Zielony", "Niebieski")

Warunek łączenia wewnętrznego niekoniecznie musi być warunkiem równości i nie musi odwoływać się do kolumn z obu (lub nawet z) tabel. Ewaluacja A.Colour NOT IN ('Green','Blue') w każdym rzędzie krzyża sprzężenie zwrotne.

inner 2

WYBIERZ A.Kolor, B.Kolor z ODŁĄCZENIA WE B 1 1

Warunek łączenia jest prawdziwy dla wszystkich wierszy w wyniku łączenia krzyżowego, więc jest to to samo, co połączenie krzyżowe. Nie powtórzę ponownie obrazu 16 wierszy.

WYBIERZ A.Kolor, B.Kolor od LEWEGO ZEWNĘTRZNEGO POŁĄCZENIA B WŁ. A.Kolor = B.Kolor

Łączenia zewnętrzne są logicznie obliczane w taki sam sposób, jak połączenia wewnętrzne, z wyjątkiem tego, że jeśli wiersz z lewej tabeli (dla łączenia lewego) nie łączy się z żadnymi wierszami z tabeli po prawej stronie, jest zachowywany w wyniku z NULL wartości dla kolumn po prawej stronie.

LOJ

WYBIERZ A.Kolor, B.Kolor od LEWEGO ZEWNĘTRZNEGO POŁĄCZENIA B WŁ. A.Kolor = B.Kolor WHERE B.Kolor jest NULL

Ogranicza to po prostu poprzedni wynik tylko do zwracania wierszy gdzie B.Colour IS NULL. W tym szczególnym przypadku będą to wiersze, które zostały zachowane, ponieważ nie pasowały do ​​prawej tabeli, a zapytanie zwraca pojedynczy czerwony wiersz nie pasujący do tabeli B. Jest to znane jako anti semi join.

Ważne jest, aby wybrać kolumnę dla IS NULL test, który nie jest zerowy lub dla którego warunek sprzężenia gwarantuje, że dowolny NULL wartości zostaną wykluczone, aby wzorzec działał poprawnie, i unikaj tylko przywracania wierszy, które mają wartość NULL wartość dla tej kolumny, oprócz nie dopasowanych wierszy.

loj is null

WYBIERZ A.Kolor, B.Kolor z PRAWEJ STRONY ZEWNĘTRZNEJ WŁĄCZONY A. Kolor = B.Kolor

Prawe sprzężenia zewnętrzne działają podobnie do lewych złączeń zewnętrznych, z wyjątkiem zachowania niepasujących wierszy z prawego stołu i zerowej rozciągnięcia lewej kolumny.

ROJ

WYBIERZ A.Kolor, B.Kolor z PEŁNEGO ZEWNĘTRZNEGO DOŁĄCZENIA B ON A.Kolor = B.Kolor

Pełne sprzężenia zewnętrzne łączą zachowanie lewych i prawych połączeń i zachowują niepasujące wiersze z lewej i prawej tabeli.

FOJ

WYBIERZ A.Kolor, B.Kolor z PEŁNEGO ZEWNĘTRZNEGO DOŁĄCZENIA B NA 1 = 0

Żadne wiersze w połączeniu krzyżowym nie pasują do 1=0 orzec. Wszystkie wiersze z obu stron są zachowywane przy użyciu normalnych reguł łączenia zewnętrznego z wartością NULL w kolumnach tabeli po drugiej stronie.

FOJ 2

WYBIERZ WĘGIEL (A.Kolor, B.Kolor) JAKO KOLOR Z PEŁNEGO ZEWNĘTRZNEGO DOŁĄCZ DO B ON 1 = 0

Z niewielką poprawką do poprzedniego zapytania można symulować a UNION ALL z dwóch tabel.

UNION ALL

WYBIERZ A.Kolor, B.Kolor od LEWEGO ZEWNĘTRZNEGO POŁĄCZENIA B WŁ. A.Kolor = B.Kolor WHERE B.Kolor = "Zielony"

Zauważ, że WHERE (jeśli jest) logicznie działa po złączeniu. Jeden wspólny błąd polega na wykonaniu lewego sprzężenia zewnętrznego, a następnie dołączeniu klauzuli WHERE z warunkiem na prawej tabeli, który kończy się wyłączeniem niepasujących wierszy. Powyższe kończy się wykonywanie zewnętrznego łączenia ...

LOJ

... A następnie uruchamia się klauzula "Gdzie". NULL= 'Green' nie jest prawdziwe, więc rząd zachowany przez połączenie zewnętrzne zostaje odrzucony (razem z niebieskim), co skutecznie przekształca połączenie z powrotem na wewnętrzne.

LOJtoInner 

Jeśli intencją było włączenie tylko wierszy z B, gdzie kolor jest zielony i wszystkich wierszy od A, bez względu na poprawną składnię

WYBIERZ A.Kolor, B.Kolor od LEWEGO ZEWNĘTRZNA DOŁĄCZ DO B ON A. Kolor = B.Kolor I B.Kolor = "Zielony"

enter image description here

SQL Fiddle

Zobacz te przykłady uruchom na żywo na stronie SQLFiddle.com.


529
2017-12-13 11:58



Powiem, że chociaż to nie działa dla mnie tak dobrze, jak diagramy Venna, doceniam to, że ludzie się różnią i uczą inaczej, a to jest bardzo dobrze przedstawione wyjaśnienie, jakiego nie widziałem wcześniej, więc wspieram @ypercube w przyznawanie punktów bonusowych. Również dobra praca wyjaśniająca różnicę w umieszczaniu dodatkowych warunków w klauzuli JOIN względem klauzuli WHERE. Kudos dla ciebie, Martin Smith. - Old Pro
@OldPro Diagramy Venna są w porządku tak daleko, jak sądzę, ale nie mówią nic o tym, jak przedstawiać sprzężenie krzyżowe, ani też rozróżniać jednego rodzaju predykatu łączenia, takiego jak equi join od innego. Umysłowy model oceniania predykatu łączenia w każdym wierszu łączenia krzyżowego powoduje, że dodajemy z powrotem niedopasowane wiersze, jeśli łączenie zewnętrzne i ostatecznie oceniamy, gdzie działa lepiej dla mnie. - Martin Smith
Diagramy Venna są dobre do reprezentowania związków i przecięć i różnic, ale nie do łączenia. Mają one niewielką wartość edukacyjną dla bardzo prostych połączeń, tj. Łączeń, w których warunek łączenia znajduje się na unikalnych kolumnach. - ypercubeᵀᴹ
@Arth - Nie, jesteś w błędzie. SQL Fiddle sqlfiddle.com/#!3/9eecb7db59d16c80417c72d1/5155 jest to coś, czego diagramy Venna nie mogą zilustrować. - Martin Smith
@MartinSmith Wow, zgadzam się, jestem całkowicie w błędzie! Zbyt często używane do pracy z jednym z wielu ... dzięki za poprawkę. - Arth


Poniższy tekst został zaczerpnięty z artykułu "MySQL - LEFT JOIN i RIGHT JOIN, INNER DOŁĄCZ DOŁĄCZ DO ZEWNĄTRZ"Graham Ellis na swoim blogu Horse's Mouth.

W bazie danych, takiej jak MySQL, dane są podzielone na kilka tabel, które są następnie połączone (Joined) razem przez JOIN w SELECT polecenia do odczytu rekordów z wielu tabel. Przeczytaj ten przykład, aby zobaczyć, jak to działa.

Najpierw kilka przykładowych danych:

people
    mysql> select * from people;
    +------------+--------------+------+
    | name       | phone        | pid  |
    +------------+--------------+------+
    | Mr Brown   | 01225 708225 |    1 |
    | Miss Smith | 01225 899360 |    2 |
    | Mr Pullen  | 01380 724040 |    3 |
    +------------+--------------+------+
    3 rows in set (0.00 sec)

property
    mysql> select * from property;
    +------+------+----------------------+
    | pid  | spid | selling              |
    +------+------+----------------------+
    |    1 |    1 | Old House Farm       |
    |    3 |    2 | The Willows          |
    |    3 |    3 | Tall Trees           |
    |    3 |    4 | The Melksham Florist |
    |    4 |    5 | Dun Roamin           |
    +------+------+----------------------+
    5 rows in set (0.00 sec)

REGULARNE DOŁĄCZENIE

Jeśli wykonamy zwykłe JOIN (bez żadnego słowa kluczowego INNER, OUTER, LEFT lub RIGHT), to otrzymamy wszystkie rekordy pasujące w odpowiedni sposób do dwóch tabel, a rekordy w obu przychodzących tabelach, które nie pasują, nie są zgłaszane :

mysql> select name, phone, selling 
from people join property 
on people.pid = property.pid;
+-----------+--------------+----------------------+
| name      | phone        | selling              |
+-----------+--------------+----------------------+
| Mr Brown  | 01225 708225 | Old House Farm       |
| Mr Pullen | 01380 724040 | The Willows          |
| Mr Pullen | 01380 724040 | Tall Trees           |
| Mr Pullen | 01380 724040 | The Melksham Florist |
+-----------+--------------+----------------------+
4 rows in set (0.01 sec)

LEFT DOŁĄCZ

Jeśli wykonamy LEFT JOIN, otrzymamy wszystkie rekordy, które pasują w taki sam sposób, a IND DODATEK otrzymamy dodatkowy rekord dla każdego nieosiągalnego rekordu w lewej tabeli sprzężenia - zapewniając w ten sposób (w tym przykładzie), że każdy OSOB otrzymuje wzmiankę :

   mysql> select name, phone, selling 
    from people left join property 
    on people.pid = property.pid; 
    +------------+--------------+----------------------+
    | name       | phone        | selling              |
    +------------+--------------+----------------------+
    | Mr Brown   | 01225 708225 | Old House Farm       |
    | Miss Smith | 01225 899360 | NULL <<-- unmatch    |
    | Mr Pullen  | 01380 724040 | The Willows          |
    | Mr Pullen  | 01380 724040 | Tall Trees           |
    | Mr Pullen  | 01380 724040 | The Melksham Florist |
    +------------+--------------+----------------------+
    5 rows in set (0.00 sec)

DOŁĄCZ DO PRAWA

Jeśli wykonamy PRAWE POŁĄCZENIE, otrzymamy wszystkie rekordy, które pasują i IN ADDITION, dodatkowy rekord dla każdego nieosiągalnego rekordu w prawej tabeli łączenia - w moim przykładzie oznacza to, że każda właściwość dostaje wzmiankę, nawet jeśli nie podać dane sprzedawcy:

mysql> select name, phone, selling 
from people right join property 
on people.pid = property.pid;
+-----------+--------------+----------------------+
| name      | phone        | selling              |
+-----------+--------------+----------------------+
| Mr Brown  | 01225 708225 | Old House Farm       |
| Mr Pullen | 01380 724040 | The Willows          |
| Mr Pullen | 01380 724040 | Tall Trees           |
| Mr Pullen | 01380 724040 | The Melksham Florist |
| NULL      | NULL         | Dun Roamin           |
+-----------+--------------+----------------------+
5 rows in set (0.00 sec)

INNER JOIN dokonuje pełnego sprzężenia, podobnie jak pierwszy przykład, a słowo OUTER może zostać dodane po słowie LEFT lub RIGHT w dwóch ostatnich przykładach - zapewnia zgodność z ODBC i nie dodaje dodatkowych możliwości.


291
2018-02-14 05:53



REGULAR JOIN i INNER JOIN to to samo. Co Graham Ellis chciał powiedzieć odrobinę REGULAR jest to, że INNER JOIN jest "domyślnym" JOIN, gdy nie ma żadnego z nich LEFT lub RIGHT słowa kluczowe zostały określone. Jeśli czytasz cały wpis, w końcu On mówi: "INNER JOIN wykonuje pełne sprzężenie, tak jak pierwszy przykład, i słowo OUTER może być dodane po słowie LEWO lub PRAWO w dwóch ostatnich przykładach - jest przewidziane dla ODBC kompatybilność i nie dodaje dodatkowych możliwości. " - vegatripy
Dla tych, którzy są zdezorientowani pid i spid, pid oznacza "id osoby", oraz spid oznacza "identyfikator właściwości sprzedaży". - Andrew Grimm
Co jeśli chcemy tylko wybrać Mr.BROWN, Miss SMITH I Mr Pullen? - Fortune
@Fortune Select (wiersze zawierające name równy jeden z nich z czego? W jaki sposób jest to związane z różnicą między połączeniami wewnętrznymi i zewnętrznymi? - philipxy
wiele odpowiedzi nie myśli o jednej - sytuacji wielości, a to zwiedzie kogoś, twoja odpowiedź jest lepsza! - rocket1037


Wewnętrzne dołączenie

Odzyskaj tylko dopasowane wiersze, czyli A intersect B.

Enter image description here

SELECT *
FROM dbo.Students S
INNER JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Left Outer Join

Wybierz wszystkie rekordy z pierwszej tabeli i dowolne rekordy w drugiej tabela pasująca do połączonych kluczy.

Enter image description here

SELECT *
FROM dbo.Students S
LEFT JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Pełne połączenie zewnętrzne

Wybierz wszystkie rekordy z drugiej tabeli i wszystkie rekordy w pierwszej tabela pasująca do połączonych kluczy.

Enter image description here

SELECT *
FROM dbo.Students S
FULL JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Referencje


114
2018-01-27 12:16



Jak nazywa się narzędzie? Uważam, że jest to interesujące, ponieważ pokazuje liczbę rzędów i diagramów venn - Grijesh Chauhan
@GrijeshChauhan Datamartist :) - Tushar Gupta
@Trushar :( to nie jest dla systemu Linux .. - Grijesh Chauhan
@GrijeshChauhan Tak, ale możesz spróbować uruchomić go przy użyciu wino . - Tushar Gupta
Ohh! tak, użyłem SQLYog używając wina ... jest też PlayOnLinux - Grijesh Chauhan


Dołącza są używane do łączenia danych z dwóch tabel, a wynikiem jest nowa, tymczasowa tabela. Połączenia są wykonywane na podstawie czegoś, co nazywa się predykatem, który określa warunek do użycia w celu wykonania sprzężenia. Różnica między połączeniem wewnętrznym a złączem zewnętrznym polega na tym, że sprzężenie wewnętrzne zwróci tylko wiersze, które faktycznie są zgodne na podstawie predykatu łączenia. Przyjrzyjmy się tabeli Employee and Location:

enter image description here

Wewnętrzne dołączenie: - Łączenie wewnętrzne tworzy nową tabelę wyników, łącząc wartości kolumn dwóch tabel (Pracownik i Lokalizacja) na podstawie predykatu łączenia. Kwerenda porównuje każdy wiersz Pracownik z każdym rzędem Lokalizacja aby znaleźć wszystkie pary wierszy, które spełniają predykat łączenia. Gdy predykcja łączenia jest spełniona przez dopasowanie wartości innych niż NULL, wartości kolumn dla każdej dopasowanej pary wierszy Pracownik i Lokalizacja są połączone w wiersz wyników. Oto, jak będzie wyglądał SQL dla wewnętrznego sprzężenia:

select  * from employee inner join location on employee.empID = location.empID
OR
select  * from employee, location where employee.empID = location.empID

Oto, jak będzie wyglądał wynik działania SQL: enter image description here enter image description here

Dołączenie zewnętrzne: - Zewnętrzne sprzężenie nie wymaga, aby każdy rekord w dwóch połączonych tabelach był zgodny z rekordem. Połączona tabela zachowuje każdy rekord - nawet jeśli nie ma innego pasującego rekordu. Połączenia zewnętrzne dzielą się dalej na lewe połączenie zewnętrzne i prawe połączenie zewnętrzne, w zależności od tego, które rzędy stołu są zachowane (w lewo lub w prawo).

Left Outer Join: - Wynik lewego zewnętrznego połączenia (lub po prostu lewostronnego) dla tabel Pracownik i Lokalizacja zawsze zawiera wszystkie zapisy "lewego" stołu (Pracownik), nawet jeśli warunek łączenia nie znajdzie żadnego pasującego rekordu w "poprawnym" tabeli (Lokalizacja). Oto, jak wyglądałby kod SQL dla lewego sprzężenia zewnętrznego, korzystając z tabel powyżej:

select  * from employee left outer join location on employee.empID = location.empID;
//Use of outer keyword is optional

Oto, jak będzie wyglądał wynik tego SQL: enter image description here enter image description here

Right Outer Join: - Prawe połączenie zewnętrzne (lub prawe połączenie) bardzo przypomina lewe sprzężenie zewnętrzne, z wyjątkiem odwrotnego traktowania tabel. Każdy wiersz z "prawego" stołu (Lokalizacja) pojawi się w połączonej tabeli co najmniej raz. Jeśli nie pasuje wiersz z "lewej" tabeli (Pracownik) istnieje, NULL pojawi się w kolumnach z Pracownik dla tych rekordów, które nie pasują do siebie Lokalizacja. Oto jak wygląda SQL:

select * from employee right outer join location  on employee.empID = location.empID;
//Use of outer keyword is optional

Korzystając z powyższych tabel, możemy pokazać, jak wyglądałby zestaw wyników prawego łączenia zewnętrznego:

enter image description hereenter image description here

Pełne połączenia zewnętrzne: - Pełne łączenie zewnętrzne lub pełne łączenie ma na celu zatrzymanie niepasujących informacji poprzez uwzględnienie niepasujących wierszy w wynikach łączenia, użycie pełnego sprzężenia zewnętrznego. Obejmuje wszystkie wiersze z obu tabel, niezależnie od tego, czy druga tabela ma pasującą wartość. enter image description here

Źródło obrazu

Podręcznik referencyjny MySQL 8.0 - Dołącz do składni

Operacje Oracle Join


108
2017-12-18 06:54





W prostych słowach:

Na sprzężenie wewnętrzne pobrać tylko dopasowane wiersze.

Podczas gdy sprzężenie zewnętrzne pobierz dopasowane wiersze z jednej tabeli i wszystkich wierszy w innej tabeli .... wynik zależy od tego, z którego korzystasz:

  • Lewo: Dopasowane wiersze w prawej tabeli i wszystkie wiersze w lewej tabeli

  • Dobrze: Dopasowane wiersze w lewej tabeli i wszystkie wiersze w prawej tabeli lub

  • Pełny: Wszystkie wiersze we wszystkich tabelach. Nie ma znaczenia, czy jest mecz, czy nie


101
2018-01-12 11:07



@Nomen Nie ta odpowiedź na to pytanie, ale INNER JOIN to przecięcie, a FULL OUTER JOIN to odpowiadająca UNIONA gdyby zestawy lewy i prawy / okręgi zawierają wiersze (odpowiednio) łączenia LEWEGO i PRAWEGO. PS Ta odpowiedź nie jest jasna co do wierszy w danych wejściowych i wyjściowych. Dezorientuje "w lewym / prawym stole" z "ma lewą / prawą część w lewo / prawo" i używa "dopasowanego wiersza" w stosunku do "wszystkich", aby oznaczać wiersz rozszerzony o wiersz z innej tabeli vs o wartość zerową. - philipxy


Łączenie wewnętrzne pokazuje tylko wiersze, jeśli istnieje pasujący rekord po drugiej (prawej) stronie sprzężenia.

A (lewe) sprzężenie zewnętrzne pokazuje rzędy dla każdego rekordu po lewej stronie, nawet jeśli nie ma pasujących wierszy po drugiej (prawej) stronie łączenia. Jeśli nie ma pasujących wierszy, kolumny dla drugiej (prawej) strony będą pokazywać wartości NULL.


92
2017-09-01 22:38





Wewnętrzne sprzężenia wymagają, aby rekord z powiązanym identyfikatorem istniał w połączonej tabeli.

Zewnętrzne sprzężenia zwrócą rekordy po lewej stronie, nawet jeśli nic nie istnieje po prawej stronie.

Na przykład masz tabele zamówień i zamówień. Są powiązane przez "OrderID".

Święcenia

  • OrderID
  • CustomerName

Szczegóły zamówienia

  • OrderDetailID
  • OrderID
  • Nazwa produktu
  • Ilość
  • Cena £

Prośba

SELECT Orders.OrderID, Orders.CustomerName FROM Orders 
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID

zwróci tylko zamówienia, które również mają coś w tabeli OrderDetails.

Jeśli zmienisz go na OUTER LEFT JOIN

SELECT Orders.OrderID, Orders.CustomerName FROM Orders 
LEFT JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID

to zwróci rekordy z tabeli Orders, nawet jeśli nie mają rekordów OrderDetails.

Możesz użyć tego do znalezienia Zleceń, które nie mają żadnych Zamówień zamówienia, wskazujących możliwe osierocone zamówienie, dodając klauzulę, na przykład WHERE OrderDetails.OrderID IS NULL.


67
2017-09-01 22:47



Doceniam prosty, ale realistyczny przykład. Zmieniłem prośbę jak SELECT c.id, c.status, cd.name, c.parent_id, cd.description, c.image FROM categories c, categories_description cd WHERE c.id = cd.categories_id AND c.status = 1 AND cd.language_id = 2 ORDER BY c.parent_id ASC do SELECT c.id, c.status, cd.name, c.parent_id, cd.description, c.image FROM categories c INNER JOIN categories_description cd ON c.id = cd.categories_id WHERE c.status = 1 AND cd.language_id = 2 ORDER BY c.parent_id ASC (MySQL) z sukcesem. Nie byłem pewien co do dodatkowych warunków, dobrze się mieszają ... - PhiLho


W prostych słowach:

Złączenie wewnętrzne -> Wykonuj TYLKO typowe rekordy z tabel nadrzędnych i podrzędnych GDZIE klucz podstawowy tabeli nadrzędnej jest zgodny z kluczem obcym w tabeli podrzędnej.

Lewe dołączenie ->

pseudo kod

1.Take All records from left Table
2.for(each record in right table,) {
    if(Records from left & right table matching on primary & foreign key){
       use their values as it is as result of join at the right side for 2nd table.
    } else {
       put value NULL values in that particular record as result of join at the right side for 2nd table.
    }
  }

Prawo dołącz : Dokładnie przeciwne do lewego połączenia. Umieść nazwę tabeli w LEWYCH DOŁĄCZACH po prawej stronie w prawym złączeniu, otrzymasz taki sam wynik jak LEWY DOŁĄCZ.

Łącze zewnętrzne : Pokaż wszystkie rekordy w obu tabelach No matter what. Jeśli rekordy w lewej tabeli nie pasują do prawej tabeli w oparciu o klucz podstawowy, klucz Forieign, należy użyć wartości NULL jako wyniku sprzężenia.

Przykład:

Example

Załóżmy teraz 2 tabele

1.employees , 2.phone_numbers_employees

employees : id , name 

phone_numbers_employees : id , phone_num , emp_id   

Tutaj tabela pracowników to tabela główna, numer_dzwonienia_działań jest tabelą podrzędną (zawiera emp_id jako klucz obcy, który się łączy employee.id więc jego stolik dla dzieci.)

Wewnętrzne sprzężenia 

Weź zapisy z 2 tabel TYLKO JEŚLI Główny klucz tabeli pracowników (jej id) jest zgodny z kluczem obcym tabeli podrzędnej phone_numbers_employees (emp_id).

Tak więc zapytanie będzie:

SELECT e.id , e.name , p.phone_num FROM employees AS e INNER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Tutaj bierzemy tylko pasujące wiersze na kluczu podstawowym = klucz obcy, jak wyjaśniono powyżej. Poniższe niepasujące wiersze klucza podstawowego = klucz obcy są pomijane w wyniku połączenia.

Lewe połączenia :

Lewe połączenie zachowuje wszystkie wiersze lewej tabeli, niezależnie od tego, czy istnieje wiersz pasujący do prawego stołu.

SELECT e.id , e.name , p.phone_num FROM employees AS e LEFT JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Zewnętrzne sprzężenia :

SELECT e.id , e.name , p.phone_num FROM employees AS e OUTER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Diagramatycznie wygląda to tak:

Diagram


58
2018-02-19 04:50



Wynik nie ma nic do (zrobienia per se) z kluczami pierwotnymi / unikalnymi / kandydującymi i kluczami obcymi. Ubrania mogą i powinny być opisane bez odniesienia się do nich. Łączenie krzyżowe jest obliczane, a następnie wiersze niespełniające warunku włączenia są odfiltrowywane; dodatkowo dla wierszy złączy zewnętrznych wiersze filtrowane / niedopasowane są rozszerzane o wartości NULL (na LEWO / PRAWO / PEŁNE i dołączone). - philipxy