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

вопрос создан в Wed, Feb 13, 2019 12:00 AM

Также как вписываются LEFT JOIN, RIGHT JOIN и FULL JOIN?

    
4376
  1. Ответов & комментарии и их ссылки ниже только один фактически объясняет, как диаграммы Венна представляют операторы: область пересечения окружности представляет набор строк в A JOIN B. Область, уникальная для каждого круга, представляет набор строк, которые вы получаете, взяв его Строки таблицы, которые не участвуют в A JOIN B, и при добавлении столбцов, уникальных для другой таблицы, устанавливаются в NULL. (И большинство из них дают смутное поддельное соответствие кругов А и В.)
    2014-10-18 20: 24: 59Z
  2. Многие ответы уже предоставлены, но я не видел упомянутое руководство. Если вы знаете диаграммы Венна, это БОЛЬШОЙ учебник: blog.codinghorror .com /a-visual-объяснение-sql-joins Для меня это достаточно кратко, чтобы быстро прочитать, но все же схватывает всю концепцию и работает все случаи очень хорошо. Если вы не знаете, что такое диаграммы Венна, изучите их. Займет 5-10 минут, чтобы помочь вам, когда вам нужно будет визуализировать работу с наборами и управление операциями над наборами.
    2017-05-09 09: 04: 59Z
  3. @ DanteTheSmith Нет, это страдает от тех же проблем, что и диаграммы здесь. См. Мой комментарий выше относительно вопроса & Ниже приведено то же сообщение в блоге: «Джефф отказывается от своего блога на несколько страниц внизу в комментариях». Диаграммы Венна показывают элементы в наборах. Просто попробуйте точно определить, что это за наборы и каковы элементы этих диаграмм. Наборы не являются таблицами, а элементы не являются их строками. Также можно объединить любые две таблицы, поэтому PK & ФК не имеют отношения к делу. Все поддельные. Вы делаете то, что сделали тысячи других - у вас смутное впечатление , что вы (ошибочно) предполагаете, имеет смысл.
    2017-05-18 05: 57: 51Z
  4. Может ли кто-нибудь ответить на это в отношении индексированных данных временного ряда - внутренние и внешние объединения часто не работают в человеческом /веннском смысле, когда речь идет о временных метках, которые отличается только на миллисекунды.
    2017-09-05 09: 26: 18Z
  5. исходный заголовок был более понятен в отношении того, что запрашивалось бенгом («разные типы соединения» могут относиться к физическим типам соединения, таким как, например, хеш и объединение) Исходное название Был также явно эффективен с поисковиками по количеству просмотров. Я откатил это ненужное редактирование назад
    2019-02-13 08: 14: 08Z
24 ответа                              24                         

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

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

  • Внешнее объединение A и B дает результаты объединения 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
    
5823
2017-09-28 19: 52: 04Z
  1. Было бы хорошо дополнить пример, добавив еще одну строку в таблицу B со значением 4. Это покажет, что внутренние объединения не обязательно должны быть равны ни одной из строк.
    2009-08-30 12: 59: 48Z
  2. Отличное объяснение, однако это утверждение: Внешнее соединение A и B дает результаты объединения A B, то есть внешние части объединения диаграммы Венна . не точно сформулирован. Внешнее соединение даст результаты пересечения B с B в дополнение к одному из следующих: все A (левое соединение), все B (правое соединение) или все A и все B (полное соединение). Только этот последний сценарий действительно является объединением Б. Тем не менее, хорошо написанное объяснение.
    2011-05-03 19: 57: 42Z
  3. Я прав, что FULL JOIN является псевдонимом FULL OUTER JOIN, а LEFT JOIN является псевдонимом LEFT OUTER JOIN?
    2013-01-01 20: 29: 27Z
  4. да отличное и отличное объяснение. но почему в столбце b значения не в порядке? то есть это 6,5, а не 5,6?
    2013-03-04 08: 39: 36Z
  5. @ Ameer, спасибо. Регистрация не гарантирует порядок, вам нужно добавить предложение ORDER BY.
    2013-03-05 00: 28: 16Z

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

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

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

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

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

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

 введите описание изображения здесь

Объяснение

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

введите описание ссылки здесь

Сначала начните с CROSS JOIN (декартово произведение АКА). В нем нет предложения ON, и он просто возвращает каждую комбинацию строк из двух таблиц.

ВЫБРАТЬ A.Colour, B.Colour ИЗ КРЕСТОВОГО СОЕДИНЕНИЯ B

введите описание ссылки здесь

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

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

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

