Różnica między INNER JOIN a LEFT SEMI JOIN


85

Jaka jest różnica między INNER JOINi LEFT SEMI JOIN?

W poniższym scenariuszu, dlaczego otrzymuję dwa różne wyniki?

Zestaw INNER JOINwyników jest dużo większy. Czy ktoś może wyjaśnić? Próbuję uzyskać nazwy, table_1które pojawiają się tylko w table_2.

SELECT name
FROM table_1 a
    INNER JOIN table_2 b ON a.name=b.name

SELECT name
FROM table_1 a
    LEFT SEMI JOIN table_2 b ON (a.name=b.name)

2
Wewnętrzne połączenie osiągnie twój cel. Nigdy nie słyszałem o półłączeniu, dopóki nie zobaczyłem tego pytania.
Dan Bracuk,

left semi joinNależy wracać więcej wierszy niż inner join.
Gordon Linoff,

1
inner joinPowróci dane tylko wtedy, gdy istnieje zgodność między obu tabelach. left joinZwróci dane z pierwszej tabeli niezależnie od tego czy rekord zostanie znaleziony w drugiej tabeli.
j03z

11
@GordonLinoff niekoniecznie, a LEFT SEMI JOINzwróci tylko jeden wiersz od lewej, nawet jeśli po prawej jest wiele dopasowań. An INNER JOINzwróci wiele wierszy, jeśli po prawej stronie znajduje się wiele dopasowań.
D Stanley,

1
@ j03z, które nie mogą być poprawne. Jeśli celem lewego łączenia hemi jest 1) zwrócenie tylko informacji z lewej tabeli (jak powiedzieli inni) i 2) zwrócenie wierszy z lewej tabeli niezależnie od dopasowania (jak myślę, że mówisz), to jest to tylko oryginalna lewa tabela - do tego nie jest potrzebne łączenie. Myślę, że inni muszą mieć rację, że lewe łączenie hemi 1) zwraca tylko kolumny z lewej tabeli, 2) zwraca tylko wiersze, które mają dopasowanie w prawej tabeli, a 3) zwróci pojedynczy wiersz od lewej dla jednego lub więcej dopasowań.
Carl G

Odpowiedzi:


126

INNER JOINMoże zwrócić dane z kolumn z obu tabel, a może powielać wartości rejestrów po obu stronach mają więcej niż jeden mecz. A LEFT SEMI JOINmoże zwracać tylko kolumny z tabeli po lewej stronie i zwraca jeden z każdego rekordu z tabeli po lewej stronie, w której znajduje się jedno lub więcej dopasowań w tabeli po prawej stronie (niezależnie od liczby dopasowań). Jest to odpowiednik (w standardowym SQL):

SELECT name
FROM table_1 a
WHERE EXISTS(
    SELECT * FROM table_2 b WHERE (a.name=b.name))

Jeśli w prawej kolumnie znajduje się wiele pasujących wierszy, polecenie a INNER JOINzwróci jeden wiersz na każde dopasowanie w prawej tabeli, a a LEFT SEMI JOINzwróci tylko wiersze z lewej tabeli, niezależnie od liczby pasujących wierszy po prawej stronie. Dlatego w wyniku widzisz inną liczbę wierszy.

Próbuję uzyskać nazwy z tabeli_1, które pojawiają się tylko w tabeli_2.

Następnie LEFT SEMI JOINnależy użyć odpowiedniego zapytania.


Czy naprawdę istnieje coś takiego jak LEFT SEMI JOIN? Czy to nie jest po prostu SEMI JOIN? Nie ma sensu RIGHT SEMI JOIN, prawda?
ErikE

W Hive , tak.
D Stanley,

1
świetna odpowiedź, czego szukałem. uściśliłbym odpowiedź dokładniej: „... INNER JOIN zwróci jeden wiersz na każdy pasujący wiersz prawej tabeli , a LEFT SEMI JOIN ...
Barak1731475

2
Przeciwieństwem tego jest LEFT ANTI JOIN, które filtruje dane z prawej tabeli w lewej tabeli według klucza. Pomyślałem, że zostawię ten samorodek dla kogoś, kto może patrzeć!
shantanusinghal

64

Załóżmy, że istnieją 2 tabele TableA i TableB z tylko 2 kolumnami (Id, Data) i następującymi danymi:

Tabela A:

+----+---------+
| Id |  Data   |
+----+---------+
|  1 | DataA11 |
|  1 | DataA12 |
|  1 | DataA13 |
|  2 | DataA21 |
|  3 | DataA31 |
+----+---------+

Tabela B:

+----+---------+
| Id |  Data   |
+----+---------+
|  1 | DataB11 |
|  2 | DataB21 |
|  2 | DataB22 |
|  2 | DataB23 |
|  4 | DataB41 |
+----+---------+

Wewnętrzne sprzężenie w kolumnie Idzwróci kolumny z obu tabel i tylko pasujące rekordy:

