Jak zrobić PEŁNE ZŁĄCZE ZEWNĘTRZNE w MySQL?


654

Chcę wykonać Full Outer Join w MySQL. czy to możliwe? Czy MySQL obsługuje pełne przyłączenie zewnętrzne?


2
możliwy duplikat błędu składni
Joe Stefanelli

4
To pytanie ma lepsze odpowiedzi
Julio Marins

Uważaj na odpowiedzi tutaj. Standard SQL mówi, że pełne połączenie jest złączeniem wewnętrznym w połączeniu wierszy wszystkie niedopasowane lewe wiersze tabeli rozszerzone o nulls union wszystkie prawe wiersze tabeli przedłużone o null. Większość odpowiedzi tutaj jest zła (patrz komentarze), a te, które nie są złe, nie zajmują się ogólnym przypadkiem. Chociaż istnieje wiele (nieuzasadnionych) głosów poparcia. (Zobacz moją odpowiedź).
philipxy

Co powiesz na to, kiedy próbujesz dołączyć za pomocą kluczy innych niż podstawowe / pogrupowane kolumny? tak jak mam zapytanie o sprzedaż na państwo „stan”, „sprzedaż” i inne wydatki na państwo „stan”, „wydatki”, oba zapytania używają grupowania według („stan”). Kiedy łączę lewe i prawe połączenia między dwoma pytaniami, dostaję kilka wierszy ze sprzedażami, ale bez wydatków, kilka więcej z wydatkami, ale bez sprzedaży, wszystko do tego momentu, ale mam też kilka z obu sprzedaż i wydatki oraz powtarzająca się kolumna „stanowa” ... nie stanowi większego problemu, ale nie wydaje się słuszna ...
Jairo Lozano

1
@ JairoLozano Ograniczenia nie są potrzebne do zapytania. Chociaż gdy ograniczenia zawierają dodatkowe zapytania, zwracają pożądaną odpowiedź, która w innym przypadku nie byłaby. Ograniczenia nie wpływają na to, jakie pełne sprzężenie przy zwrotach dla danych argumentów. Opisany problem polega na tym, że zapytanie, które napisałeś, jest błędne. (Przypuszczalnie częsty błąd, w którym ludzie chcą niektórych złączeń, z których każde może wymagać innego klucza, niektórych podkwerend, z których każde może obejmować złączenie i / lub agregację, ale błędnie próbują wykonać całe łączenie, a następnie całe łączenie lub agregowanie w stosunku do poprzednich agregacji .)
philipxy

Odpowiedzi:


669

Nie masz PEŁNYCH ŁĄCZEŃ na MySQL, ale z pewnością możesz je emulować .

W przypadku kodu PRÓBKI przepisanego z tego pytania SO masz:

z dwiema tabelami t1, t2:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

Powyższe zapytanie działa w szczególnych przypadkach, w których operacja FULL OUTER JOIN nie wygenerowałaby żadnych duplikatów wierszy. Powyższe zapytanie zależy od UNIONustawionego operatora, aby usunąć zduplikowane wiersze wprowadzone przez wzorzec zapytania. Możemy uniknąć wprowadzania zduplikowanych wierszy przez stosując anty-przyłączyć wzorzec dla drugiego zapytania, a następnie użyć zestawu operator UNION ALL, aby połączyć dwa sety. W bardziej ogólnym przypadku, gdy PEŁNE DOŁĄCZENIE DO ZEWNĄTRZ zwróci zduplikowane wiersze, możemy to zrobić:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.id IS NULL

33
W rzeczywistości to, co napisałeś, jest nieprawidłowe. Ponieważ kiedy tworzysz UNIONĘ, usuwasz duplikaty, a czasami, kiedy dołączasz do dwóch różnych tabel, powinny być duplikaty.
Pavle Lekic

158
Oto poprawny przykład:(SELECT ... FROM tbl1 LEFT JOIN tbl2 ...) UNION ALL (SELECT ... FROM tbl1 RIGHT JOIN tbl2 ... WHERE tbl1.col IS NULL)
Pavle Lekic

8
Różnica polega na tym, że wykonuję lewe dołączenie, a następnie prawe wyłączanie za pomocą UNION ALL
Pavle Lekic