ВЫБРАТЬ A.Colour, B.Colour ИЗ ВНУТРЕННЕГО СОЕДИНЕНИЯ B НА A.Colour = B.Colour

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

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

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

 введите описание изображения здесь

ВЫБРАТЬ A.Colour, B.Colour ИЗ ВНУТРЕННЕГО СОЕДИНЕНИЯ B НА A.Colour NOT IN («Зеленый», «Синий»)

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

inner 2

ВЫБРАТЬ A.Colour, B.Colour ИЗ ВНУТРЕННЕГО СОЕДИНЕНИЯ B ON 1 = 1

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

ВЫБРАТЬ A.Colour, B.Colour ИЗ ЛЕВОГО НАРУЖНОГО СОЕДИНЕНИЯ B НА A.Colour = B.Colour

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

LOJ

ВЫБРАТЬ A.Colour, B.Colour ИЗ ЛЕВОГО ВНЕШНЕГО СОЕДИНЕНИЯ B НА A.Colour = B.Colour ГДЕ B.Colour NULL

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

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

loj is null

ВЫБРАТЬ A.Colour, B.Colour ИЗ ПРАВИЛЬНОГО ВНЕШНЕГО СОЕДИНЕНИЯ B НА A.Colour = B.Colour

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

ROJ

ВЫБРАТЬ A.Colour, B.Colour ИЗ ПОЛНОГО НАРУЖНОГО СОЕДИНЕНИЯ B НА A.Colour = B.Colour

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

FOJ

ВЫБРАТЬ A.Colour, B.Colour ИЗ ПОЛНОГО НАРУЖНОГО СОЕДИНЕНИЯ B ON 1 = 0

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

FOJ 2

ВЫБЕРИТЕ COALESCE (A.Colour, B.Colour) В КАЧЕСТВЕ ИЗ ПОЛНОГО НАРУЖНОГО СОЕДИНЕНИЯ B ON 1 = 0

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

UNION ALL

ВЫБРАТЬ A.Colour, B.Colour ИЗ ЛЕВОГО ВНЕШНЕГО СОЕДИНЕНИЯ B НА A.Colour = B.Colour ГДЕ B.Colour = 'Green'

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

LOJ

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

LOJtoInner

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

ВЫБРАТЬ A.Colour, B.Colour ИЗ ЛЕВОГО ВНЕШНЕГО СОЕДИНЕНИЯ B НА A.Colour = B.Colour AND B.Colour = 'Green'

 введите описание изображения здесь

SQL Fiddle

Посмотрите эти примеры запустить на SQLFiddle.com .

    
623
2018-08-03 07: 59: 59Z
  1. Я скажу, что хотя это не работает для меня так же хорошо, как диаграммы Венна, я ценю, что люди меняются и учатся по-разному, и это очень хорошо представлено объяснение не похоже на то, что я видел раньше, поэтому я поддерживаю @ypercube в начислении бонусных баллов. Также хорошая работа, объясняющая разницу в размещении дополнительных условий в предложении JOIN по сравнению с предложением WHERE. Слава тебе, Мартин Смит.
    2014-12-20 04: 48: 36Z
  2. @ OldPro Диаграммы Венна в порядке, я полагаю, они в порядке, но они ничего не говорят о том, как представлять перекрестное соединение или дифференцировать один тип предиката объединения, такой по мере присоединения от другого. Ментальная модель оценки предиката соединения в каждой строке результата перекрестного соединения, затем добавление обратно в несопоставленные строки, если внешнее соединение, и, наконец, оценка местоположения для меня лучше.
    2014-12-20 11: 05: 32Z
  3. Диаграммы Венна хороши для представления союзов и пересечений и различий, но не для соединений. Они имеют небольшую образовательную ценность для очень простых объединений, то есть объединений, где условие объединения находится в уникальных столбцах.
    2014-12-20 22: 28: 49Z
  4. @ MartinSmith Ого, я согласен, я совершенно не прав! Слишком привык работать с одним человеком ... спасибо за исправление.
    2016-01-28 15: 57: 18Z

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

введите описание изображения здесь

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

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

Теперь вот как будет выглядеть результат выполнения SQL: введите описание изображения здесь  введите описание изображения здесь

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

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

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

Теперь вот как будет выглядеть результат выполнения этого SQL: введите описание изображения здесь  введите описание изображения здесь

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

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

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

введите описание изображения здесь  введите описание изображения здесь

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

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

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

Операции Oracle Join

    
142
2018-08-07 14: 38: 49Z

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

Извлекайте только совпадающие строки, то есть A intersect B.

Введите описание изображения здесь

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

Внешнее соединение слева

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

Введите описание изображения здесь

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

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

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

