Вопрос: В чем разница между «INNER JOIN» и «OUTER JOIN»?


Также как сделать LEFT JOIN, RIGHT JOINа также FULL JOINвписаться?


3971


источник


Ответы:


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

  • Внутреннее объединение A и B дает результат пересечения A с B, то есть внутреннюю часть Диаграмма Венна пересечение.

  • Внешнее объединение A и B дает результаты объединения A B, то есть внешние части диаграммы Венна диаграммы.

Примеры

Предположим, что у вас есть две таблицы с одним столбцом каждый и данные:

A    B
-    -
1    3
2    4
3    5
4    6

Заметим, что (1,2) уникальны для A, (3,4) являются общими, и (5,6) являются единственными для B.

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

Внутреннее соединение, использующее любой из эквивалентных запросов, дает пересечение двух таблиц, т. Е. Двух строк, которые они имеют вместе.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Левое внешнее соединение

Левое внешнее соединение даст все строки в A плюс любые общие строки в B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Правое внешнее соединение

Правое внешнее объединение даст все строки в B плюс любые общие строки в A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

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

Полное внешнее объединение даст вам объединение A и B, т. Е. Все строки в A и все строки в B. Если что-то в A не имеет соответствующей базы данных в B, то B-часть является нулевой, а порог наоборот.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

5375



Также вы можете рассмотреть следующую схему для разных типов соединений;

visual explanation of joins

Источник: Визуально-Представление-из-SQL-Присоединяется подробно объясняется C.L. Моффат


2420



я рекомендую Статья блога Джеффа , Лучшее описание, которое я когда-либо видел, плюс визуализация, например:

Внутреннее соединение:

enter image description here

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

enter image description here


586



Диаграммы Венна на самом деле не делают этого для меня.

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

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

  1. Представьте себе крест.
  2. Оцените onпредложение против всех строк с шага 1, сохраняя те, где предикат оценивает true
  3. (Только для внешних соединений) добавьте обратно в любые внешние строки, которые были потеряны на шаге 2.

(NB: На практике оптимизатор запросов может найти более эффективные способы выполнения запроса, чем чисто логическое описание выше, но конечный результат должен быть одинаковым)

Я начну с анимированной версии полное внешнее соединение , Дальнейшее объяснение следует.

enter image description here


объяснение

Исходные таблицы

enter link description here

Сначала начните с CROSS JOIN(Декартовой продукт AKA). Это не имеет ONи просто возвращает каждую перестановку строк из двух таблиц.

SELECT A.Colour, B.Colour FROM A CROSS JOIN B

enter link description here

Внутренние и внешние соединения имеют предикат предложения ON.

  • Внутреннее соединение. Оцените условие в предложении «ON» для всех строк в результате перекрестного соединения. Если true, верните объединенную строку. В противном случае отбросьте его.
  • Left Outer Join. То же, что и внутреннее соединение, то для любых строк в левой таблице, которые не соответствуют чему-либо, выводят их с NULL-значениями для столбцов правой таблицы.
  • Правое внешнее соединение. То же, что и внутреннее соединение, то для любых строк в правой таблице, которые не соответствуют чему-либо, выводят их с значениями NULL для столбцов левой таблицы.
  • Полное внешнее соединение. То же, что и внутреннее соединение, тогда сохраняйте левые несопоставимые строки, как в левом внешнем соединении, так и в правых не совпадающих строках в соответствии с правым внешним соединением.

Некоторые примеры

SELECT A.Colour, B.Colour FROM IN INER JOIN B ON A.Colour = B.Colour

Вышеприведенное классическое объединение equi.

Inner Join

Анимированная версия

enter image description here

SELECT A.Colour, B.Colour FROM IN INER JOIN B ON A.Colour NOT IN («Зеленый», «Синий»)

Внутреннее условие соединения необязательно должно быть условием равенства, и ему не нужно ссылаться на столбцы из (или даже любой) таблиц. Оценка A.Colour NOT IN ('Green','Blue')на каждой строке перекрестного соединения возвращается.

inner 2

