Jak zrobić FULL OUTER JOIN w MySQL?

Odpowiedź, którą Pablo Santa Cruz dał jest poprawna; jednak w przypadku, gdy ktoś natknął 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

Połączenia Wewnętrzne

Wewnętrzne połączenie, jak to:

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

Otrzymamy tylko rekordy, które pojawiają się w obu tabelach, jak to:

1 Tim  1 Tim

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

Złącza Zewnętrzne

Zewnętrzne połączenia służą natomiast do znajdowania rekordów, które mogą nie mieć dopasowania w innej tabeli. W związku z tym musisz określić , która strona join ma prawo mieć brakujący rekord.

LEFT JOIN i {[11] } są skrótem od LEFT OUTER JOIN i RIGHT OUTER JOIN; użyję ich pełnych nazw poniżej, aby wzmocnić koncepcję połączeń zewnętrznych vs połączeń wewnętrznych.

Lewa Zewnętrzna Join

Lewy zewnętrzny łącznik, tak:

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

...dałoby nam wszystkie rekordy z lewej tabeli, niezależnie od tego, czy mają mecz w prawej tabeli, jak to:

1 Tim   1    Tim
2 Marta NULL NULL

Prawy Zewnętrzny Łącznik

Prawe złącze zewnętrzne, jak to:

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

...dałoby nam wszystkie rekordy z prawej tabeli, niezależnie od tego, czy mają mecz w lewej tabeli, jak to:

1    Tim   1  Tim
NULL NULL  3  Katarina

Pełne Połączenie Zewnętrzne

Pełne połączenie zewnętrzne dałoby nam wszystkie rekordy z obu tabel, czy mają mecz w drugiej tabeli, z Null po obu stronach, gdzie nie ma meczu. 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 go naśladować, wykonując połączenie lewego i prawego połączenia, jak to: {]}

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 myśleć o UNION jako o "uruchom oba te zapytania, a następnie stos wyniki jeden na drugim"; niektóre wiersze będzie pochodzić z pierwszego zapytania, a niektóre z drugiego.

Należy zauważyć, że {[14] } w MySQL wyeliminuje dokładne duplikaty: Tim pojawi się w obu zapytaniach tutaj, ale wynik UNION wymienia go tylko raz. Mój kolega z bazy danych Guru uważa, że na takim zachowaniu nie należy polegać. Aby być bardziej wyraźnym, możemy dodać klauzulę WHERE 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ślichcesz zobaczyć duplikaty dla niektórych reason, you could use UNION ALL.

W przeciwieństwie do tego, w jaki sposób można usunąć duplikaty, nie ma to wpływu na zachowanie full outer join, które nigdy nie usuwa duplikatów:

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

Oto oczekiwany wynik full outer join:

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

Jest to wynik użycia left i right Join z 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 oczekiwano wynik:

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

[SQL Fiddle]

@Steve Chambers: ^{[Z komentarzy, z podziękowaniami!]}
Uwaga: {[38] } może to być najlepsze rozwiązanie, zarówno pod względem wydajności, jak i generowania tych samych wyników co FULL OUTER JOIN. ten wpis na blogu również dobrze to wyjaśnia - cytując z metody 2: "to poprawnie obsługuje duplikaty wierszy i nie zawiera niczego, czego nie powinno. konieczne jest użycie UNION ALL zamiast zwykłego UNION, co wyeliminowałoby duplikaty, które chcę zachować. Może to być znacznie bardziej efektywne 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 lepiej, że powyżej, ale bardziej czytelne:

Full outer join oznacza (t1 ∪ t2): all in t1 Lub in t2
(t1 ∪ t2) = (t1 ∩ t2) + t1_only + t2_only: Wszystko w t1 i t2 plus wszystko w t1, które nie są w t2 i plus wszystko w t2 które nie są 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]

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 poprawna, ponieważ nie podąża za semantyką, gdy istnieją zduplikowane wartości.

Dla zapytania takiego jak (z tego duplikat):

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

Poprawnym odpowiednikiem jest:

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 to działało z wartościami NULL (które również mogą być konieczne), użyj NULL-bezpiecznego operatora porównania, <=> zamiast =.

Zmodyfikowany shA.zapytanie t dla większej jasnoś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 

