SQL Server: Jaka jest różnica między CROSS JOIN a FULL OUTER JOIN?


Odpowiedzi:


244

Połączenie krzyżowe tworzy iloczyn kartezjański między dwiema tabelami, zwracając wszystkie możliwe kombinacje wszystkich rzędów. Nie ma onklauzuli, ponieważ po prostu łączysz wszystko ze wszystkim.

A full outer joinjest kombinacją a left outeri right outerdołączenia. Zwraca wszystkie wiersze w obu tabelach, które pasują do whereklauzuli zapytania , a w przypadkach, w których onwarunek nie może być spełniony dla tych wierszy, wstawia nullwartości dla pustych pól.

Ten artykuł na Wikipedii wyjaśnia różne rodzaje złączeń z przykładami danych wyjściowych na podstawie przykładowego zestawu tabel.


Wtedy w przypadku dużych stołów FROM t1 FULL OUTER JOIN t2 ON t1.id=t2.id zawsze będzie szybciej niż FROM t1,t2 WHERE t1.id=t2.id?
alexkovelsky,

Złączenia wewnętrzne @alexkovelsky są często szybsze, gdy między dwiema tabelami występuje niewiele dopasowań, ponieważ użycie indeksów oznacza, że ​​nie przeszkadza to w czytaniu wszystkich wierszy w jednej z tabel. pełne sprzężenie zewnętrzne musi zawsze czytać wszystkie wiersze w obu tabelach (lub odpowiednich indeksach). W przypadku, gdy indeksy nie są wystarczające lub konieczne jest odczytanie stosu bazowego w celu wygenerowania potrzebnych kolumn, wówczas pełne łączenie zewnętrzne jest prawie zawsze wolniejsze niż łączenie wewnętrzne.
Andrew Hill,

1
Jest outer joinszybszy czy cross join?
Shafizadeh

2
@Shafizadeh - Robią różne rzeczy.
Donnie

9
Co jeśli FULL OUTER DOŁĄCZ do True? czy wynik / wydajność będzie podobna do CROSS JOIN?
Architectonic

65

Jedną rzeczą, która nie zawsze może być oczywista dla niektórych, jest to, że połączenie krzyżowe z pustą tabelą (lub zestawem wyników) skutkuje pustą tabelą (M x N; stąd M x 0 = 0)

Pełne sprzężenie zewnętrzne zawsze będzie miało rzędy, chyba że zarówno M, jak i N są równe 0.


32

Chciałbym dodać jeden ważny aspekt do innych odpowiedzi, które faktycznie wyjaśniły mi ten temat w najlepszy sposób:

Jeśli 2 połączone tabele zawierają M i N wierszy, wówczas łączenie krzyżowe zawsze będzie dawało (M x N) wiersze, ale pełne łączenie zewnętrzne da wiersze od MAX (M, N) do (M + N) wierszy (w zależności od liczby faktycznie dopasuj predykat „on”).

EDYTOWAĆ:

Z logicznego punktu widzenia przetwarzania zapytań CROSS JOIN rzeczywiście zawsze generuje M x N wierszy. To, co dzieje się z FULL OUTER JOIN, polega na tym, że zarówno lewa, jak i prawa tabela są „zachowywane”, tak jakby nastąpiło połączenie LEWE i PRAWE. Tak więc wiersze, niespełniające predykatu ON, zarówno z lewej, jak i prawej tabeli są dodawane do zestawu wyników.


2
Czy te granice wykluczają możliwe mecze 1-wiele? Pełne połączenie zewnętrzne jest nadal w stanie wytwarzać (M x N) rzędy.
maxwellb

1
wybierz COUNT_BIG (*) FROM Traffic t CROSS JOIN odbiorca r i WYBIERZ COUNT_BIG (*) FROM Traffic t FULL JOIN odbiorca r ON (1 = 1) są takie same.
urlreader

2
Twoja jest najlepszą prawdziwą odpowiedzią. Zasadniczo: cross joinwielokrotność tabel; a full outer joindodaje je w najgorszym przypadku, w zależności od liczby pasujących rzędów.
Brian Peterson

Tak .. Szukałem tej matematyki - produkuj od MAX (M, N) do (M + N) wierszy .. Dzięki zagłosowałem.
Arup Rakshit

1
To jest źle. FULL JOIN ON wiersze są INNER JOIN ON wiersze UNION ALL niedopasowane lewe rzędy tabeli null-Extended UNION ALL niedopasowane prawe rzędy tabeli null-Extended. Zatem FULL JOIN może zwrócić wiersze M * N - być może większe niż MAX (M, N) i M + N. Ale w każdym razie minimalna i maksymalna liczba wierszy zwróconych jako funkcja M i N jest po prostu nieprzydatna . Przydaje się jasna definicja FULL JOIN ON - w kategoriach INNER JOIN ON i niedopasowane wiersze.
philipxy

15

Połączenia krzyżowe: Połączenia krzyżowe dają wyniki, które składają się z każdej kombinacji wierszy z dwóch lub więcej tabel. Oznacza to, że jeśli tabela A ma 3 wiersze, a tabela B ma 2 wiersze, połączenie krzyżowe da w wyniku 6 wierszy. Nie ma żadnej relacji między tymi dwiema tabelami - dosłownie tworzysz każdą możliwą kombinację.

