Как сделать полное внешнее соединение в MySQL?

ответ Пабло Санта Крус дал правильно; однако, в случае, если кто-то наткнулся на этой странице и хочет больше разъяснений, вот подробный разбивка.

Таблицы

предположим, что у нас есть следующие таблицы:

-- t1
id  name
1   Tim
2   Marta

-- t2
id  name
1   Tim
3   Katarina

Внутреннее Соединение

внутреннее соединение, как это:

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

мы получим только записи, которые появляются в обеих таблицах, например:

1 Tim  1 Tim

внутренние соединения не имеют направления (например, влево или вправо), потому что они явно двунаправленные - нам требуется совпадение с обеих сторон.

Внешние Соединения

внешние соединения, с другой стороны, предназначены для поиска записей, которые могут не совпадать в другой таблице. Таким образом, вы должны указать С какой стороны из соединения допускается наличие отсутствующей записи.

LEFT JOIN и RIGHT JOIN это сокращение для LEFT OUTER JOIN и RIGHT OUTER JOIN; Я буду использовать их полные имена ниже чтобы укрепить концепцию внешних соединений против внутренних соединений.

Левое Внешнее Соединение

левое внешнее соединение, например:

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

...мы получим все записи из левой таблицы независимо от того, есть ли у них совпадение в правой таблице, например:

1 Tim   1    Tim
2 Marta NULL NULL

Правое Внешнее Соединение

правое внешнее соединение, например:

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

...нас достанут все записи из правой таблицы, независимо от есть ли у них совпадение в левой таблице, например:

1    Tim   1  Tim
NULL NULL  3  Katarina

Полное Внешнее Соединение

полное внешнее соединение даст нам все записи из обеих таблиц, независимо от того, есть ли у них совпадение в другой таблице, с нулями с обеих сторон, где нет совпадения. Результат будет выглядеть так:

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

однако, как указал Пабло Санта-Круз, MySQL не поддерживает это. Мы можем эмулировать его, выполняя объединение левого соединения и правого соединения, например это:

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

вы можете думать о UNION в значении "выполнить оба этих запроса, а затем сложить результаты друг на друга"; некоторые из строк будут поступать из первого запроса, а некоторые из второго.

следует отметить, что a UNION в MySQL будут устранены точные дубликаты: Тим появится в обоих запросах здесь, но результат UNION только перечисляет его один раз. Мой коллега-гуру базы данных считает, что на это поведение нельзя полагаться. Поэтому, чтобы быть более явным об этом, мы могли бы добавить WHERE предложение ко второму запросу:

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;

С другой стороны, если вы хотел чтобы увидеть дубликаты по какой-то причине, вы можете использовать UNION ALL.

С помощью union запрос удаляет дубликаты, и это отличается от поведения full outer join это никогда не удаляет дубликат:

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

ожидаемый результат full outer join:

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

это результат использования left и right Join С union:

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

[SQL Fiddle]

мой предлагаемый запрос:

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 

результат вышеуказанного запроса, который является таким же, как ожидаемый результат:

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

[SQL Fiddle]

---

@Steve Chambers:^{[из комментариев, большое спасибо!]}
Примечание: это может быть лучшим решением, как для КПД и для получения тех же результатов как FULL OUTER JOIN. этот блог также объясняет это хорошо-цитата из Метода 2:"это обрабатывает повторяющиеся строки правильно и не включать ничего не надо. Это необходимо, чтобы использовать UNION ALL вместо простого UNION, что позволит устранить дубликаты, которые я хочу сохранить. Это может быть значительно более эффективным на больших результирующих наборов, так как нет необходимости сортировать и удалять дубликаты."

---

я решил добавить еще одно решение, которое приходит от full outer join визуализация и математика, это не лучше, что выше, но более читабельным:

полное внешнее соединение означает (t1 ∪ t2): все t1 или t2
(t1 ∪ t2) = (t1 ∩ t2) + t1_only + t2_only: все как t1 и t2 плюс все в t1 не в t2 и плюс все в t2 не в 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]

в SQLite, вы должны сделать это:

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

ни один из приведенных выше ответов на самом деле не является правильным, потому что они не следуют семантике, когда есть дублированные значения.

