Chcę wykonać Full Outer Join w MySQL. czy to możliwe? Czy MySQL obsługuje pełne przyłączenie zewnętrzne?
Chcę wykonać Full Outer Join w MySQL. czy to możliwe? Czy MySQL obsługuje pełne przyłączenie zewnętrzne?
Odpowiedzi:
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 UNION
ustawionego 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
(SELECT ... FROM tbl1 LEFT JOIN tbl2 ...) UNION ALL (SELECT ... FROM tbl1 RIGHT JOIN tbl2 ... WHERE tbl1.col IS NULL)
t1
i 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 ALL
operatora set, a jedno z zapytań wymagałoby wzorca zapobiegającego łączeniu . Komentarz Pavle Lekic (powyżej) podaje prawidłowy wzorzec zapytania.
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ł.
Załóżmy, że mamy następujące tabele:
-- t1
id name
1 Tim
2 Marta
-- t2
id name
1 Tim
3 Katarina
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.
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 JOIN
i RIGHT JOIN
są skrótami dla LEFT OUTER JOIN
i RIGHT OUTER JOIN
; Wykorzystam ich pełne nazwy poniżej, aby wzmocnić koncepcję złączeń zewnętrznych vs. złączeń wewnętrznych.
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
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 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, UNION
co 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 UNION
w MySQL wyeliminuje dokładne duplikaty: Tim pojawiłby się w obu zapytaniach tutaj, ale wynik UNION
jedynego 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ć WHERE
klauzulę 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
.
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.
UNION
operacja 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)
.
Użycie union
zapytania usunie duplikaty, a to różni się od zachowania, full outer join
któ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 left
oraz right Join
z union
:
value | value
------+-------
Null | 5
1 | 1
2 | 2
4 | Null
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
@ 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 jakFULL 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życieUNION ALL
zamiast zwykłegoUNION
, 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 join
wizualizacji i matematyki, nie jest lepsze niż powyższe, ale bardziej czytelne:
Pełne sprzężenie zewnętrzne oznacza
(t1 ∪ t2)
: wszystko wt1
lub wewnątrzt2
(t1 ∪ t2) = (t1 ∩ t2) + t1_only + t2_only
: wszystko w jednymt1
it2
plus wszystko wt1
tym, czego nie ma,t2
a plus wszystko w,t2
czego nie ma wt1
:
-- (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)
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 ”.
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 FROM
i ON
w 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 wON
klauzuli, 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 . 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.
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
Ż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 NULL
wartościami (które mogą być również konieczne), użyj zamiast tego NULL
operatora porównywania -safe .<=>
=
FULL OUTER JOIN
każdym razem, gdy name
kolumna jest pusta. union all
Zapytanie 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.
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.
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
co powiedziałeś o rozwiązaniu Cross Join ?
SELECT t1.*, t2.*
FROM table1 t1
INNER JOIN table2 t2
ON 1=1;
select (select count(*) from t1) * (select count(*) from t2))
wierszami w zestawie wyników.
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
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
);
tablea
, w których nie ma dopasowania tableb
i 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.
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.
UNION ALL
rozwiązanie. Przedstawia również rozwiązanie, w UNION
którym wolniej działałoby na dużych tabelach źródłowych z powodu wymaganego usuwania duplikacji. Wreszcie nie skompiluje się, ponieważ pole id
nie istnieje w podzapytaniu tmp
.
UNION ALL
Rozwią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.
Średnia SQL mówi full join on
to inner join on
wiersze union all
niedopasowane wiersze lewej tabeli udzielonych przez null union all
prawy wiersze tabeli przedłużony o wartości null. Tzn. inner join on
Rzędy w union all
rzędach, left join on
ale nie w inner join on
union all
rzędach, right join on
ale nie inner join on
.
Tzn. left join on
Wierszy union all
right join on
nie ma inner join on
. Lub jeśli wiesz, że twój inner join on
wynik nie może mieć wartości null w konkretnej prawej kolumnie tabeli, wówczas „ right join on
wiersze nie w inner join on
” to wiersze right join on
z on
warunkiem rozszerzonym przez and
tę kolumnę is null
.
Tj. Podobnie right join on
union all
odpowiednie left join on
rzę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.)