MySql nie ma składni FULL-OUTER-JOIN. Musisz emulować, wykonując zarówno lewe, jak i prawe połączenie 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 również nie ma poprawnej składni JOIN. Zgodnie z uproszczeniem zewnętrznego join MySql, prawe join jest konwertowane na równoważne lewe join poprzez zamianę t1 i T2 w klauzuli FROM i ON w zapytaniu. W ten sposób MySql Query Optimizer przekłada 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 nie ma szkoda w pisaniu oryginalnego zapytania tak jak jest, ale powiedzmy, że masz predykaty takie jak klauzula WHERE, która jest przed-Dołącz predykat lub an i predykat na ON klauzuli, która jest podczas-Dołącz predykat, to może warto spojrzeć na diabła; który jest w szczegółach.

MySql Query optimizer rutynowo sprawdza predykaty, jeśli są odrzucone przez null . Teraz, jeśli zrobiłeś dobrze dołączyć, ale z gdzie predykat na kolumnie z t1, wtedy możesz być narażony na ryzyko wystąpienia scenariusza odrzuconego przez null .

Na przykład, Poniższe 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'

Zostaje 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'

Tak więc kolejność tabel się zmieniła, ale predykat jest nadal stosowany do t1, ale t1 jest teraz w klauzuli "ON". Jeśli t1.col1 jest zdefiniowany jako NOT NULL column, then this query will be null-rejected .

Any outer-join (left, right, pełne) to jest null-odrzucone jest konwertowane do połączenia wewnętrznego przez MySql.

Tak więc wyniki, których możesz się spodziewać, mogą być zupełnie inne od tych, które zwraca MySql. Możesz pomyśleć, że to błąd z właściwym połączeniem MySql, ale to nie w porządku. To jak MySQL query-optimizer działa. Tak więc deweloper odpowiedzialny musi zwracać uwagę na te niuanse podczas konstruowania zapytania.

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);

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

Co powiedziałeś o Cross join rozwiązanie?

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

Jest to również możliwe, ale musisz podać 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

Mysql jako taki nie obsługuje żadnych poleceń o nazwie FULL OUTER JOIN. Obsługiwane są trzy połączenia: połączenie wewnętrzne,połączenie lewe i połączenie prawe.

Możesz jednak zaimplementować full outer join używając polecenia UNION jako
(left join query) UNION (right join query)

Na przykład, rozważ poniższy przykład, w którym mam dwie tabele studentów i marks. Aby wykonać pełne zewnętrzne połączenie, wykonałbym następujący kod:

SELECT * FROM students  
LEFT JOIN marks   
ON students.id = marks.id  
UNION ALL  
SELECT * FROM students 
RIGHT JOIN marks  
ON students.id = marks.id;

Poprawiam odpowiedź, i działa zawierają 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
    );

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;

Użycie odpowiedzi UNION lub UNION ALL nie obejmuje przypadku krawędzi, w którym tabele bazowe mają zduplikowane wpisy.

Explanation:

Istnieje przypadek skrajny, którego Unia lub wszystkie związki nie mogą objąć. Nie możemy tego przetestować na mysql, ponieważ nie obsługuje on pełnych połączeń zewnętrznych, ale możemy to zilustrować na 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

Unia rozwiązanie:

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

Daje niepoprawną odpowiedź:

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

The UNION ALL solution:

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ż niepoprawne.

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:

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.

Standard SQL mówi full join on jest inner join on rows union all niepasowane wiersze lewej tabeli rozszerzone o nulls union all wiersze prawej tabeli rozszerzone o nulls. Ie inner join on wiersze union all wiersze w left join on ale nie inner join on union all wiersze w right join on, ale nie inner join on.

Ie left join on wiersze union all right join on wiersze nie w inner join on. Lub jeśli wiesz, że twój inner join on wynik nie może mieć null w konkretnej kolumnie prawej tabeli, to" right join on wiersze nie w inner join on " są wierszami w right join on z warunkiem on rozszerzonym o and Ta kolumna is null.

Ie podobnie right join on union all odpowiednie left join on wiersze.

Od Jaka jest różnica między "wewnętrznym połączeniem" a "zewnętrznym połączeniem"?:

W 2006 roku firma SQL wprowadziła na rynek nową wersję SQL-A, która została zaprojektowana przez firmę SQL-a w 2007 roku.]}