Pełna zewnętrzna złączenie: PEŁNE ZŁĄCZE ZEWNĘTRZNE nie jest ani „lewe”, ani „prawe” - to jedno i drugie! Obejmuje wszystkie wiersze z obu tabel lub zestawów wyników uczestniczących w JOIN. Jeśli nie ma pasujących wierszy dla wierszy po „lewej” stronie DOŁĄCZ, zobaczysz wartości Null z zestawu wyników po „prawej”. I odwrotnie, gdy nie ma pasujących wierszy dla wierszy po „prawej” stronie JOIN, wartości Null z zestawu wyników znajdują się po „lewej” stronie.


15

Dla SQL Server CROSS JOIN and FULL OUTER JOINsą różne. CROSS JOINjest po prostu produktem kartezjańskim dwóch tabel, niezależnie od kryteriów filtrowania lub warunków.

FULL OUTER JOINdaje unikalny zestaw LEFT OUTER JOIN and RIGHT OUTER JOINwyników dwóch tabel. Potrzebuje również klauzuli ON, aby odwzorować dwie kolumny tabel.

Tabela 1 zawiera 10 wierszy, a tabela 2 zawiera 20 wierszy z 5 rzędami pasującymi do poszczególnych kolumn.

Następnie CROSS JOINzwróci 10 * 20 = 200 wierszy w zestawie wyników.

FULL OUTER JOIN zwróci 25 wierszy w zestawie wyników.

FULL OUTER JOIN(lub dowolny inny DOŁĄCZ) zawsze zwraca zestaw wyników z wartością mniejszą lub równą Cartesian Product number.

Liczba wierszy zwróconych o FULL OUTER JOINrówna (liczba wierszy wg LEFT OUTER JOIN) + (liczba wierszy wg RIGHT OUTER JOIN) - (liczba wierszy wg INNER JOIN).


8

Są to te same pojęcia, z wyjątkiem zwracanej wartości NULL.

Patrz poniżej:

declare @table1 table( col1 int, col2 int );
insert into @table1 select 1, 11 union all select 2, 22;

declare @table2 table ( col1 int, col2 int );
insert into @table2 select 10, 101 union all select 2, 202;

select
    t1.*,
    t2.*
from @table1 t1
full outer join @table2 t2 on t1.col1 = t2.col1
order by t1.col1, t2.col1;

/* full outer join
col1        col2        col1        col2
----------- ----------- ----------- -----------
NULL        NULL        10          101
1           11          NULL        NULL
2           22          2           202
*/

select
    t1.*,
    t2.*
from @table1 t1
cross join @table2 t2
order by t1.col1, t2.col1;

/* cross join
col1        col2        col1        col2
----------- ----------- ----------- -----------
1           11          2           202
1           11          10          101
2           22          2           202
2           22          10          101
*/

1
Doskonały przykład!
Lucas925

1
dzięki za prawdziwy przykład danych. to czyni to jaśniejszym.
dtc


2

Pełne połączenie zewnętrzne łączy lewe połączenie zewnętrzne i prawe połączenie zewnętrzne. Zestaw wyników zwraca wiersze z obu tabel, w których warunki są spełnione, ale zwraca kolumny null, w których nie ma zgodności.

Łączenie krzyżowe jest produktem kartezjańskim , który nie wymaga żadnego warunku do łączenia tabel. Zestaw wyników zawiera wiersze i kolumny będące zwielokrotnieniem obu tabel.


1

Oto przykład, w którym zarówno FULL OUTER JOIN, jak i CROSS JOIN zwracają ten sam zestaw wyników bez zwracanej wartości NULL. Zwróć uwagę na 1 = 1 w klauzuli ON dla PEŁNEGO DOŁĄCZENIA DO ZEWNĘTRZNEGO:

declare @table1 table (     col1    int,    col2    int ) 
declare @table2 table (     col1    int,    col2    int )

insert into @table1 select  1, 11   union all select    2, 22   

insert into @table2 select  10, 101 union all select     2, 202

select  *
from    @table1 t1 full outer join @table2 t2
    on  1 = 1
(Dotyczy 2 wierszy)

(Dotyczy 2 wierszy)
col1 col2 col1 col2
----------- ----------- ----------- -----------
1 11 10 101
2 22 10 101
1 11 2 202
2 22 2 202
select  *
from    @table1 t1 cross join @table2 t2
col1 col2 col1 col2
----------- ----------- ----------- -----------
1 11 10 101
2 22 10 101
1 11 2 202
2 22 2 202

(Dotyczy 4 wierszy)

1

DOŁĄCZ DO PEŁNEGO ZEWNĘTRZNEGO SQL

  • FULL OUTER JOIN zwraca wszystkie wiersze z lewej tabeli (tabela 1) i z prawej tabeli (tabela 2) niezależnie od dopasowania.

  • Słowo kluczowe FULL OUTER JOIN łączy wynik zarówno LEFT OUTER JOIN, jak i RIGHT OUTER JOIN

  • Pełne sprzężenie zewnętrzne SQL jest również znane jako FULL JOIN

Odniesienie: http://datasciencemadesimple.com/sql-full-outer-join/

ŁĄCZENIE KRZYŻA SQL

  • W SQL CROSS JOIN Każdy wiersz w pierwszej tabeli jest odwzorowany na każdy wiersz w drugiej tabeli.

  • Liczba wierszy wygenerowanych przez zestaw wyników operacji CROSS JOIN jest równa liczbie wierszy w pierwszej tabeli pomnożonej przez liczbę wierszy w drugiej tabeli.

  • CROSS JOIN jest również znany jako produkt kartezjański / połączenie kartezjańskie

  • Liczba wierszy w tabeli A wynosi m, Liczba wierszy w tabeli B wynosi n, a wynikowa tabela będzie miała m * n wierszy

Odniesienie: http://datasciencemadesimple.com/sql-cross-join/

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.