CROSS JOIN vs INNER JOIN w SQL

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

CROSS DOŁĄCZ:

SELECT 
    Movies.CustomerID, Movies.Movie, Customers.Age, 
    Customers.Gender, Customers.[Education Level], 
    Customers.[Internet Connection], Customers.[Marital Status], 
FROM   
    Customers 
CROSS JOIN 
    Movies

WEWNĘTRZNE DOŁĄCZ:

SELECT 
    Movies.CustomerID, Movies.Movie, Customers.Age, 
    Customers.Gender, Customers.[Education Level], 
    Customers.[Internet Connection], Customers.[Marital Status]
FROM   
    Customers 
INNER JOIN 
    Movies ON Customers.CustomerID = Movies.CustomerID

Który z nich jest lepszy i dlaczego miałbym używać jednego?

Łączenie krzyżowe nie łączy wierszy, jeśli masz 100 wierszy w każdej tabeli z dopasowaniem 1 do 1, otrzymasz 10.000 wyników, Innerjoin zwróci tylko 100 wierszy w tej samej sytuacji.

Te dwa przykłady zwrócą ten sam wynik:

Łączenie krzyżowe

select * from table1 cross join table2 where table1.id = table2.fk_id

Połączenie wewnętrzne

select * from table1 join table2 on table1.id = table2.fk_id

Użyj ostatniej metody

Oto najlepszy przykład łączenia krzyżowego i wewnętrznego.

Rozważ poniższe tabele

STÓŁ : Teacher

x------------------------x
| TchrId   | TeacherName | 
x----------|-------------x
|    T1    |    Mary     |
|    T2    |    Jim      |
x------------------------x

STÓŁ : Student

x--------------------------------------x
|  StudId  |    TchrId   | StudentName | 
x----------|-------------|-------------x            
|    S1    |     T1      |    Vineeth  |
|    S2    |     T1      |    Unni     |
x--------------------------------------x

1. WEWNĘTRZNE DOŁĄCZENIE

Sprzężenie wewnętrzne wybiera wiersze, które spełniają wymagania obu tabeli .

Weź pod uwagę, że musimy znaleźć nauczycieli, którzy są nauczycielami klasowymi i odpowiadającymi im uczniami. W takim przypadku musimy złożyć wniosek JOINlub INNER JOINi będziemy

Pytanie

SELECT T.TchrId,T.TeacherName,S.StudentName 
FROM #Teacher T
INNER JOIN #Student S ON T.TchrId = S.TchrId

• SQL FIDDLE

Wynik

x--------------------------------------x
|  TchrId  | TeacherName | StudentName | 
x----------|-------------|-------------x            
|    T1    |     Mary    |    Vineeth  |
|    T1    |     Mary    |    Unni     |
x--------------------------------------x

2. CROSS JOIN

Łączenie krzyżowe wybiera wszystkie wiersze z pierwszej tabeli i wszystkie wiersze z drugiej tabeli i przedstawia jako iloczyn kartezjański, tj. Ze wszystkimi możliwościami

Rozważ, że musimy znaleźć wszystkich nauczycieli w szkole i uczniów niezależnie od nauczycieli klasowych, musimy złożyć wniosek CROSS JOIN.

Pytanie

SELECT T.TchrId,T.TeacherName,S.StudentName 
FROM #Teacher T
CROSS JOIN #Student S 

• SQL FIDDLE

Wynik

x--------------------------------------x
|  TchrId  | TeacherName | StudentName | 
x----------|-------------|-------------x            
|    T2    |     Jim     |    Vineeth  |
|    T2    |     Jim     |    Unni     |
|    T1    |     Mary    |    Vineeth  |
|    T1    |     Mary    |    Unni     |
x--------------------------------------x

CROSS JOIN = (INNER) JOIN = przecinek („,”)