SELECT A.Colour, B.Colour FROM IN INER JOIN B ON 1 = 1

Условие соединения оценивается как true для всех строк в результате перекрестного соединения, так что это то же самое, что и кросс-соединение. Я не буду повторять изображение 16 строк.

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour

Внешние соединения логически оцениваются так же, как и внутренние соединения, за исключением того, что если строка из левой таблицы (для левого соединения) не объединяется ни с одной строкой из правой таблицы, она сохраняется в результате с NULLзначения для правых столбцов.

LOJ

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour IS NULL

Это просто ограничивает предыдущий результат только возвратом строк, где B.Colour IS NULL, В этом конкретном случае это будут строки, которые были сохранены, поскольку они не совпадали в правой таблице, и запрос возвращает единственную красную строку, не сопоставленную в таблице B, Это известно как анти-соединение.

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

loj is null

SELECT A.Colour, B.Colour FROM A RIGHT OUTER JOIN B ON A.Colour = B.Colour

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

ROJ

SELECT A.Colour, B.Colour FROM FULL OUTER JOIN B ON A.Colour = B.Colour

Полное внешнее объединение объединяет поведение левого и правого объединений и сохраняет несоответствующие строки как из левой, так и из правой таблиц.

FOJ

SELECT A.Colour, B.Colour FROM FULL OUTER JOIN B ON 1 = 0

Никакие строки в перекрестном соединении не соответствуют 1=0сказуемое. Все строки с обеих сторон сохраняются с использованием обычных правил внешнего соединения с NULL в столбцах из таблицы с другой стороны.

FOJ 2

SELECT COALESCE (A.Colour, B.Colour) AS Color ОТ ПОЛНОГО ВНЕШНЕГО СОЕДИНЕНИЯ B ON 1 = 0

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

UNION ALL

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour = 'Green'

Обратите внимание, что WHERE(если присутствует) логически запускается после объединения. Одной из распространенных ошибок является выполнение левого внешнего соединения, а затем включение предложения WHERE с условием в правой таблице, которое заканчивается исключением несоответствующих строк. Вышеприведенное завершает выполнение внешнего соединения ...

LOJ

... И затем выполняется предложение «Где». NULL= 'Green'не оценивается как true, поэтому строка, сохраненная внешним соединением, заканчивается отбрасыванием (вместе с синим), эффективно преобразуя соединение обратно во внутреннее.

LOJtoInner

Если бы намерение состояло в том, чтобы включать только строки из B, где Цвет Зеленый, а все строки из A, независимо от правильного синтаксиса,

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour AND B.Colour = 'Green'

enter image description here

SQL Fiddle

См. Эти примеры запустите live на сайте SQLFiddle.com ,


509



Из статьи " MySQL - LEFT JOIN и RIGHT JOIN, INNER JOIN и OUTER JOIN «Грэм Эллис на своем блоге« Лошадь ».

В базе данных, такой как MySQL, данные делятся на несколько таблиц, которые затем подключаются ( Joined) вместе JOINв SELECTкоманды для чтения записей из нескольких таблиц. Прочтите этот пример, чтобы узнать, как он работает.

Во-первых, некоторые примеры данных:

people
    mysql> select * from people;
    +------------+--------------+------+
    | name       | phone        | pid  |
    +------------+--------------+------+
    | Mr Brown   | 01225 708225 |    1 |
    | Miss Smith | 01225 899360 |    2 |
    | Mr Pullen  | 01380 724040 |    3 |
    +------------+--------------+------+
    3 rows in set (0.00 sec)

property
    mysql> select * from property;
    +------+------+----------------------+
    | pid  | spid | selling              |
    +------+------+----------------------+
    |    1 |    1 | Old House Farm       |
    |    3 |    2 | The Willows          |
    |    3 |    3 | Tall Trees           |
    |    3 |    4 | The Melksham Florist |
    |    4 |    5 | Dun Roamin           |
    +------+------+----------------------+
    5 rows in set (0.00 sec)

РЕГУЛЯРНОЕ СОЕДИНЕНИЕ