Введите описание изображения здесь

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

Ссылки

121
2014-07-21 15: 38: 32Z
  1. Как называется инструмент? Я нахожу это интересным, так как показывает количество строк и диаграмм Венна
    2014-01-27 12: 23: 03Z
  2. @ GrijeshChauhan Datamartist :)
    2014-01-27 12: 25: 31Z
  3. @ Trushar :( это не для системы Linux ..
    2014-01-27 12: 27: 42Z
  4. @ GrijeshChauhan Да, но вы можете попробовать запустить его, используя wine .
    2014-01-27 12: 30: 38Z
  5. Ооо! да я .. я использовал SQLyog с помощью вина .. также есть PlayOnLinux
    2014-01-27 12: 32: 59Z

Простыми словами:

внутреннее соединение извлекает только совпадающие строки.

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

  • Left . Соответствующие строки в правой таблице и все строки в левой таблице

  • Право . Соответствующие строки в левой таблице и все строки в правой таблице или

  • Полный : все строки во всех таблицах. Не имеет значения, есть совпадение или нет

106
2014-07-21 15: 35: 37Z
  1. @ nomen Этот ответ не относится к нему, но INNER JOIN - это пересечение, а FULL OUTER JOIN - это соответствующий UNION if left & правые наборы /кружки содержат строки (соответственно) LEFT & УЖЕ присоединиться. PS Этот ответ неясен для строк во входных и выходных данных. Он путает «в левой /правой таблице» с «имеет левую /правую часть в левой /правой» и использует «совпадающую строку» против «все» для обозначения строки, расширенной строкой из другой таблицы, против нуля.
    2015-11-29 01: 17: 46Z

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

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

    
100
2015-01-05 01: 14: 26Z

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

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

Например, у вас есть таблица Orders и OrderDetails. Они связаны "OrderID".

Заказы

  • OrderID
  • CustomerName

Детали_Заказов

  • OrderDetailID
  • OrderID
  • ProductName
  • Количество
  • Цена

Запрос

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

будет возвращать только те заказы, которые также есть в таблице OrderDetails.

Если вы измените его на OUTER LEFT JOIN

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

тогда он вернет записи из таблицы Orders, даже если у них нет записей OrderDetails.

Это можно использовать для поиска заказов, в которых нет OrderDetails, указывающего возможный потерянный заказ, добавив предложение where, например WHERE OrderDetails.OrderID IS NULL.

    
75
2018-09-10 08: 25: 51Z
  1. Я ценю простой, но реалистичный пример. Я успешно изменил запрос, например с SELECT c.id, c.status, cd.name, c.parent_id, cd.description, c.image FROM categories c, categories_description cd WHERE c.id = cd.categories_id AND c.status = 1 AND cd.language_id = 2 ORDER BY c.parent_id ASC, на SELECT c.id, c.status, cd.name, c.parent_id, cd.description, c.image FROM categories c INNER JOIN categories_description cd ON c.id = cd.categories_id WHERE c.status = 1 AND cd.language_id = 2 ORDER BY c.parent_id ASC (MySQL). Я не был уверен насчет дополнительных условий, они хорошо сочетаются ...
    2013-01-05 11: 11: 24Z

Простыми словами:

Внутреннее объединение - > Возьмите ТОЛЬКО общие записи из родительской и дочерней таблиц, ГДЕ первичный ключ родительской таблицы соответствует внешнему ключу в дочерней таблице.

Левое объединение - >

псевдокод

1.Take All records from left Table
2.for(each record in right table,) {
    if(Records from left & right table matching on primary & foreign key){
       use their values as it is as result of join at the right side for 2nd table.
    } else {
       put value NULL values in that particular record as result of join at the right side for 2nd table.
    }
  }

Правое соединение . Точно противоположно левому соединению. Поместите имя таблицы в LEFT JOIN справа от правого соединения, вы получите тот же вывод, что и в LEFT JOIN.

Внешнее соединение : Показать все записи в обеих таблицах No matter what. Если записи в левой таблице не соответствуют правой таблице на основе первичного ключа Forieign, используйте значение NULL в качестве результата объединения.

Пример:

Пример

Давайте теперь предположим, что для 2 таблиц

1.employees , 2.phone_numbers_employees р>

employees : id , name 

phone_numbers_employees : id , phone_num , emp_id   

Здесь таблица employee - это главная таблица, phone_numbers_employees - это дочерняя таблица (она содержит emp_id в качестве внешнего ключа, который соединяет employee.id с его дочерней таблицей.)

Внутренние объединения

Взять записи из 2 таблиц. ТОЛЬКО ЕСЛИ если первичный ключ таблицы сотрудников (его идентификатор) совпадает с внешним ключом дочерней таблицы phone_numbers_employees (emp_id) .

Таким образом, запрос будет:

SELECT e.id , e.name , p.phone_num FROM employees AS e INNER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

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

Левые присоединения .

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

SELECT e.id , e.name , p.phone_num FROM employees AS e LEFT JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Внешние объединения .

SELECT e.id , e.name , p.phone_num FROM employees AS e OUTER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Схематически это выглядит так:

Diagram

    
62
2015-02-19 05: 11: 56Z
  1. Результат не имеет ничего общего с первичными /уникальными /потенциальными ключами & внешние ключи. Бавур может и должен быть описан без ссылки на них. Рассчитывается перекрестное соединение, затем строки, не соответствующие условию ON, отфильтровываются; кроме того, для внешних объединений строки, отфильтрованные /несопоставленные, расширяются на NULL (для LEFT /RIGHT /FULL и включаются.
    2015-08-10 04: 27: 54Z

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

В OUTER JOIN в результирующей таблице могут быть пустые столбцы. Внешнее соединение может быть либо LEFT, либо RIGHT.

LEFT OUTER JOIN возвращает все строки из первой таблицы, даже если во второй таблице нет совпадений.

RIGHT OUTER JOIN возвращает все строки из второй таблицы, даже если в первой таблице нет совпадений.

    
55
2015-06-09 03: 21: 07Z

 Это хорошее объяснение для объединений

Это хорошее схематичное объяснение для всех видов объединений

источник: http://ssiddique.info/understanding-sql- присоединяется к-в-простой way.html

    
54
2016-10-18 18: 48: 59Z

INNER JOIN требует, чтобы при сравнении этих двух таблиц было как минимум совпадение. Например, таблица A и таблица B, которые подразумевают A ٨ B (пересечение A B).

LEFT OUTER JOIN и LEFT JOIN одинаковы. Он дает все записи, совпадающие в обеих таблицах, и все возможности левой таблицы.

Аналогично, RIGHT OUTER JOIN и RIGHT JOIN одинаковы. Он дает все записи, совпадающие в обеих таблицах, и все возможности правой таблицы.

FULL JOIN - это комбинация LEFT OUTER JOIN и RIGHT OUTER JOIN без дублирования.

    
51
2013-09-26 21: 25: 28Z

Ответ в смысле каждого из них, а значит и в результатах.

  

Примечание.
  В SQLite нет RIGHT OUTER JOIN или FULL OUTER JOIN.
  А также в MySQL нет FULL OUTER JOIN.

Мой ответ основан на приведенной выше заметке .

Если у вас есть две такие таблицы:

--[table1]               --[table2]
id | name                id | name
---+-------              ---+-------
1  | a1                  1  | a2
2  | b1                  3  | b2

CROSS JOIN /OUTER JOIN:
Вы можете иметь все эти данные таблиц с CROSS JOIN или просто с , следующим образом:

SELECT * FROM table1, table2
--[OR]
SELECT * FROM table1 CROSS JOIN table2

--[Results:]
id | name | id | name 
---+------+----+------
1  | a1   | 1  | a2
1  | a1   | 3  | b2
2  | b1   | 1  | a2
2  | b1   | 3  | b2

ВНУТРЕННЕЕ СОЕДИНЕНИЕ:
Если вы хотите добавить фильтр к вышеуказанным результатам на основе отношения, такого как table1.id = table2.id, вы можете использовать INNER JOIN:

SELECT * FROM table1, table2 WHERE table1.id = table2.id
--[OR]
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id

--[Results:]
id | name | id | name 
---+------+----+------
1  | a1   | 1  | a2

ВЛЕВО [НАРУЖНЫЙ] ПРИСОЕДИНЯЙТЕСЬ:
Если вы хотите, чтобы все строки одной из таблиц в приведенном выше результате - с одинаковым отношением - вы могли использовать LEFT JOIN:
(Для ПРАВИЛЬНОГО СОЕДИНЕНИЯ просто поменяйте местами таблицы)

SELECT * FROM table1, table2 WHERE table1.id = table2.id 
UNION ALL
SELECT *, Null, Null FROM table1 WHERE Not table1.id In (SELECT id FROM table2)
--[OR]
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id

--[Results:]
id | name | id   | name 
---+------+------+------
1  | a1   | 1    | a2
2  | b1   | Null | Null

ПОЛНОЕ НАРУШЕНИЕ:
Если вы также хотите, чтобы в ваших результатах были все строки другой таблицы, вы можете использовать FULL OUTER JOIN:

SELECT * FROM table1, table2 WHERE table1.id = table2.id
UNION ALL
SELECT *, Null, Null FROM table1 WHERE Not table1.id In (SELECT id FROM table2)
UNION ALL
SELECT Null, Null, * FROM table2 WHERE Not table2.id In (SELECT id FROM table1)
--[OR] (recommended for SQLite)
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
UNION ALL
SELECT * FROM table2 LEFT JOIN table1 ON table2.id = table1.id
WHERE table1.id IS NULL
--[OR]
SELECT * FROM table1 FULL OUTER JOIN table2 On table1.id = table2.id

--[Results:]
id   | name | id   | name 
-----+------+------+------
1    | a1   | 1    | a2
2    | b1   | Null | Null
Null | Null | 3    | b2

Ну, так как вам нужно, вы выбираете каждый, который покрывает ваши потребности;).

    
39
2017-09-12 08: 50: 33Z
  1. Вы можете добавить к своей заметке, что в MySQL также нет full outer join.
    2015-06-14 13: 34: 13Z

Внутреннее объединение.

Объединение объединяет строки из двух таблиц. внутреннее объединение пытается сопоставить две таблицы на основе критериев, указанных в запросе, и возвращает только соответствующие строки. Если строка из первой таблицы в соединении совпадает с двумя строками во второй таблице, то в результатах будут возвращены две строки. Если в первой таблице есть строка, которая не соответствует строке во второй таблице, она не возвращается; аналогично, если во второй таблице есть строка, которая не соответствует строке в первой, она не возвращается.

Внешнее соединение.

left join пытается найти соответствие строк из первой таблицы и строк во второй таблице. Если он не может найти совпадение, он вернет столбцы из первой таблицы и оставит столбцы из второй таблицы пустыми (пустыми).

    
31
2016-02-02 11: 14: 00Z

В других ответах я не вижу подробностей о производительности и оптимизаторе.

Иногда полезно знать, что только INNER JOIN является ассоциативным, что означает, что оптимизатор имеет больше возможностей для игры с ним. Он может изменить порядок соединения, чтобы сделать его быстрее, сохраняя тот же результат. Оптимизатор может использовать большинство режимов соединения.

Как правило, рекомендуется использовать INNER JOIN вместо разных видов соединений. (Конечно, если это возможно, учитывая ожидаемый набор результатов.)

Здесь есть несколько хороших примеров и объяснений об этом странном ассоциативном поведении:

25
2017-05-23 12: 34: 48Z
  1. Не может быть "хорошей практикой" использовать один тип соединения поверх другого. Какое соединение вы используете, определяет данные, которые вы хотите. Если вы используете другой, вы не правы. Плюс, в Oracle, по крайней мере, этот ответ совершенно неверен. Это звучит совершенно неправильно для всех, и у вас нет никаких доказательств. Ты хадоказательство?
    2014-12-26 08: 51: 00Z
  2. 1. Я имею в виду стараюсь использовать. Я видел много людей, использующих LEFT OUTER соединения везде без какой-либо веской причины. (Объединенные столбцы были «не нулевыми».) В этих случаях было бы определенно лучше использовать INNER объединения. 2. Я добавил ссылку, объясняющую неассоциативное поведение лучше, чем я мог.
    2014-12-26 11: 01: 45Z
  3. Как я знаю, INNER JOIN медленнее, чем LEFT JOIN, в большинстве случаев, и люди могут использовать LEFT JOIN вместо INNER JOIN вместо 0600350505050505050 для других организаций. /DIV>
    2015-05-13 03: 01: 51Z
  4. Эти комментарии сделали меня немного неуверенным. Как вы думаете, почему WHERE медленнее?
    2015-05-13 08: 04: 42Z

Точный алгоритм для NULL, INNER следующий:

  1. Возьмите каждую строку из первой таблицы: INNER JOIN
  2. Рассмотрим все строки второй таблицы рядом с ним: LEFT/RIGHT OUTER JOIN
  3. Оцените предложение a для каждой пары: (a, b[i])
    • Когда условие оценивается как ON ..., верните эту объединенную строку ON( a, b[i] ) = true/false?.
    • Когда достигается конец второй таблицы без какого-либо совпадения, а это true, затем возвращается пара (виртуальная) , используя (a, b[i]) для всех столбцов другой таблицы: Outer Join для левого внешнего соединения или Null для REF. внешнее соединение. Это необходимо для того, чтобы все строки первой таблицы существовали в конечных результатах.

Примечание: условие, указанное в предложении (a, Null), может быть любым, использовать первичные ключи не обязательно (и вам не нужно всегда ссылаться на столбцы из обе таблицы)! Например:

 Внутреннее соединение против внешнего левого соединения

 введите описание изображения здесь

Примечание: левое соединение = левое внешнее соединение, правое соединение = правое внешнее соединение.

    
22
2018-10-11 14: 16: 16Z

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

Несмотря на то, что ответ @Martin Smith является лучшим из всей этой серии, он показывает только ключевые столбцы из каждой таблицы, в то время как я считаю, что в идеале также должны быть показаны неключевые столбцы.

Лучшее, что я мог сделать за полчаса, я все еще не думаю, что это адекватно показывает, что нулевые значения присутствуют из-за отсутствия значений ключа в ... ON T1.y IS NULL или что ... ON 1 = 0 на самом деле является объединением, а не объединением: р>

 введите описание изображения здесь

    
20
2016-01-22 15: 23: 20Z
  1. Вопрос заключается в разнице между соединениями INNER и OUTER, но не обязательно левым внешним соединением lol
    2016-01-22 19: 32: 48Z
  2. @ LearnByReading: моя картинка справа - подставкаht внешнее соединение, т.е. заменить TableB на OUTER JOIN
    2019-04-29 07: 58: 14Z

 введите описание изображения здесь

  • TableA a LEFT OUTER JOIN TableB b наиболее типичное объединение для двух или более таблиц. Возвращает совпадение данных как для таблицы ON primarykey, так и для отношения forignkey.
  • TableB B RIGHT OUTER JOIN TableA a совпадает с INNER JOIN, но также содержит данные OUTER JOIN для ResultSet.
    • INNER JOIN = NULL + Несоответствующие данные левой таблицы с совпадением LEFT JOIN на правой таблице.
    • INNER JOIN = Null + Несоответствующие данные таблицы справа с совпадением RIGHT JOIN на левой таблице.
    • INNER JOIN = Null + Несоответствующие данные в правой и левой таблицах с совпадениями FULL JOIN.
  • Self join не является ключевым словом в SQL, когда таблица ссылается на данные, которые сами по себе известны как self join. Используя INNER JOIN и Null, мы можем написать запросы на самостоятельное объединение.

Например:

INNER JOIN     
19
2019-03-21 22: 19: 48Z

Простейшие определения

Внутреннее объединение: возвращает совпадающие записи из обеих таблиц.

Полное внешнее объединение: возвращает сопоставленные и несопоставленные записи из обеих таблиц с нулевым значением для несопоставленных записей из обеих таблиц .

Левое внешнее соединение. Возвращает совпадающие и несопоставленные записи только из таблицы в левой стороне .

Правое внешнее объединение. Возвращает совпадающие и несопоставленные записи только из таблицы на правой стороне .

В-Short

Совпадение + Несоответствие слева + Несоответствие справа = Полное внешнее соединение

Совпадение + Левое несоответствие = Левое внешнее соединение

Совпадение + правое несоответствие = правое внешнее соединение

Соответствует = Внутреннее объединение

    
17
2016-04-28 09: 10: 48Z
  1. Это замечательно и объясняет, почему объединение не работает так, как ожидалось для индексов временных рядов. Отметки времени с интервалом в одну секунду не имеют аналогов.
    2017-09-05 09: 22: 46Z
  2. @ yeliabsalohcin Вы не объясняете "как ожидалось" здесь или "работает" в своем комментарии к вопросу. Это просто какое-то необъяснимое личное заблуждение, которое, как ни странно, ожидают другие. Если вы воспринимаете слова как небрежные, когда вы читаете - неправильно истолковываете ясное письмо и /или принимаете нечеткое письмо - как, когда вы пишете здесь, тогда вы можете ожидать ошибочных представлений. На самом деле этот ответ, как и большинство здесь, неясен & неправильно. «Внутреннее соединение: возвращает сопоставленные записи из обеих таблиц» неправильно, если наборы входных столбцов различаются. Он пытается сказать что-то определенное, но это не . (См. Мой ответ.)
    2017-11-22 02: 52: 49Z

Проще говоря,

1. ВНУТРЕННЕЕ СОЕДИНЕНИЕ ИЛИ СОЕДИНЕНИЕ EQUI: Возвращает набор результатов, который соответствует только условию в обеих таблицах.

2. ВНЕШНЕЕ СОЕДИНЕНИЕ . Возвращает набор результатов всех значений из обеих таблиц, даже если условие соответствует или нет.

3. LEFT JOIN: возвращает набор результатов всех значений из левой таблицы и только строк, соответствующих условию в правой таблице.

4. RIGHT JOIN: возвращает набор результатов всех значений из правой таблицы и только строк, соответствующих условию в левой таблице.

5. ПОЛНОЕ СОЕДИНЕНИЕ: полное и полное внешнее объединение совпадают.

    
6
2016-10-13 09: 04: 54Z

1. Внутреннее соединение: также называется присоединением. Он возвращает строки, присутствующие как в левой таблице, так и в правой таблице только , если есть совпадение . В противном случае он возвращает ноль записей.

Пример: р> OUTER JOIN

 output1

2. Полное внешнее соединение: также называется полным соединением. Он возвращает все строки , присутствующие как в левой, так и в правой таблице.

Пример: р>

SELECT * 
FROM   tablea a 
       INNER JOIN tableb b 
               ON a.primary_key = b.foreign_key 
       INNER JOIN tablec c 
               ON b.primary_key = c.foreign_key 

 output2

3. Левое внешнее соединение: Или просто называется левым соединением. Он возвращает все строки, присутствующие в левой таблице, и совпадающие строки из правой таблицы (если есть).

4. Правое внешнее соединение: также называется правым соединением. Он возвращает совпадающие строки из левой таблицы (если есть) и все строки, присутствующие в правой таблице.

 joins

Преимущества объединений

  1. Выполняется быстрее.
4
2017-07-28 04: 17: 49Z
  1. Это верно только в том случае, если для таблиц установлен одинаковый столбец. (Это путает внутреннее соединение с пересечением и полное соединение с объединением.) Также «соответствие» не определено. Прочитайте мои другие комментарии.
    2017-07-24 13: 02: 37Z
  • Внутреннее соединение - внутреннее соединение , использующее любой из эквивалентных запросов, дает пересечение двух таблиц , то есть двух у них есть общие строки.

  • Левое внешнее соединение - Левое внешнее соединение даст все строки в A, а также все общие строки в B.

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

3
2016-06-23 13: 55: 57Z
  1. Это и неправильно, и неясно. Соединение не является пересечением, если таблицы не имеют одинаковые столбцы. Внешние объединения не имеют строк из A или B, если они не имеют одинаковые столбцы, в этом случае не добавляются пустые значения. Вы пытаетесь что-то сказать, но вы этого не говорите. Вы не объясняете правильно или ясно.
    2017-02-03 11: 15: 30Z
  2. @ philipxy: не согласны с вашим утверждением
    SELECT
      e1.emp_name,
      e2.emp_salary    
    FROM emp1 e1
    INNER JOIN emp2 e2
      ON e1.emp_id = e2.emp_id
    
    Нет. Вы можете объединить любые столбцы, которые хотите, и, если значение совпадает, они объединятся.
    2017-04-11 09: 23: 49Z
  3. Этот комментарий так же неясен, как и ваш ответ. (Я полагаю, что вы можете подумать что-то вроде: набор значений subrow для общих столбцов результата - это пересечение наборов значений subrow для общих столбцов каждого из входных данных; но это не то, что вы написали. не понятно.)
    2017-04-11 14: 24: 05Z

SELECT
  e1.emp_name,
  e2.emp_salary    
FROM emp1 e1
FULL OUTER JOIN emp2 e2
  ON e1.emp_id = e2.emp_id
(он же Join is not an intersection unless the tables have the same columns) возвращает left join on строк left outer join on несопоставленных строк левой таблицы, расширенных нулями.

inner join on aka union all) возвращает right join (on строк right outer join on несоответствующих строк правой таблицы, расширенных нулями.

inner join on (он же union all) возвращает full join onх строкfull outer join on несоответствующие строки левой таблицы, расширенные нулями inner join on несоответствующие строки правой таблицы, расширенные нулями.

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

Так что не union all, пока вы не знаете, что лежит в основе union all.

Узнайте, какие строки возвращает outer join .

Прочитайте мои комментарии, там много запутанных & плохие ответы.

Тогда прочитайте мои комментарии здесь, многие из которых запутались & плохие ответы.

    
3
2018-07-23 05: 53: 22Z
  1. Я действительно прочитал ваши многочисленные комментарии. Когда вы говорите: «Диаграмма Венна, при правильной интерпретации, может представлять собой внутреннее или внешнее соединение», вы имеете в виду, когда она правильно интерпретируется наблюдателем или самой диаграммой Венна? Если последнее, пожалуйста, нарисуйте его:)
    2019-04-24 16: 08: 07Z
  2. Я не уверен, что вы пытаетесь сказать. Я говорю о стандартной интерпретации диаграммы Венна как наборов элементов. (Потому что некоторые виды использования диаграмм даже не управляют этим.) «Правильно» для приложения включает в себя указание, что такое наборы и /или элементы. См. Комментарий в верхней части этой страницы с 50 ответами на вопрос о диаграмме Венна для внутренних и внешних объединений. Я отредактирую некоторые из моих комментариев в этом вопросе. Мне не нужна диаграмма Венна в этом посте.
    2019-04-24 16: 31: 27Z
  3. Мне тоже не нужны диаграммы Венна!
    2019-04-25 15: 52: 29Z
  4. Я должен признать, что, несмотря на мои быстрые фразы в комментариях, потому что SQL включает в себя сумки & NULL и культура SQL не имеют общей терминологии для именования и разработки. Различая соответствующие понятия, нетривиально даже ясно объяснить, как элементы диаграммы Венна имеют 1: 1 с выходными «строками», не говоря уже о «строках». Или то, что делают внутренние или внешние соединения, не говоря уже об их разнице. «value» может включать или не включать NULL, «row» может быть списком значений против слота в табличном значении или переменной & "=" может быть SQL "=" против равенства.
    2019-04-25 17: 44: 05Z
  5. Как и в нашем обсуждении Cartesian-product-vs-реляционный-продукт, я подозреваю, что именно в этом случае диаграммы Венна имеют большой смысл для людей, которые уже понимают различия между типами соединения!
    2019-04-29 07: 51: 15Z