TL; DR Jedyną różnicą między SQL CROSS JOIN, (INNER) JOIN i przecinkiem („,”) (poza przecinkiem o niższym priorytecie dla kolejności oceny) jest to, że (INNER) JOIN ma ON, podczas gdy CROSS JOIN i przecinek nie.

Produkty pośrednie

Wszystkie trzy tworzą pośredni, konceptualny produkt „kartezjański” w stylu SQL, nazywany łączeniem krzyżowym wszystkich możliwych kombinacji wiersza z każdej tabeli. Jest ON i / lub GDZIE, które zmniejszają liczbę wierszy. SQL Fiddle

Standard SQL definiuje <comma> za pośrednictwem produktu (7.5 1.b.ii), <łączenie krzyżowe> przez <comma> (7.7 1.a) i JOIN ON <warunek wyszukiwania> przez <comma> plus WHERE (7.7 1.b ).

Jak podaje Wikipedia:

Łączenie krzyżowe

CROSS JOIN zwraca iloczyn kartezjański wierszy z tabel w złączeniu. Innymi słowy, utworzy wiersze, które łączą każdy wiersz z pierwszej tabeli z każdym wierszem z drugiej tabeli.

Połączenie wewnętrzne

[...] Wynik sprzężenia można zdefiniować jako wynik pierwszego obliczenia iloczynu kartezjańskiego (lub sprzężenia krzyżowego) wszystkich rekordów w tabelach (połączenie każdego rekordu w tabeli A z każdym rekordem w tabeli B), a następnie zwrócenia wszystkie rekordy spełniające predykat łączenia.

„Notacja niejawnego łączenia” po prostu wymienia tabele do łączenia, w klauzuli FROM instrukcji SELECT, używając przecinków do ich oddzielenia. W ten sposób określa sprzężenie krzyżowe

W przypadku ZEWNĘTRZNYCH JOIN i używania opcji ON vs WHERE w nich zobacz Warunki w LEFT JOIN (OUTER JOIN) vs INNER JOIN .

Po co porównywać kolumny między tabelami?

Gdy nie ma zduplikowanych wierszy:

Każda tabela zawiera wiersze, które składają się na prawdziwe stwierdzenie z określonego szablonu instrukcji wypełniania [nazwanych] pustych miejsc. (To sprawia, że prawdziwa propozycja from-- spełnia -A pewną (charakterystyczny) predykat ).

• Tabela podstawowa zawiera wiersze, które zawierają prawdziwe oświadczenie z szablonu instrukcji podanego przez DBA:

  /* rows where
  customer C.CustomerID has age C.Age and ...
  */
  FROM Customers C

• Produkt pośredni sprzężenia zawiera wiersze, które tworzą prawdziwe stwierdzenie z AND z szablonów argumentów:

  /* rows where
      customer C.CustomerID has age C.Age and ...
  AND movie M.Movie is rented by customer M.CustomerID and ...
  */
  FROM Customers C CROSS JOIN Movies M

• ON & WHERE warunki są wstawiane AND, aby dać kolejny szablon. Wartością są ponownie wiersze, które spełniają wymagania tego szablonu:

  /* rows where
      customer C.CustomerID has age C.Age and ...
  AND movie M.Movie is rented by customer M.CustomerID and ...
  AND C.CustomerID = M.CustomerID
  AND C.Age >= M.[Minimum Age]
  AND C.Age = 18
  */
  FROM Customers C INNER JOIN Movies M
  ON C.CustomerID = M.CustomerID
  AND C.Age >= M.[Minimum Age]
  WHERE C.Age = 18

W szczególności porównanie kolumn dla równości (SQL) między tabelami oznacza, że ​​wiersze zachowane z produktu z części szablonu połączonych tabel mają tę samą (inną niż NULL) wartość dla tych kolumn. To po prostu przypadek, że wiele wierszy jest zwykle usuwanych przez porównania równości między tabelami - konieczne i wystarczające jest scharakteryzowanie żądanych wierszy.