5
i widzę teraz, że sam to mówisz, przepraszam. Być może mógłbyś zaktualizować swoją odpowiedź, biorąc pod uwagę, że jest to przypadek, w którym popełnił błąd i że UNION ALL zawsze będzie bardziej wydajny?
ysth

10
@ypercube: Jeśli nie ma żadnych zduplikowanych wierszy w t1i t2, zapytanie w tej odpowiedzi nie zwraca zestaw wyników, które emuluje FULL OUTER JOIN. Ale w bardziej ogólnym przypadku, na przykład, lista SELECT nie zawiera wystarczającej liczby kolumn / wyrażeń, aby zwracane wiersze były unikalne, wówczas ten wzorzec zapytania jest niewystarczający do odtworzenia zestawu, który zostałby wygenerowany przez FULL OUTER JOIN. Aby uzyskać bardziej wierną emulację, potrzebowalibyśmy UNION ALLoperatora set, a jedno z zapytań wymagałoby wzorca zapobiegającego łączeniu . Komentarz Pavle Lekic (powyżej) podaje prawidłowy wzorzec zapytania.
spencer7593

350

Odpowiedź, którą udzielił Pablo Santa Cruz, jest poprawna; jednak w przypadku, gdy ktoś natknie się na tę stronę i chce więcej wyjaśnień, oto szczegółowy podział.

Przykładowe tabele

Załóżmy, że mamy następujące tabele:

-- t1
id  name
1   Tim
2   Marta

-- t2
id  name
1   Tim
3   Katarina

Wewnętrzne połączenia

Połączenie wewnętrzne, takie jak to:

SELECT *
FROM `t1`
INNER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

Otrzymalibyśmy od nas tylko te rekordy, które pojawiają się w obu tabelach:

1 Tim  1 Tim

Połączenia wewnętrzne nie mają kierunku (jak w lewo lub w prawo), ponieważ są wyraźnie dwukierunkowe - wymagamy dopasowania po obu stronach.

Połączenia zewnętrzne

Z drugiej strony sprzężenia zewnętrzne służą do wyszukiwania rekordów, które mogą nie mieć dopasowania w drugiej tabeli. W związku z tym musisz określić, która strona sprzężenia może mieć brakujący rekord.

LEFT JOINi RIGHT JOINsą skrótami dla LEFT OUTER JOINi RIGHT OUTER JOIN; Wykorzystam ich pełne nazwy poniżej, aby wzmocnić koncepcję złączeń zewnętrznych vs. złączeń wewnętrznych.

Lewy zewnętrzny łącznik

Lewe połączenie zewnętrzne, takie jak to:

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

... dostarczyłby nam wszystkie rekordy z lewej tabeli niezależnie od tego, czy pasują do prawej tabeli, jak poniżej:

1 Tim   1    Tim
2 Marta NULL NULL

Right Outer Join

Prawe połączenie zewnętrzne, takie jak to:

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

... dostarczyłby nam wszystkie rekordy z prawej tabeli, niezależnie od tego, czy pasują do lewej tabeli, jak poniżej:

1    Tim   1  Tim
NULL NULL  3  Katarina

Pełne połączenie zewnętrzne

Pełne sprzężenie zewnętrzne dałoby nam wszystkie rekordy z obu tabel, bez względu na to, czy pasują one do drugiej tabeli, z wartościami NULL po obu stronach, w których nie ma zgodności. Wynik wyglądałby tak:

1    Tim   1    Tim
2    Marta NULL NULL
NULL NULL  3    Katarina

Jednak, jak zauważył Pablo Santa Cruz, MySQL tego nie obsługuje. Możemy to naśladować, wykonując UNION lewego i prawego łączenia, w następujący sposób:

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`

UNION

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

Możesz pomyśleć o tym, UNIONco oznacza „uruchom oba te zapytania, a następnie ułóż wyniki jeden na drugim”; niektóre wiersze będą pochodzić z pierwszego zapytania, a niektóre z drugiego.

Należy zauważyć, że a UNIONw MySQL wyeliminuje dokładne duplikaty: Tim pojawiłby się w obu zapytaniach tutaj, ale wynik UNIONjedynego wypisuje go raz. Mój kolega guru z bazy danych uważa, że ​​nie należy polegać na tym zachowaniu. Aby być bardziej precyzyjnym, możemy dodać WHEREklauzulę do drugiego zapytania:

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`