.----.---------.----.---------.
| Id |  Data   | Id |  Data   |
:----+---------+----+---------:
|  1 | DataA11 |  1 | DataB11 |
:----+---------+----+---------:
|  1 | DataA12 |  1 | DataB11 |
:----+---------+----+---------:
|  1 | DataA13 |  1 | DataB11 |
:----+---------+----+---------:
|  2 | DataA21 |  2 | DataB21 |
:----+---------+----+---------:
|  2 | DataA21 |  2 | DataB22 |
:----+---------+----+---------:
|  2 | DataA21 |  2 | DataB23 |
'----'---------'----'---------'

Left Join (lub Left Outer Join ) w kolumnie Idzwróci kolumny z obu tabel i pasujące rekordy z rekordami z lewej tabeli (wartości Null z prawej tabeli):

.----.---------.----.---------.
| Id |  Data   | Id |  Data   |
:----+---------+----+---------:
|  1 | DataA11 |  1 | DataB11 |
:----+---------+----+---------:
|  1 | DataA12 |  1 | DataB11 |
:----+---------+----+---------:
|  1 | DataA13 |  1 | DataB11 |
:----+---------+----+---------:
|  2 | DataA21 |  2 | DataB21 |
:----+---------+----+---------:
|  2 | DataA21 |  2 | DataB22 |
:----+---------+----+---------:
|  2 | DataA21 |  2 | DataB23 |
:----+---------+----+---------:
|  3 | DataA31 |    |         |
'----'---------'----'---------'

Right Join (lub Right Outer join) w kolumnie Idzwróci kolumny z obu tabel i pasujące rekordy z rekordami z prawej tabeli (wartości Null z lewej tabeli):

┌────┬─────────┬────┬─────────┐
│ Id │  Data   │ Id │  Data   │
├────┼─────────┼────┼─────────┤
│  1 │ DataA11 │  1 │ DataB11 │
│  1 │ DataA12 │  1 │ DataB11 │
│  1 │ DataA13 │  1 │ DataB11 │
│  2 │ DataA21 │  2 │ DataB21 │
│  2 │ DataA21 │  2 │ DataB22 │
│  2 │ DataA21 │  2 │ DataB23 │
│    │         │  4 │ DataB41 │
└────┴─────────┴────┴─────────┘

Pełne połączenie zewnętrzne w kolumnie Idzwróci kolumny z obu tabel i pasujące rekordy z rekordami z lewej tabeli (wartości Null z prawej tabeli) i rekordy z prawej tabeli (wartości Null z lewej tabeli):

╔════╦═════════╦════╦═════════╗
║ Id ║  Data   ║ Id ║  Data   ║
╠════╬═════════╬════╬═════════╣
║  - ║         ║    ║         ║
║  1 ║ DataA11 ║  1 ║ DataB11 ║
║  1 ║ DataA12 ║  1 ║ DataB11 ║
║  1 ║ DataA13 ║  1 ║ DataB11 ║
║  2 ║ DataA21 ║  2 ║ DataB21 ║
║  2 ║ DataA21 ║  2 ║ DataB22 ║
║  2 ║ DataA21 ║  2 ║ DataB23 ║
║  3 ║ DataA31 ║    ║         ║
║    ║         ║  4 ║ DataB41 ║
╚════╩═════════╩════╩═════════╝

Lewe półłączenie w kolumnie Idzwróci kolumny tylko z lewej tabeli i pasujące rekordy tylko z lewej tabeli:

┌────┬─────────┐
│ Id │  Data   │
├────┼─────────┤
│  1 │ DataA11 │
│  1 │ DataA12 │
│  1 │ DataA13 │
│  2 │ DataA21 │
└────┴─────────┘

Nazywałem to jako „LEFT INNER Join”.
Anshul Joshi

DISTINCT z A. * z wyniku INNER JOIN jest równoważne LEFT SEMI JOIN.
Teja

4
Odrębność nie brzmi bezpiecznie, przypuśćmy, że A zawiera dwa identyczne rekordy.
Dennis Jaheruddin

Nawet jeśli wynik będzie taki sam, użycie DISTINCT może mieć droższy plan w porównaniu z EXISTS
manotheshark

32

Wypróbowałem w Hive i otrzymałem poniższe wyniki

Tabela 1

1, WQE, Chennai, Indie

2, stu, salem, indie

3, Mia, Bangalore, Indie

4, tak, Newyork, USA

Tabela 2

1, WQE, Chennai, Indie

2, stu, salem, indie

3, Mia, Bangalore, Indie

5, chapie, Los angels, USA

Połączenie wewnętrzne

SELECT * FROM table1 INNER JOIN table2 ON (table1.id = table2.id);

1 wqe chennai indie 1 wqe chennai indie

2 stu salem indie 2 stu salem indie

3 mia bangalore indie 3 mia bangalore indie

Left Join

SELECT * FROM table1 LEFT JOIN table2 ON (table1.id = table2.id);

1 wqe chennai indie 1 wqe chennai indie

2 stu salem indie 2 stu salem indie

3 mia bangalore indie 3 mia bangalore indie

4 yepie newyork USA NULL NULL NULL NULL

Połączyć lewe

SELECT * FROM table1 LEFT SEMI JOIN table2 ON (table1.id = table2.id);

1 wqe chennai indie

2 stu salem indie

3 mia bangalore indie

Uwaga: Wyświetlane są tylko rekordy z lewej tabeli, podczas gdy dla Left Join oba rekordy tabeli są wyświetlane

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.