для запроса, такого как (от этого дублировать):

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

правильный эквивалент:

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;

Если вам нужно для этого работать с NULL значения (которые также могут быть необходимы), затем используйте NULL-безопасный сравнения оператор <=>, а не =.

измененный shA.запрос t для большей ясности:

-- 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 не имеет синтаксиса полного внешнего соединения. Вы должны эмулировать, делая как левое соединение, так и правое соединение следующим образом -

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

но MySql также не имеет правильного синтаксиса соединения. Согласно MySql внешнее соединение упрощение, правое соединение преобразуется в эквивалентное левое соединение путем переключения t1 и t2 в FROM и ON предложение в запросе. Таким образом, оптимизатор запросов MySql преобразует исходный запрос в следующий -

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

теперь нет никакого вреда в написании исходного запроса как есть, но скажем, если у вас есть предикаты, такие как предложение WHERE, которое является before-join предикат или и предикат на ON предложение, которое является во время-join предикат, тогда вы можете взглянуть на дьявола; который находится в деталях.

оптимизатор запросов MySql регулярно проверяет предикаты, если они есть null-rejected. Теперь, если вы сделали правильное соединение, но с предикатом WHERE в столбце из t1, то вы можете столкнуться с риском столкнуться с null-rejected сценарий.

например, Следующий запрос -

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'

переводится оптимизатором запросов в следующий формат -

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'

таким образом, порядок таблиц изменился, но предикат по-прежнему применяется к t1, но t1 теперь находится в 'О' предложении. Если t1.col1 определяется как NOT NULL столбец, то этот запрос будет null-rejected.

любое внешнее соединение (слева, справа, полное), которое null-rejected преобразуется во внутреннее соединение MySql.

таким образом, результаты, которые вы могли бы ожидать, могут полностью отличаться от того, что возвращает MySql. Вы можете подумать, что это ошибка с правильным соединением MySql, но это не так. Его просто, как MySql query-optimizer работает. Так ответственный разработчик должен обратить внимание на эти нюансы при построении запроса.

вы можете сделать следующее:

(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

что ты сказал о Cross join решение?

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

это также возможно, но вы должны упомянуть те же имена полей в 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 как таковой не поддерживает ни одну команду с именем FULL OUTER JOIN. Поддерживаются три соединения: внутреннее соединение, левое соединение и правое соединение.

однако вы можете реализовать полное внешнее соединение с помощью команды UNION as
(левое соединение запрос) Союз (право вступать запроса)

например, рассмотрим следующий пример, где у меня есть две таблицы students и marks. Чтобы выполнить полное внешнее соединение, я бы выполнил следующий код:

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

я фиксирую ответ, и работы включают все строки (на основе ответа Павла Лекича)

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

ответ:

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

можно воссоздать следующим образом:

 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;

использование UNION или UNION ALL answer не охватывает крайний случай, когда базовые таблицы дублируют записи.

объяснение:

существует крайний случай, который Союз или союз все не могут покрыть. Мы не можем проверить это на mysql, поскольку он не поддерживает полные внешние соединения, но мы можем проиллюстрировать это на базе данных, которая поддерживает это:

 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

решение Союза:

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

дает неверный ответ:

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

объединение всех решений:

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

тоже неверно.

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

тогда как этот запрос:

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;

дает следующее:

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

порядок отличается, но в остальном соответствует правильному ответу.

стандарт SQL говорит full join on и inner join on строки union all непревзойденные левые строки таблицы, расширенные нулями union all правые строки таблицы расширены нулями. Т. е. inner join on строки union all строки left join on а не inner join onunion all строки right join on а не inner join on.

Ie left join on строки union allright join on строки не inner join on. Или если вы знаете свой inner join on результат не может иметь null в определенном правом столбце таблицы, то"right join on строки не inner join on" строки right join on С on условие продлен на and столбец is null.

т. е. аналогично right join onunion all соответствующего left join on строк.

С в чем разница между "внутренним соединением" и "внешним соединением"?:

(SQL Standard 2006 SQL / Foundation 7.7 синтаксические правила 1, Общие правила 1 b, 3 c & d, 5 b.)