Как сделать полное внешнее соединение в 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

Полное внешнее соединение

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

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 в значении «выполнить оба этих запроса, а затем поместить результаты друг на друга»; некоторые строки будут получены из первого запроса, а некоторые из второго.

Следует отметить, что 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 не имеет синтаксиса FULL-OUTER-JOIN. Вы должны подражать, выполнив обе функции: ЛЕВЫЙ и ПРАВЫЙ, как показано ниже:

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

Но MySql также не имеет синтаксиса RIGHT JOIN. Согласно MySql’s упрощение внешнего соединения, правое соединение преобразуется в эквивалентное левое соединение путем переключения t1 и t2 в FROM а также ON предложение в запросе. Таким образом, MySql Query Optimizer переводит исходный запрос в следующее:

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

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

Оптимизатор запросов MySql регулярно проверяет предикаты, если они Нуль-отвергнута.
Теперь, если вы выполнили ПРАВИЛЬНОЕ СОЕДИНЕНИЕ, но с предикатом WHERE для столбца от t1, то вы можете столкнуться с риском Нуль-отвергнута сценарий.

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

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 теперь находится в предложении ON. Если t1.col1 определен как NOT NULL
столбец, то этот запрос будет Нуль-отвергнута.

Любое внешнее соединение (левое, правое, полное) Нуль-отвергнута преобразуется во внутреннее соединение MySql.

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

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

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