Как сделать FULL OUTER JOIN в MySQL?

Question

Как сделать FULL OUTER JOIN в MySQL?

Я хочу сделать полное внешнее соединение в MySQL. Это возможно? Поддерживается ли полное внешнее соединение MySQL?

875

sql mysql join outer-join full-outer-join

Источник

user440266 25 янв '11 в 17:34

14 ответов

Решение

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

Таблицы примеров

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

-- 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,

444

Источник

user4376 09 фев '12 в 16:23

Используя 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]

52

Источник

user4519059 28 май '15 в 05:01

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

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

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оператор безопасного сравнения, <=> скорее, чем =,

13

Источник

user1144035 14 янв '17 в 13:27

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

11

Источник

user2809294 12 окт '17 в 19:22

В 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

5

Источник

user1889017 14 янв '13 в 13:50

Модифицированный запрос 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

5

Источник

user163382 11 мар '16 в 08:47

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

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

4

Источник

user1001224 05 июн '18 в 07:54

Вы можете просто преобразовать полное внешнее соединение, например

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

2

Источник

user17389 18 авг '20 в 00:41

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

1

Источник

user350294 24 окт '13 в 11:10

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

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

-1

Источник

user7412996 22 май '17 в 21:53

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

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

-1

Источник

user7484554 13 июн '17 в 19:53

Это также возможно, но вы должны упомянуть те же имена полей в 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

-1

Источник

user2731924 03 фев '14 в 12:30

Mysql как таковой не поддерживает ни одну команду с именем FULL OUTER JOIN. Поддерживаются три соединения: INNER JOIN,LEFT JOIN и RIGHT JOIN.

Однако вы можете реализовать полное внешнее соединение, используя команду UNION как
(запрос на соединение слева) UNION (запрос на соединение справа)

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

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

-1

Источник

user7333741 31 дек '16 в 08:52

Ответ:

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

 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

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

-2

Источник

user5401970 09 фев '17 в 14:29

Стандарт 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,

Т.е. 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 строк.

В чем разница между "INNER JOIN" и "OUTER JOIN"?:

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

-2

Источник

user3404097 11 авг '18 в 22:09

Другие вопросы по тегам sql mysql join outer-join full-outer-join

user67606 25 янв '11 в 17:38 2011-01-25 17:38 · Accepted Answer · 2011-01-25 17:38

У вас нет ПОЛНЫХ СОЕДИНЕНИЙ на MySQL, но вы можете их эмулировать.

Для кода SAMPLE, транскрибированного из этого 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 удаляет повторяющиеся строки, введенные шаблоном запроса. Мы можем избежать введения повторяющихся строк, используя шаблон против объединения для второго запроса, а затем использовать оператор множеств UNION ALL для объединения двух множеств. В более общем случае, когда FULL OUTER JOIN будет возвращать повторяющиеся строки, мы можем сделать это:

  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