Разница между внутренним соединением и внешним соединением заключается в следующем:

  1. Внутреннее объединение - это объединение, объединяющее таблицы на основе соответствующих кортежей, тогда как внешнее объединение - это объединение, объединяющее таблицы на основе как сопоставленного, так и несогласованного кортежа.
  2. Внутреннее объединение объединяет совпавшую строку из двух таблиц, в которой пропущены несопоставленные строки, тогда как внешнее объединение объединяет строки из двух таблиц, и несопоставленные строки заполняются нулевым значением.
  3. Внутреннее соединение похоже на операцию пересечения, тогда как внешнее соединение похоже на операцию объединения.
  4. Внутреннее соединение - это два типа, а внешнее соединение - три типа.
  5. Внутреннее соединение медленнее, тогда как внешнее соединение быстрее внутреннего соединения.
1
2017-10-17 12: 25: 56Z

Рассмотрим ниже 2 таблицы:

EMP

inner join

Отдел

inner join

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

В основном пишется как JOIN в SQL-запросах. Он возвращает только совпадающие записи между таблицами.

Найти oвсе сотрудники и имена их отделов:

empid   name    dept_id salary
1       Rob     1       100
2       Mark    1       300
3       John    2       100
4       Mary    2       300
5       Bill    3       700
6       Jose    6       400

