Я хочу выполнить полное внешнее соединение в MySQL. Это возможно? Поддерживается ли полное внешнее соединение MySQL?
Как выполнить ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ в MySQL?
Ответы (14)
У вас нет ПОЛНЫХ СОЕДИНЕНИЙ в MySQL, но вы можете быть уверены: подражайте им.
Для ОБРАЗЦА кода, записанного с этого вопроса SO, у вас есть:
с двумя таблицами 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
Вышеупомянутый запрос работает для особых случаев, когда операция FULL OUTER JOIN не приведет к созданию повторяющихся строк. Вышеупомянутый запрос зависит от оператора UNION
set для удаления повторяющихся строк, представленных шаблоном запроса. Мы можем избежать появления повторяющихся строк, используя шаблон anti-join для второго запроса, а затем используя оператор множества UNION ALL для объединения двух наборов. В более общем случае, когда ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ будет возвращать повторяющиеся строки, мы можем сделать это:
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)
- person Pavle Lekic; 19.03.2013
tbl1.col
должен быть столбцом соединения tbl1.id
) - нет никакой разницы в результатах
- person Nikola Bogdanović; 15.11.2013
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
дает два.
- person ysth; 01.04.2014
NULL
в предложении WHERE
?) и по-прежнему не могут идеально имитировать внешнее соединение. Например, он может завершиться неудачно, если у вас есть условие эксцентрического соединения, например, если все столбцы обеих таблиц имеют значение NULL.
- person Mark Amery; 31.08.2014
UNION
может привести к неверному результату, но только если одна из двух таблиц не имеет первичного ключа или ограничений уникальности. (в реляционном смысле можно утверждать, что в этом случае они не являются правильными таблицами;)
- person ypercubeᵀᴹ; 11.04.2015
t1
и t2
нет повторяющихся строк, запрос в этом ответе действительно возвращает набор результатов, который имитирует ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ. Но в более общем случае, например, список SELECT не содержит достаточного количества столбцов / выражений, чтобы сделать возвращаемые строки уникальными, тогда этот шаблон запроса недостаточен для воспроизведения набора, который был бы создан а FULL OUTER JOIN
. Чтобы добиться более точной эмуляции, нам понадобится оператор set UNION ALL
, а для одного из запросов потребуется шаблон anti-join. Комментарий от Павла Лекича (выше) дает правильный шаблон запроса.
- person spencer7593; 07.05.2015
UNION
завершится ошибкой, если одна из двух таблиц не имеет первичного ключа или ограничений уникальности, но он также завершится ошибкой, если SELECT
возвращает только подмножество столбцов вместо *
. Я понимаю, что запрос UNION
должен работать, и если он не работает, возникает проблема в реляционных терминах в другом месте, но я думаю, что общий способ воспроизведения FULL OUTER JOIN
- это UNION ALL
запрос с шаблоном сглаживания
- person fthiella; 31.03.2017
union all
без шаблона антисоединения - это просто неправильно, а union
запрос каким-то образом, но не всегда, правильный ... Я все еще считаю правильным ответ union all
с шаблоном антисоединения на любом из первых или второй запрос ...)
- person fthiella; 03.04.2017
Can't reopen table
- person tom10271; 16.04.2018
Ответ, который дал Пабло Санта-Крус, правильный; однако, если кто-то наткнулся на эту страницу и хочет получить больше разъяснений, вот подробная разбивка.
Примеры таблиц
Предположим, у нас есть следующие таблицы:
-- 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 не поддерживает это. Мы можем сымитировать это, выполнив UNION левого и правого соединения, например:
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
.
FULL OUTER JOIN
. Нет ничего плохого в том, чтобы делать запросы таким образом и использовать UNION для удаления этих дубликатов. Но чтобы действительно воспроизвести FULL OUTER JOIN
, нам нужно, чтобы один из запросов был антисоединением.
- person spencer7593; 07.05.2015
where
во втором select
запросе WHERE `t1`.`id` IS NULL;
?
- person Istiaque Ahmed; 06.11.2017
UNION
удалит эти дубликаты; но он также удаляет ВСЕ повторяющиеся строки, включая повторяющиеся строки, которые были бы возвращены FULL OUTER JOIN. Чтобы подражать a FULL JOIN b
, правильный шаблон - (a LEFT JOIN b) UNION ALL (b ANTI JOIN a)
.
- person spencer7593; 06.11.2017
including duplicate rows that would be in the returned by a FULL OUTER JOIN
- не могли бы вы объяснить это поподробнее?
- person Istiaque Ahmed; 06.11.2017
Использование запроса 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
Мой предлагаемый запрос:
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
@Steve Chambers: [Из комментариев, большое спасибо!]
< strong> Примечание. Это может быть лучшим решением как с точки зрения эффективности, так и с точки зрения получения тех же результатов, что и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)
FULL OUTER JOIN
. Это сообщение в блоге также хорошо это объясняет - цитируя метод 2: Он правильно обрабатывает повторяющиеся строки и не включает ничего, чего не должно быть. Необходимо использовать UNION ALL вместо обычного UNION, чтобы удалить дубликаты, которые я хочу сохранить. Это может быть значительно более эффективным для больших наборов результатов, поскольку нет необходимости сортировать и удалять дубликаты.
- person Steve Chambers; 22.07.2016
MySql не имеет синтаксиса FULL-OUTER-JOIN. Вы должны подражать, выполняя как LEFT JOIN, так и RIGHT 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, право join преобразуется в эквивалентное левое соединение путем переключения 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, которое является before-join или предикат AND в предложении 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 RIGHT JOIN, но это не так. Именно так работает оптимизатор запросов MySql. Поэтому ответственный разработчик должен обращать внимание на эти нюансы при построении запроса.
В 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
-безопасный оператор сравнения, <=>
, а не =
.
FULL OUTER JOIN
, если столбец name
имеет значение NULL. Запрос union all
с шаблоном анти-соединения должен правильно воспроизводить поведение внешнего соединения, но какое решение является более подходящим, зависит от контекста и от ограничений, которые активны для таблиц.
- person fthiella; 31.03.2017
union all
, но в этом ответе отсутствует шаблон антисоединения в первом или втором запросе, который сохранит существующие дубликаты, но не позволит добавлять новые. В зависимости от контекста другие решения (например, это) могут быть более подходящими.
- person fthiella; 03.04.2017
select t1.name,t2.name from t1 left join t2 using (name) union all select null, name from t2 where not exists (select 1 from t1 where t1.name<=>t2.name);
- person ysth; 16.08.2020
Вы можете сделать следующее:
(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);
Изменен запрос 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 t1.value IS NULL
Вы можете просто преобразовать полное внешнее соединение, например
SELECT fields
FROM firsttable
FULL OUTER JOIN secondtable ON joincondition
в:
SELECT fields
FROM firsttable
LEFT JOIN secondtable ON joincondition
UNION ALL
SELECT fields (replacing any fields from firsttable with NULL)
FROM secondtable
WHERE NOT EXISTS (SELECT 1 FROM firsttable WHERE joincondition)
Или, если у вас есть хотя бы один столбец, скажем foo
, в firsttable
, который НЕ равен NULL, вы можете сделать:
SELECT fields
FROM firsttable
LEFT JOIN secondtable ON joincondition
UNION ALL
SELECT fields
FROM firsttable
RIGHT JOIN secondtable ON joincondition
WHERE firsttable.foo IS NULL
что вы сказали о решении Cross join?
SELECT t1.*, t2.*
FROM table1 t1
INNER JOIN table2 t2
ON 1=1;
select (select count(*) from t1) * (select count(*) from t2))
строками в наборе результатов.
- person Marc L.; 24.07.2017
Я исправляю ответ, и работы включают все строки (на основе ответа Павла Лекича)
(
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
, не совпадающие с tableb
, и наоборот. Вы пытаетесь UNION ALL
, что будет работать только в том случае, если в этих двух таблицах будут столбцы с одинаковым порядком, что не гарантируется.
- person Marc L.; 24.07.2017
Ответ:
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 не распространяется на крайний случай, когда в базовых таблицах есть повторяющиеся записи.
Объяснение:
Есть крайний случай, который UNION или UNION ALL не может охватить. Мы не можем протестировать это на 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
Решение 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
Дает неверный ответ:
id1 id2
NULL 3
NULL 4
1 NULL
2 NULL
5 5
6 6
Решение 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
Тоже неверно.
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
Порядок другой, но в остальном соответствует правильному ответу.
UNION ALL
решение. Кроме того, в нем представлено решение, использующее UNION
, которое будет медленнее для больших исходных таблиц из-за требуемой дедупликации. Наконец, он не будет компилироваться, потому что поле id
не существует в подзапросе tmp
.
- person Marc L.; 24.07.2017
UNION ALL
решение: ... Также неверно. Код, который вы представляете, не учитывает исключение пересечения из правого соединения (where t1.id1 is null
), которое должно быть указано в UNION ALL
. Другими словами, ваше решение превосходит все остальные, только если одно из этих решений реализовано неправильно. Что касается привлекательности, то внимание было уделено. Мои извинения, это было безвозмездно.
- person Marc L.; 25.07.2017
Это также возможно, но вы должны указать те же имена полей в 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
Стандарт SQL говорит, что full join on
- это inner join on
строки, union all
несопоставленные строки левой таблицы, расширенные нулями, union all
строки правой таблицы, расширенные нулями. Т.е. inner join on
строк union all
строк в left join on
, но не inner join on
union all
строк в right join on
, но не inner join on
.
Т.е. left join on
строк, union all
right join on
строк не в inner join on
. Или, если вы знаете, что ваш inner join on
результат не может иметь значение NULL в конкретном правом столбце таблицы, тогда «right join on
строк не в inner join on
» - это строки в right join on
с условием on
, расширенным на and
этот столбец is null
.
Т.е. аналогично right join on
union all
соответствующие left join on
строки.
Из В чем разница между «INNER JOIN» и «OUTER JOIN»?:
(Стандарт SQL 2006, правила синтаксиса SQL / Foundation 7.7 1, общие правила 1 b, 3 c и d, 5 b.)