Po prostu napisz SQL dla szablonu dla żądanych wierszy!

Co do znaczenia zapytań (i tabel a warunków) zobacz:
Jak uzyskać pasujące dane z innej tabeli SQL dla dwóch różnych kolumn: Inner Join i / lub Union?
Czy jest jakaś praktyczna zasada konstruowania zapytania SQL na podstawie opisu czytelnego dla człowieka?

Przeciążanie połączenia krzyżowego

Niestety, termin „łączenie krzyżowe” jest używany do:

• Produkt pośredni.
• CROSS JOIN.
• (INNER) JOIN z wartością ON lub WHERE, która nie porównuje żadnych kolumn z jednej tabeli z żadnymi kolumnami innej. (Ponieważ powoduje to zwrócenie tak wielu pośrednich wierszy produktów).

Te różne znaczenia zostają zmieszane. (Np. Jak w innych odpowiedziach i komentarzach tutaj.)

Używanie CROSS JOIN vs (INNER) JOIN vs comma

Wspólna konwencja to:

• Użyj CROSS JOIN tylko wtedy, gdy nie porównujesz kolumn między tabelami. Ma to pokazać, że brak porównań był zamierzony.
• Użyj opcji (INNER) JOIN z wartością ON wtedy i tylko wtedy, gdy porównujesz kolumny między tabelami. (Oraz ewentualnie inne warunki.)
• Nie używaj przecinka.

Zwykle również warunki, które nie znajdują się na parach tabel, są przechowywane dla GDZIE. Ale może być konieczne umieszczenie ich w (n INNER) JOIN ON, aby uzyskać odpowiednie wiersze dla argumentu DOŁĄCZENIA PRAWEGO, LEWEGO lub PEŁNEGO (ZEWNĘTRZNEGO).

Re „Nie używaj przecinka” Mieszanie przecinka z jawnym JOIN może wprowadzać w błąd, ponieważ przecinek ma niższy priorytet. Ale biorąc pod uwagę rolę produktu pośredniego w znaczeniu CROSS JOIN, (INNER) JOIN i przecinka, argumenty za powyższą konwencją w ogóle go nie używają są niepewne. CROSS JOIN lub przecinek jest podobny do (INNER) JOIN, który ma PRAWDA. Iloczyn pośredni, ON i WHERE, wszystkie wprowadzają AND w odpowiednim predykacie. Jednak w innym przypadku można pomyśleć o INNER JOIN ON - powiedzmy, generowaniu wiersza wyjściowego tylko wtedy, gdy znajduje się para wierszy wejściowych, które spełniają warunek ON - mimo to zwraca wiersze sprzężenia krzyżowego, które spełniają warunek. Jedynym powodem, dla którego ON musiał uzupełniać przecinek w SQL, było pisanie OUTERDOŁĄCZA. Oczywiście wyrażenie powinno jasno określać swoje znaczenie; ale to, co jest jasne, zależy od tego, jakie rzeczy mają oznaczać.

Diagramy Re Venna Diagram Venna z dwoma przecinającymi się okręgami może zilustrować różnicę między wierszami wyjściowymi dla WEWNĘTRZNYCH, W LEWO, PRAWO i PEŁNYCH SPRZĘŻEŃ dla tego samego wejścia. A kiedy ON jest bezwarunkowo PRAWDA, wynik INNER JOIN jest taki sam jak CROSS JOIN. Może również zilustrować wiersze wejściowe i wyjściowe dla INTERSECT, UNION & EXCEPT. A gdy oba wejścia mają te same kolumny, wynik INTERSECT jest taki sam, jak dla standardowego SQL NATURAL JOIN, a wynik EXCEPT jest taki sam, jak dla niektórych idiomów obejmujących LEFT & RIGHT JOIN. Ale nie ilustruje, jak ogólnie działa funkcja (INNER) JOIN. Na pierwszy rzut oka wydaje się to prawdopodobne . Może identyfikować części danych wejściowych i / lub wyjściowych dlaspecjalne przypadki ON, PK (klucze podstawowe), FK (klucze obce) i / lub SELECT. Wszystko, co musisz zrobić, aby to zobaczyć, to określić, jakie dokładnie są elementy zestawów reprezentowanych przez koła . (Które niejasne prezentacje nigdy nie wyjaśniają.) (Pamiętaj, że generalnie w przypadku złączeń wiersze wyjściowe mają inne nagłówki niż wiersze wejściowe . A tabele SQL to worki, a nie zestawy wierszy z wartościami NULL ).