Как вы видели выше,

deptid  name
1       IT
2       Accounts
3       Security
4       HR
5       R&D
не выводится из EMP в выходных данных, поскольку его dept_id
Select a.empid, a.name, b.name as dept_name
FROM emp a
JOIN department b
ON a.dept_id = b.deptid
;

empid   name    dept_name
1       Rob     IT
2       Mark    IT
3       John    Accounts
4       Mary    Accounts
5       Bill    Security
не находит соответствия в таблице Department. Аналогично, строки Jose и 6 не печатаются из таблицы Отдел , поскольку они не нашли соответствия в таблице Emp.

Таким образом, INNER JOIN или просто JOIN возвращает только совпадающие строки.

LEFT JOIN:

Возвращает все записи из таблицы LEFT и только совпадающие записи из таблицы RIGHT.

HR

Итак, если вы наблюдаете вышеприведенный вывод, все записи из таблицы LEFT (Emp) будут напечатаны только с соответствующими записями из таблицы RIGHT.

Строки

R&D и

Select a.empid, a.name, b.name as dept_name
FROM emp a
LEFT JOIN department b
ON a.dept_id = b.deptid
;

empid   name    dept_name
1       Rob     IT
2       Mark    IT
3       John    Accounts
4       Mary    Accounts
5       Bill    Security
6       Jose    
не печатаются из таблицы Department , поскольку они не нашли соответствия в таблице Emp в dept_id.

Итак, LEFT JOIN возвращает ВСЕ строки из левой таблицы и только совпадающие строки из правой таблицы.

Также можно проверить DEMO здесь .

    
0
2018-11-25 15: 04: 40Z
  1. Ничто в этом документе четко не описывает, что делает любое из объединений. (И при этом это не обращается к «разнице» между ними, за исключением того, что они говорят, что они разные.) Это ничего не добавляет ко многим ответам (многие высоко одобренные) на 10-летний вопрос - это «полезно»?
    2018-11-27 01: 19: 10Z
HR
источник размещен Вот