Если мы делаем регулярный JOIN (без каких-либо ключевых слов INNER, OUTER, LEFT или RIGHT), мы получим все записи, соответствующие соответствующим образом в двух таблицах, и записи в обеих входящих таблицах, которые не совпадают, не сообщаются :

mysql> select name, phone, selling 
from people join property 
on people.pid = property.pid;
+-----------+--------------+----------------------+
| name      | phone        | selling              |
+-----------+--------------+----------------------+
| Mr Brown  | 01225 708225 | Old House Farm       |
| Mr Pullen | 01380 724040 | The Willows          |
| Mr Pullen | 01380 724040 | Tall Trees           |
| Mr Pullen | 01380 724040 | The Melksham Florist |
+-----------+--------------+----------------------+
4 rows in set (0.01 sec)

ВЛЕВО

Если мы делаем LEFT JOIN, мы получаем все записи, которые совпадают так же, и IN ADDITION мы получаем дополнительную запись для каждой несопоставимой записи в левой таблице соединения - таким образом, гарантируя (в этом примере), что каждый PERSON получает упоминание :

   mysql> select name, phone, selling 
    from people left join property 
    on people.pid = property.pid; 
    +------------+--------------+----------------------+
    | name       | phone        | selling              |
    +------------+--------------+----------------------+
    | Mr Brown   | 01225 708225 | Old House Farm       |
    | Miss Smith | 01225 899360 | NULL <<-- unmatch    |
    | Mr Pullen  | 01380 724040 | The Willows          |
    | Mr Pullen  | 01380 724040 | Tall Trees           |
    | Mr Pullen  | 01380 724040 | The Melksham Florist |
    +------------+--------------+----------------------+
    5 rows in set (0.00 sec)

ПРАВОЕ СОЕДИНЕНИЕ

Если мы делаем ПРАВИЛЬНОЕ СОЕДИНЕНИЕ, мы получаем все записи, которые соответствуют и IN ADDITION, дополнительную запись для каждой несогласованной записи в правой таблице соединения - в моем примере, это означает, что каждое свойство получает упоминание, даже если мы не делаем есть детали продавца:

mysql> select name, phone, selling 
from people right join property 
on people.pid = property.pid;
+-----------+--------------+----------------------+
| name      | phone        | selling              |
+-----------+--------------+----------------------+
| Mr Brown  | 01225 708225 | Old House Farm       |
| Mr Pullen | 01380 724040 | The Willows          |
| Mr Pullen | 01380 724040 | Tall Trees           |
| Mr Pullen | 01380 724040 | The Melksham Florist |
| NULL      | NULL         | Dun Roamin           |
+-----------+--------------+----------------------+
5 rows in set (0.00 sec)

INNER JOIN делает полное соединение, как и в первом примере, и слово OUTER можно добавить после слова LEFT или RIGHT в последних двух примерах - оно предусмотрено для совместимости ODBC и не добавляет дополнительных возможностей.


287



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

Получить только сопоставленные строки, т. Е. A intersect B,

Enter image description here

SELECT *
FROM dbo.Students S
INNER JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Левая внешняя связь

Выберите все записи из первой таблицы, а любые записи во второй таблицу, соответствующую совпадающим клавишам.

Enter image description here

SELECT *
FROM dbo.Students S
LEFT JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Полная внешняя связь

Выберите все записи из второй таблицы и любые записи в первой таблицу, соответствующую совпадающим клавишам.

Enter image description here

SELECT *
FROM dbo.Students S
FULL JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Рекомендации


113



присоединяется используются для объединения данных из двух таблиц, в результате чего создается новая временная таблица. Соединения выполняются на основе чего-то, называемого предикатом, который определяет условие для использования для соединения. Разница между внутренним соединением и внешним соединением заключается в том, что внутреннее соединение будет возвращать только строки, которые фактически соответствуют на основе предиката соединения. Рассмотрим таблицу Employee and Location:

enter image description here