Połączenie wewnętrzne

Sprzężenie, które wyświetla tylko te wiersze, które są zgodne w obu połączonych tabelach, jest znane jako sprzężenie wewnętrzne. Jest to domyślne łączenie w zapytaniu i projektancie widoku.

Składnia łączenia wewnętrznego

SELECT t1.column_name,t2.column_name
FROM table_name1 t1
INNER JOIN table_name2 t2
ON t1.column_name=t2.column_name

Łączenie krzyżowe

Sprzężenie krzyżowe, które tworzy iloczyn kartezjański tabel biorących udział w sprzężeniu. Rozmiar iloczynu kartezjańskiego to liczba wierszy w pierwszej tabeli pomnożona przez liczbę wierszy w drugiej tabeli.

Składnia połączenia krzyżowego

SELECT * FROM table_name1
CROSS JOIN table_name2

Lub możemy napisać to również w inny sposób

SELECT * FROM table_name1,table_name2

Teraz sprawdź poniższe zapytanie dotyczące łączenia krzyżowego

Przykład

SELECT * FROM UserDetails
CROSS JOIN OrderDetails

Lub

SELECT * FROM UserDetails, OrderDetails

Pamiętaj, że jeśli dodana jest klauzula WHERE, sprzężenie krzyżowe zachowuje się jak sprzężenie wewnętrzne. Na przykład następujące zapytania języka Transact-SQL dają ten sam zestaw wyników. Zobacz http://technet.microsoft.com/en-us/library/ms190690(v=sql.105).aspx

CROSS JOIN

Jak wyjaśniłem w tym artykule , CROSS JOIN ma na celu wygenerowanie produktu kartezjańskiego.

Iloczyn kartezjański przyjmuje dwa zbiory A i B i generuje wszystkie możliwe permutacje par rekordów z dwóch podanych zestawów danych.

Na przykład zakładając, że masz następujące ranksi suitstabele bazy danych:

A ranksma następujące wiersze:

| name  | symbol | rank_value |
|-------|--------|------------|
| Ace   | A      | 14         |
| King  | K      | 13         |
| Queen | Q      | 12         |
| Jack  | J      | 11         |
| Ten   | 10     | 10         |
| Nine  | 9      |  9         |

Chociaż suitstabela zawiera następujące rekordy:

| name    | symbol |
|---------|--------|
| Club    | ♣      |
| Diamond | ♦      |
| Heart   | ♥      |
| Spade   | ♠      |

Jako zapytanie CROSS JOIN, takie jak następujące:

SELECT
   r.symbol AS card_rank,
   s.symbol AS card_suit
FROM
   ranks r
CROSS JOIN
   suits s

wygeneruje wszystkie możliwe permutacje ranksi suitespary:

| card_rank | card_suit |
|-----------|-----------|
| A         | ♣         |
| A         | ♦         |
| A         | ♥         |
| A         | ♠         |
| K         | ♣         |
| K         | ♦         |
| K         | ♥         |
| K         | ♠         |
| Q         | ♣         |
| Q         | ♦         |
| Q         | ♥         |
| Q         | ♠         |
| J         | ♣         |
| J         | ♦         |
| J         | ♥         |
| J         | ♠         |
| 10        | ♣         |
| 10        | ♦         |
| 10        | ♥         |
| 10        | ♠         |
| 9         | ♣         |
| 9         | ♦         |
| 9         | ♥         |
| 9         | ♠         |