UNION

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`
WHERE `t1`.`id` IS NULL;

Z drugiej strony, jeśli chcieliśmy zobaczyć duplikaty z jakiegoś powodu, można użyć UNION ALL.


4
W przypadku MySQL naprawdę chcesz unikać używania UNION zamiast UNION ALL, jeśli nie ma nakładania się (patrz komentarz Pavle powyżej). Jeśli możesz dodać więcej informacji na ten temat w swojej odpowiedzi tutaj, myślę, że byłaby to preferowana odpowiedź na to pytanie, ponieważ jest ono bardziej szczegółowe.
Garen

2
Zalecenie od „kolegi z guru bazy danych” jest prawidłowe. Jeśli chodzi o model relacyjny (wszystkie prace teoretyczne wykonane przez Teda Codda i Chrisa Date), zapytanie o ostatnią formę emuluje PEŁNE ZŁĄCZE ZEWNĘTRZNE, ponieważ łączy dwa odrębne zestawy, drugie zapytanie nie wprowadza „duplikatów” ( wiersze już zwrócone przez pierwsze zapytanie), które nie zostałyby wygenerowane przez FULL OUTER JOIN. Nie ma nic złego w robieniu zapytań w ten sposób i użyciu UNION do usuwania tych duplikatów. Ale aby naprawdę powielić FULL OUTER JOIN, potrzebujemy jednego z zapytań, aby być antywirusowym.
spencer7593

1
@IstiaqueAhmed: celem jest emulacja operacji FULL OUTER JOIN. Potrzebujemy tego warunku w drugim zapytaniu, aby zwracało tylko wiersze, które nie mają dopasowania (wzorzec antyzłączenia). Bez tego warunku zapytanie jest złączeniem zewnętrznym ... zwraca wiersze, które pasują, a także wiersze bez dopasowania. Pasujące wiersze zostały już zwrócone przez pierwsze zapytanie. Jeśli drugie zapytanie zwraca te same wiersze (ponownie), zduplikowaliśmy wiersze, a nasz wynik nie będzie równoważny PEŁNEMU ZŁĄCZENIU.
spencer7593,

1
@IstiaqueAhmed: Prawdą jest, że UNIONoperacja usunie te duplikaty; ale usuwa również WSZYSTKIE zduplikowane wiersze, w tym zduplikowane wiersze, które byłyby zwrócone przez PEŁNE ZŁĄCZE ZEWNĘTRZNE. Aby emulować a FULL JOIN b, prawidłowy wzór to (a LEFT JOIN b) UNION ALL (b ANTI JOIN a).
spencer7593,

1
Bardzo zwięzła odpowiedź z doskonałym wyjaśnieniem. Dzięki za to.
Najeeb

35

Użycie unionzapytania usunie duplikaty, a to różni się od zachowania, full outer joinktóre nigdy nie usuwa duplikatów:

[Table: t1]                            [Table: t2]
value                                  value
-------                                -------
1                                      1
2                                      2
4                                      2
4                                      5

Jest to oczekiwany wynik full outer join:

value | value
------+-------
1     | 1
2     | 2
2     | 2
Null  | 5
4     | Null
4     | Null

Jest to wynikiem zastosowania leftoraz right Joinz union:

value | value
------+-------
Null  | 5 
1     | 1
2     | 2
4     | Null

[SQL Fiddle]

Moje sugerowane zapytanie to:

select 
    t1.value, t2.value
from t1 
left outer join t2  
  on t1.value = t2.value
union all      -- Using `union all` instead of `union`
select 
    t1.value, t2.value
from t2 
left outer join t1 
  on t1.value = t2.value
where 
    t1.value IS NULL 

Wynik powyższego zapytania, który jest taki sam jak oczekiwany wynik:

value | value
------+-------
1     | 1
2     | 2
2     | 2
4     | NULL
4     | NULL
NULL  | 5

[SQL Fiddle]


@ Steve Chambers : [Od komentarzy, z wielkimi podziękowaniami!]
Uwaga: To może być najlepsze rozwiązanie, zarówno pod względem wydajności, jak i generowania takich samych wyników jak FULL OUTER JOIN. Ten post na blogu również wyjaśnia to dobrze - cytując z metody 2: „To poprawnie obsługuje zduplikowane wiersze i nie zawiera niczego, czego nie powinien. Konieczne jest użycie UNION ALLzamiast zwykłego UNION, co wyeliminowałoby duplikaty, które chcę zachować. To może być znacznie bardziej wydajny w przypadku dużych zestawów wyników, ponieważ nie ma potrzeby sortowania i usuwania duplikatów ”.


Postanowiłem dodać kolejne rozwiązanie, które pochodzi z full outer joinwizualizacji i matematyki, nie jest lepsze niż powyższe, ale bardziej czytelne:

Pełne sprzężenie zewnętrzne oznacza (t1 ∪ t2): wszystko w t1lub wewnątrz t2
(t1 ∪ t2) = (t1 ∩ t2) + t1_only + t2_only: wszystko w jednym t1i t2plus wszystko w t1tym, czego nie ma, t2a plus wszystko w, t2czego nie ma w t1:

-- (t1 ∩ t2): all in both t1 and t2
select t1.value, t2.value
from t1 join t2 on t1.value = t2.value    
union all  -- And plus 
-- all in t1 that not exists in t2
select t1.value, null
from t1
where not exists( select 1 from t2 where t2.value = t1.value)    
union all  -- and plus
-- all in t2 that not exists in t1
select null, t2.value
from t2
where not exists( select 1 from t1 where t2.value = t1.value)

[SQL Fiddle]


Robimy te same czasy holowania zadań. Jeśli istnieje zapytanie podrzędne dla t1 i t2, to mysql musi wykonać to samo zadanie więcej razy, prawda? Czy możemy usunąć to za pomocą aliasu w tej sytuacji?:
Kabir Hossain

Proponuję użyć tymczasowych tabel;).
shA.t

5
Ta metoda wydaje się być najlepszym rozwiązaniem, zarówno pod względem wydajności, jak i generowania takich samych wyników jak FULL OUTER JOIN. Ten post na blogu również to dobrze wyjaśnia - cytując z metody 2: „To poprawnie obsługuje zduplikowane wiersze i nie zawiera niczego, czego nie powinno. Konieczne jest użycie UNION ALL zamiast zwykłego UNION, co wyeliminowałoby duplikaty, które chcę zachowaj. Może to być znacznie bardziej wydajne w przypadku dużych zestawów wyników, ponieważ nie ma potrzeby sortowania i usuwania duplikatów ”.
Steve Chambers

2
@ SteveChambers jest już za późno, ale dziękuję za komentarz. Dodałem twój komentarz, aby następnie odpowiedzieć na wyróżnione więcej, jeśli nie zgadzasz się, przywróć go;).
shA.t

Nie ma problemu @ shA.t - IMO powinno naprawdę mieć więcej głosów pozytywnych i / lub być przyjętą odpowiedzią.
Steve Chambers

6

MySql nie ma składni FULL-OUTER-JOIN. Musisz naśladować, wykonując LEWE DOŁĄCZ i PRAWE DOŁĄCZ w następujący sposób -

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id  
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

Ale MySql nie ma również PRAWEJ składni. Według MySql's zewnętrznym uproszczeniem łączenia , prawe złączenie jest konwertowane na równoważne lewe łączenie, przełączając t1 i t2 w klauzuli FROMi ONw zapytaniu. Tak więc MySql Query Optimizer tłumaczy oryginalne zapytanie na następujące -

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id  
UNION
SELECT * FROM t2
LEFT JOIN t1 ON t2.id = t1.id

Teraz pisanie oryginalnego zapytania nie jest szkodliwe, ale powiedz, jeśli masz predykaty takie jak klauzula WHERE, która jest predykatem przed dołączeniem lub predykatem AND wONklauzuli, która jestpredykatem podczas łączenia , może chcieć spojrzeć na diabła; co jest szczegółowo.

Optymalizator zapytań MySql rutynowo sprawdza predykaty, jeśli są odrzucane z null . Definicja i przykłady odrzucone Teraz, jeśli wykonałeś PRAWIDŁOWE DOŁĄCZENIE, ale z GDZIE predykatem w kolumnie od t1, możesz być narażony na ryzyko wystąpienia scenariusza z zerową wartością odrzuconą .

Na przykład następujące zapytanie -

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'

zostanie przetłumaczone na następujące przez Optymalizator zapytań-

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'
UNION
SELECT * FROM t2
LEFT JOIN t1 ON t2.id = t1.id
WHERE t1.col1 = 'someValue'

Kolejność tabel zmieniła się, ale predykat jest nadal stosowany do t1, ale t1 znajduje się teraz w klauzuli „ON”. Jeśli t1.col1 jest zdefiniowany jakoNOT NULL kolumna, to zapytanie zostanie odrzucone z .

Każde sprzężenie zewnętrzne (lewy, prawy, pełny), które jest odrzucane z null, jest konwertowane na łączenie wewnętrzne przez MySql.

Tak więc wyniki, których możesz się spodziewać, mogą być całkowicie różne od tego, co zwraca MySql. Możesz pomyśleć, że to błąd w RIGHT JOIN MySql, ale to nie w porządku. Właśnie tak działa optymalizator zapytań MySql. Więc odpowiedzialny za programista musi zwracać uwagę na te niuanse podczas konstruowania zapytania.


4

W SQLite powinieneś to zrobić:

SELECT * 
FROM leftTable lt 
LEFT JOIN rightTable rt ON lt.id = rt.lrid 
UNION
SELECT lt.*, rl.*  -- To match column set
FROM rightTable rt 
LEFT JOIN  leftTable lt ON lt.id = rt.lrid

Czy możemy tego użyć? jak w: WYBIERZ * Z lewej tabeli LEWE DOŁĄCZ prawa strona Tabela rt WŁ. lt.id = rt. siatka UNION WYBIERZ lt. *, rl. * - Aby dopasować zestaw kolumn Z lewej tabeli Tabela PRAWE DOŁĄCZ prawy stół rt WŁ. lt.id = rt. lewa ;
Kabir Hossain

tak, ale SQLite nie obsługuje
poprawnych

4

Żadna z powyższych odpowiedzi nie jest w rzeczywistości poprawna, ponieważ nie są zgodne z semantyką, gdy występują zduplikowane wartości.

W przypadku zapytania takiego jak (z tego duplikatu ):

SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.Name = t2.Name;

Prawidłowy odpowiednik to:

SELECT t1.*, t2.*
FROM (SELECT name FROM t1 UNION  -- This is intentionally UNION to remove duplicates
      SELECT name FROM t2
     ) n LEFT JOIN
     t1
     ON t1.name = n.name LEFT JOIN
     t2
     ON t2.name = n.name;

Jeśli potrzebujesz, aby działało to z NULLwartościami (które mogą być również konieczne), użyj zamiast tego NULLoperatora porównywania -safe .<=>=


3
jest to często dobre rozwiązanie, ale może dawać inne wyniki niż za FULL OUTER JOINkażdym razem, gdy namekolumna jest pusta. union allZapytanie z anty-przyłączenia wzór powinien odtworzenia sprzężenia zewnętrznego zachowania prawidłowo, a który to roztwór jest bardziej odpowiedni w zależności od kontekstu i ograniczeń, które są aktywne w tabelach.
fthiella

@fthiella. . . To dobra uwaga. Poprawiłem odpowiedź.
Gordon Linoff,

1
okej, ale operator porównania zerowo-bezpieczny sprawi, że złączenie się powiedzie, co różni się od pełnego zachowania zewnętrznego łączenia w przypadku, gdy masz null nazwy zarówno w t1, jak i w t2
fthiella

@fthiella. . . Będę musiał wymyślić najlepszy sposób na zrobienie tego. Ale biorąc pod uwagę, jak błędna jest zaakceptowana odpowiedź, prawie wszystko jest bliższe właściwej odpowiedzi. (Ta odpowiedź jest po prostu błędna, jeśli po obu stronach znajduje się wiele kluczy.)
Gordon Linoff,

1
tak, zaakceptowana odpowiedź jest nieprawidłowa, jako ogólne rozwiązanie, myślę, że jest poprawna w użyciu union all, ale ta odpowiedź pomija wzór zapobiegający łączeniu w pierwszym lub drugim zapytaniu, które zachowa istniejące duplikaty, ale uniemożliwi dodanie nowych. W zależności od kontekstu inne rozwiązania (takie jak to) mogą być bardziej odpowiednie.
fthiella,

3

Zmodyfikowano zapytanie shA.t dla większej przejrzystości:

-- t1 left join t2
SELECT t1.value, t2.value
FROM t1 LEFT JOIN t2 ON t1.value = t2.value   

    UNION ALL -- include duplicates

-- t1 right exclude join t2 (records found only in t2)
SELECT t1.value, t2.value
FROM t1 RIGHT JOIN t2 ON t1.value = t2.value
WHERE t2.value IS NULL 

3

Możesz wykonać następujące czynności:

(SELECT 
    *
FROM
    table1 t1
        LEFT JOIN
    table2 t2 ON t1.id = t2.id
WHERE
    t2.id IS NULL)
UNION ALL
 (SELECT 
    *
FROM
    table1 t1
        RIGHT JOIN
    table2 t2 ON t1.id = t2.id
WHERE
    t1.id IS NULL);

1

co powiedziałeś o rozwiązaniu Cross Join ?

SELECT t1.*, t2.*
FROM table1 t1
INNER JOIN table2 t2 
ON 1=1;

2
Nie, to połączenie krzyżowe. Dopasuje każdy wiersz w t1 do każdego wiersza w t2, uzyskując zestaw wszystkich możliwych kombinacji, z select (select count(*) from t1) * (select count(*) from t2))wierszami w zestawie wyników.
Marc L.,

Chociaż ten kod może odpowiedzieć na pytanie, zapewnienie dodatkowego kontekstu dotyczącego tego, jak i dlaczego rozwiązuje problem, poprawiłoby długoterminową wartość odpowiedzi.
Alexander

Który dodatek może być pomocny? może na przykład?
Super Mario

0
SELECT
    a.name,
    b.title
FROM
    author AS a
LEFT JOIN
    book AS b
    ON a.id = b.author_id
UNION
SELECT
    a.name,
    b.title
FROM
    author AS a
RIGHT JOIN
    book AS b
    ON a.id = b.author_id

0

Jest to również możliwe, ale musisz zaznaczyć te same nazwy pól w select.

SELECT t1.name, t2.name FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT t1.name, t2.name FROM t2
LEFT JOIN t1 ON t1.id = t2.id

To tylko powielanie wyników z lewego złączenia.
Mateusz

-1

Naprawiam odpowiedź i prace obejmują wszystkie wiersze (na podstawie odpowiedzi Pavle Lekic)

    (
    SELECT a.* FROM tablea a
    LEFT JOIN tableb b ON a.`key` = b.key
    WHERE b.`key` is null
    )
    UNION ALL
    (
    SELECT a.* FROM tablea a
    LEFT JOIN tableb b ON a.`key` = b.key
    where  a.`key` = b.`key`
    )
    UNION ALL
    (
    SELECT b.* FROM tablea a
    right JOIN tableb b ON b.`key` = a.key
    WHERE a.`key` is null
    );

Nie, jest to rodzaj łączenia „tylko na zewnątrz”, które zwróci tylko wiersze tablea, w których nie ma dopasowania tablebi na odwrót. Próbujesz UNION ALL, co zadziałałoby tylko wtedy, gdy te dwie tabele mają równo uporządkowane kolumny, co nie jest gwarantowane.
Marc L.,

to działa, tworzę na bazie danych temp tablea (1,2,3,4,5,6) i tableb (4,5,6,7,8,9) jej wiersze mają 3 kolumny „id”, „number” i „nazwa_numer” jako tekst i działa w rezultacie tylko (1,2,3,7,8,9)
Rubén Ruíz

1
To nie jest zewnętrzne połączenie. Sprzężenie zewnętrzne obejmuje również pasujących członków.
Marc L.

to nowe zdanie ma wszystkie wyniki 1,2, ..., 9
Rubén Ruíz

-2

Odpowiedź:

SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id;

Można odtworzyć w następujący sposób:

 SELECT t1.*, t2.* 
 FROM (SELECT * FROM t1 UNION SELECT name FROM t2) tmp
 LEFT JOIN t1 ON t1.id = tmp.id
 LEFT JOIN t2 ON t2.id = tmp.id;

Zastosowanie odpowiedzi UNION lub UNION ALL nie obejmuje przypadku na brzegu, w którym tabele podstawowe mają zduplikowane wpisy.

Wyjaśnienie:

Istnieje przypadek skrajny, którego UNION lub UNION ALL nie są w stanie objąć. Nie możemy przetestować tego na mysql, ponieważ nie obsługuje on FULL OUTER JOIN, ale możemy to zilustrować w bazie danych, która go obsługuje:

 WITH cte_t1 AS
 (
       SELECT 1 AS id1
       UNION ALL SELECT 2
       UNION ALL SELECT 5
       UNION ALL SELECT 6
       UNION ALL SELECT 6
 ),
cte_t2 AS
(
      SELECT 3 AS id2
      UNION ALL SELECT 4
      UNION ALL SELECT 5
      UNION ALL SELECT 6
      UNION ALL SELECT 6
)
SELECT  *  FROM  cte_t1 t1 FULL OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2;

This gives us this answer:

id1  id2
1  NULL
2  NULL
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

Rozwiązanie UNION:

SELECT  * FROM  cte_t1 t1 LEFT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2
UNION    
SELECT  * FROM cte_t1 t1 RIGHT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2

Podaje niepoprawną odpowiedź:

 id1  id2
NULL  3
NULL  4
1  NULL
2  NULL
5  5
6  6

Rozwiązanie UNION ALL:

SELECT  * FROM cte_t1 t1 LEFT OUTER join cte_t2 t2 ON t1.id1 = t2.id2
UNION ALL
SELECT  * FROM  cte_t1 t1 RIGHT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2

Jest również niepoprawny.

id1  id2
1  NULL
2  NULL
5  5
6  6
6  6
6  6
6  6
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

Podczas gdy to zapytanie:

SELECT t1.*, t2.*
FROM (SELECT * FROM t1 UNION SELECT name FROM t2) tmp 
LEFT JOIN t1 ON t1.id = tmp.id 
LEFT JOIN t2 ON t2.id = tmp.id;

Daje następujące:

id1  id2
1  NULL
2  NULL
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

Kolejność jest inna, ale poza tym odpowiada poprawnej odpowiedzi.


To urocze, ale wprowadza w błąd UNION ALLrozwiązanie. Przedstawia również rozwiązanie, w UNIONktórym wolniej działałoby na dużych tabelach źródłowych z powodu wymaganego usuwania duplikacji. Wreszcie nie skompiluje się, ponieważ pole idnie istnieje w podzapytaniu tmp.
Marc L.,

Nigdy nie twierdziłem, że chodzi o prędkość, a OP nie wspomniało nic o prędkości. Zakładając, że UNION WSZYSTKO (nie polegasz na określeniu, który z nich) i to daje poprawną odpowiedź, jeśli chcielibyśmy stwierdzić, że jedno jest szybsze, musielibyśmy podać punkty odniesienia, a to byłoby dygresją od PO pytanie.
Angelos

Jeśli chodzi o spostrzeżenie o braku identyfikatora w pod-zapytaniu, poprawiłem literówkę - dziękuję za zwrócenie na to uwagi. Twoje roszczenie dotyczące wprowadzenia w błąd jest niejasne - jeśli możesz podać więcej informacji, mogę to rozwiązać. Na twoim ostatnim spostrzeżeniu na temat bystrości, nie mam komentarza, wolałbym skupić się na logice sql.
Angelos

3
Wprowadza w błąd: „ UNION ALLRozwiązanie:… jest również niepoprawne”. Prezentowany kod pomija wykluczenie przecięcia z funkcji right-join ( where t1.id1 is null), którą należy podać w pliku UNION ALL. Oznacza to, że Twoje rozwiązanie przebija wszystkie pozostałe, tylko wtedy, gdy jedno z tych innych rozwiązań zostanie nieprawidłowo zaimplementowane. Na „uroczość”, punkt. To było za darmo, przepraszam.
Marc L.,

-3

Średnia SQL mówi full join onto inner join onwiersze union allniedopasowane wiersze lewej tabeli udzielonych przez null union allprawy wiersze tabeli przedłużony o wartości null. Tzn. inner join onRzędy w union allrzędach, left join onale nie w inner join on union allrzędach, right join onale nie inner join on.

Tzn. left join onWierszy union all right join onnie ma inner join on. Lub jeśli wiesz, że twój inner join onwynik nie może mieć wartości null w konkretnej prawej kolumnie tabeli, wówczas „ right join onwiersze nie w inner join on” to wiersze right join onz onwarunkiem rozszerzonym przez andtę kolumnę is null.

Tj. Podobnie right join on union allodpowiednie left join onrzędy.

Od Jaka jest różnica między „INNER JOIN” a „OUTER JOIN”? :

(SQL Standard 2006 SQL / Foundation 7.7 Reguły składniowe 1, reguły ogólne 1 b, 3 c i d, 5 b.)

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.