Внутреннее соединение:- Внутреннее объединение создает новую таблицу результатов путем объединения значений столбцов из двух таблиц ( Наемный рабочий а также Место нахождения ) на основе предиката соединения. Запрос сравнивает каждую строку Наемный рабочий с каждой строкой Место нахождения найти все пары строк, которые удовлетворяют связующему предикату. Когда предикат соединения выполняется путем сопоставления значений, отличных от NULL, значения столбцов для каждой согласованной пары строк Наемный рабочий а также Место нахождения объединяются в строку результатов. Вот как выглядит SQL для внутреннего соединения:

select  * from employee inner join location on employee.empID = location.empID
OR
select  * from employee, location where employee.empID = location.empID

Теперь вот что получило бы результат выполнения SQL: enter image description here enter image description here

Outer Join: - Внешнее соединение не требует, чтобы каждая запись в двух соединенных таблицах имела соответствующую запись. Объединенная таблица сохраняет каждую запись, даже если не существует другой подходящей записи. Внешние соединения подразделяются дальше на левые внешние соединения и правые внешние соединения, в зависимости от того, какие строки таблицы сохраняются (слева или справа).

Left Outer Join: - Результат левого внешнего соединения (или просто левого соединения) для таблиц Наемный рабочий а также Место нахождения всегда содержит все записи «левой» таблицы ( Наемный рабочий ), даже если условие соединения не находит никакой соответствующей записи в «правой» таблице ( Место нахождения ). Вот как выглядит SQL для левого внешнего соединения, используя приведенные выше таблицы:

select  * from employee left outer join location on employee.empID = location.empID;
//Use of outer keyword is optional

Теперь вот что получило результат выполнения этого SQL: enter image description here enter image description here

Right Outer Join: - Правое внешнее соединение (или правое соединение) близко напоминает левое внешнее соединение, за исключением обработки обращенных столов. Каждая строка из «правой» таблицы ( Место нахождения ) появится в объединенной таблице хотя бы один раз. Если ни одна соответствующая строка из «левой» таблицы ( Наемный рабочий ) существует, NULL будет отображаться в столбцах из Наемный рабочий для тех записей, которые не соответствуют Место нахождения , Это выглядит так:

select * from employee right outer join location  on employee.empID = location.empID;
//Use of outer keyword is optional

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

enter image description hereenter image description here

Полное внешнее соединение: - Полное Outer Join или Full Join - сохранить информацию о несовместимости, включив в результаты соединения нечеткие строки, используйте полное внешнее соединение. Он включает все строки из обеих таблиц, независимо от того, имеет ли другая таблица соответствующее значение. enter image description here

Источник изображения

Справочное руководство по MySQL 8.0 - Синтаксис соединения


104



In simple words:

An inner join retrieve the matched rows only.

Whereas an outer join retrieve the matched rows from one table and all rows in other table ....the result depends on which one you are using:

  • Left: Matched rows in the right table and all rows in the left table

  • Right: Matched rows in the left table and all rows in the right table or

  • Full: All rows in all tables. It doesn't matter if there is a match or not


101



A inner join only shows rows if there is a matching record on the other (right) side of the join.

A (left) outer join shows rows for each record on the left hand side, even if there are no matching rows on the other (right) side of the join. If there is no matching row, the columns for the other (right) side would show NULLs.


91



Inner joins require that a record with a related ID exist in the joined table.

Outer joins will return records for the left side even if nothing exists for the right side.

For instance, you have an Orders and an OrderDetails table. They are related by an "OrderID".

Orders

  • OrderID
  • CustomerName

OrderDetails

  • OrderDetailID
  • OrderID
  • ProductName
  • Qty
  • Price

The request

SELECT Orders.OrderID, Orders.CustomerName FROM Orders 
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID

will only return Orders that also have something in the OrderDetails table.

If you change it to OUTER LEFT JOIN

SELECT Orders.OrderID, Orders.CustomerName FROM Orders 
LEFT JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID

then it will return records from the Orders table even if they have no OrderDetails records.

You can use this to find Orders that do not have any OrderDetails indicating a possible orphaned order by adding a where clause like WHERE OrderDetails.OrderID IS NULL.


66