WEWNĘTRZNE DOŁĄCZENIE

Z drugiej strony INNER JOIN nie zwraca iloczynu kartezjańskiego dwóch łączących się zestawów danych.

Zamiast tego funkcja INNER JOIN pobiera wszystkie elementy z tabeli po lewej stronie i dopasowuje je do rekordów w tabeli po prawej stronie, tak aby:

• jeśli żaden rekord nie zostanie dopasowany w tabeli po prawej stronie, wiersz po lewej stronie jest odfiltrowywany z zestawu wyników
• dla dowolnego zgodnego rekordu w tabeli po prawej stronie wiersz po lewej stronie jest powtarzany, tak jakby istniał iloczyn kartezjański między tym rekordem a wszystkimi powiązanymi z nim rekordami podrzędnymi w tabeli po prawej stronie.

Na przykład, zakładając, że mamy relację jeden do wielu między tabelami nadrzędnymi posti podrzędnymi, post_commentktóra wygląda następująco:

Teraz, jeśli posttabela zawiera następujące rekordy:

| id | title     |
|----|-----------|
| 1  | Java      |
| 2  | Hibernate |
| 3  | JPA       |

a post_commentstabela zawiera następujące wiersze:

| id | review    | post_id |
|----|-----------|---------|
| 1  | Good      | 1       |
| 2  | Excellent | 1       |
| 3  | Awesome   | 2       |

Zapytanie INNER JOIN, takie jak następujące:

SELECT
   p.id AS post_id,
   p.title AS post_title,
   pc.review  AS review
FROM post p
INNER JOIN post_comment pc ON pc.post_id = p.id

będzie obejmował wszystkie postrekordy wraz ze wszystkimi powiązanymi z nimi post_comments:

| post_id | post_title | review    |
|---------|------------|-----------|
| 1       | Java       | Good      |
| 1       | Java       | Excellent |
| 2       | Hibernate  | Awesome   |

Zasadniczo możesz myśleć o INNER JOIN jako przefiltrowanym CROSS JOIN, w którym tylko pasujące rekordy są przechowywane w końcowym zestawie wyników.

Aby uzyskać więcej informacji na temat działania INNER JOIN, zapoznaj się również z tym artykułem .

SQL Server akceptuje również prostszą notację:

SELECT A.F, 
       B.G, 
       C.H 
  FROM TABLE_A A, 
       TABLE_B B, 
       TABLE_C C
 WHERE A.X = B.X 
   AND B.Y = C.Y

Korzystając z tego prostszego zapisu, nie trzeba przejmować się różnicą między złączeniami wewnętrznymi i krzyżowymi. Zamiast dwóch klauzul „ON” istnieje pojedyncza klauzula „WHERE”, która wykonuje zadanie. Jeśli masz jakiekolwiek trudności z ustaleniem, które klauzule „JOIN” „ON” idą do miejsca, porzuć notację „JOIN” i użyj prostszego powyżej.

To nie jest oszustwo.

Podczas pisania zapytań z użyciem złączeń wewnętrznych rekordy będą pobierać z obu tabel, jeśli warunek zostanie spełniony w obu tabelach, tj. Dokładne dopasowanie wspólnej kolumny w obu tabelach.

Podczas pisania zapytania przy użyciu sprzężenia krzyżowego wynik jest podobny do iloczynu kartezjańskiego liczby rekordów w obu tabelach. przykład, jeśli tabela1 zawiera 2 rekordy, a tabela2 zawiera 3 rekordy, to wynik zapytania to 2 * 3 = 6 rekordów.

Więc nie używaj łączenia krzyżowego, dopóki tego nie potrzebujesz.

Łączenie krzyżowe i łączenie wewnętrzne są takie same z tą różnicą, że w sprzężeniu wewnętrznym filtrujemy w sposób logiczny niektóre wyniki iloczynu kartezjańskiego

table1
x--------------------------------------x
|  fieldA  |    fieldB   |    fieldC   | 
x----------|-------------|-------------x            
|    A     |      B      |    option1  |
|    A     |      B1     |    option2  |
x--------------------------------------x

table2
x--------------------------------------x
|  fieldA  |    fieldB   |    fieldC   | 
x----------|-------------|-------------x            
|    A     |      B      |    optionB1 |
|    A1    |      B1     |    optionB2 |
x--------------------------------------x

 cross join
  A,B,option1,A,B,optionB1
  A,B,option1,A1,B1,optionB2
  A,B1,option2,A,B,optionB1
  A,B1,option2,A1,B1,optionB2

 inner join on field1 (only with the value is the same in both tables)
  A,B,option1,A,B,optionB1
  A,B1,option2,A,B,optionB1

 inner join on field1
  A,B,option1,A,B,optionB1

To na projektowaniu naszych danych decydujemy, że istnieje tylko jeden przypadek pola, którego używamy do łączenia. Łącz tylko łącz krzyżowo łącz obie tabele i otrzymuj tylko linie spełniające specjalne wyrażenie boolowskie.

Zauważ, że jeśli pola, na których robimy nasze połączenia, będą miały wartość null w obu tabelach, przepuścimy filtr. Do nas lub producenta bazy danych należy dodanie dodatkowych reguł w celu uniknięcia lub zezwolenia na zerowanie. Zgodnie z podstawami jest to po prostu łączenie krzyżowe, po którym następuje filtr.

Sprzężenie wewnętrzne da wynik dopasowanych rekordów między dwiema tabelami, gdzie sprzężenie krzyżowe daje możliwe kombinacje między dwiema tabelami.

A = {1,5,3,4,6,7,9,8} B = {2,8,5,4,3,6,9}

cross join działają jak iloczyn kartezjański A ✖ B = {1,2}, {1,8} ..., {5,2}, {5,8}, {5,5} ..... {3,3} ..., {6,6} .... {8,9} i zwrócił ten długi zestaw wyników ..

podczas przetwarzania inner joindokonanego za pomocą iloczynu kartezjańskiego i wybierz pasujące pary. jeśli myślisz, że te uporządkowane pary są dwoma kluczami głównymi tabeli i szukają klauzuli A = B, toinner join wybierz {5,5}, {4,4}, {6,6} , {9,9} i zwróciliśmy zapytaną kolumnę w klauzuli select związanej z tymi identyfikatorami.

jeśli cross joinna a = b, to wynik jest taki sam jak zestaw wyników inner join. w takim przypadku również użyj inner join.

To zależy od oczekiwanej wydajności.

Sprzężenie krzyżowe dopasowuje wszystkie wiersze w jednej tabeli do wszystkich wierszy w innej tabeli. Sprzężenie wewnętrzne pasuje do pola lub pól. Jeśli masz jedną tabelę z 10 wierszami, a drugą z 10 wierszami, dwa połączenia będą się zachowywać inaczej.

W przypadku sprzężenia krzyżowego zostanie zwróconych 100 wierszy i nie będą one ze sobą powiązane, tylko tak zwany iloczyn kartezjański. Sprzężenie wewnętrzne dopasuje rekordy do siebie. Zakładając, że jeden ma klucz podstawowy, a drugi jest kluczem obcym, zwróconych zostanie 10 wierszy.

Łączenie krzyżowe ma ograniczoną użyteczność ogólną, ale istnieje w celu zapewnienia kompletności i opisuje wynik łączenia tabel bez żadnych relacji dodanych do zapytania. Możesz użyć sprzężenia krzyżowego, aby utworzyć listę kombinacji słów lub czegoś podobnego. Z drugiej strony, najczęściej stosowane jest złączenie wewnętrzne.