sql - разница - отличие left join от left outer join




В чем разница между «INNER JOIN» и «OUTER JOIN»? (20)

Также как LEFT JOIN , RIGHT JOIN И FULL JOIN подходят?


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

Они являются наиболее часто используемыми экзистенциальными операторами в SQL, где INNER JOINиспользуется для «существует» и LEFT OUTER JOINиспользуется для «не существует».

Рассмотрим эти запросы:

users who have posted and have votes
users who have posted but have no badges

Люди, которые ищут комплексные решения (отраслевой термин), распознают соответствующие запросы как:

users who have posted INTERSECT users who have votes
users who have posted MINUS users who have badges

Переведя их в стандартный SQL:

SELECT UserId FROM Posts
INTERSECT 
SELECT UserId FROM Votes;

SELECT UserId FROM Posts
EXCEPT 
SELECT UserId FROM Badges;

Другие будут думать так же:

users who have posted and IN the set of users who have votes
users who have posted and NOT IN the set of users who have badges

Переведя их в стандартный SQL:

SELECT UserId 
  FROM Posts
 WHERE UserId IN ( SELECT UserId FROM Votes );

SELECT UserId 
  FROM Posts
 WHERE UserId NOT IN ( SELECT UserId FROM Badges );

Некоторые будут думать в терминах «существования» внутри множеств, например

users who have posted and EXIST in the set of users who have votes
users who have posted and do NOT EXIST in the set of users who have badges

Воплощение их в стандартный SQL (обратите внимание , мы теперь должны использовать переменный диапазон т.е. p, v, b):

SELECT p.UserId 
  FROM Posts p
 WHERE EXISTS ( SELECT *
                  FROM Votes v
                 WHERE v.UserId = p.UserId );

SELECT p.UserId 
  FROM Posts p
 WHERE NOT EXISTS ( SELECT *
                      FROM Badges b
                     WHERE b.UserId = p.UserId );

Однако я обнаружил, что подход «отраслевой стандарт» заключается в том, чтобы использовать только объединения. Я не знаю, что думает здесь ( Закон инструмента ? Преждевременная оптимизация ?), Поэтому я перейду к синтаксису:

SELECT p.UserId 
  FROM Posts p
       INNER JOIN Votes v ON v.UserId = p.UserId;

SELECT p.UserId 
  FROM Posts p
       LEFT JOIN Badges b ON b.UserId = p.UserId
 WHERE b.UserId IS NULL;

Что нужно отметить:

  • Только проекция из , Usersно мы по- прежнему нужны все эти переменные диапазона ( p, v, b) для условий поиска.
  • UserId IS NULLУсловие поиска «принадлежит» к OUTER JOINно отключается в запросе.
  • LEFTявляется отраслевым стандартом: профессионалы переписывают запрос, чтобы избежать использования RIGHT!
  • OUTERКлючевое слово из LEFT OUTER JOINопущено.

Заключительные замечания:

Иногда объединения используются в запросах исключительно для определения того, существуют ли значения или не существуют в другом наборе. Научитесь внимательно смотреть на проецируемые атрибуты (столбцы в SELECTпредложении): если из объединенной таблицы их нет, они просто используются в качестве экзистенциальных операторов. Кроме того, для внешнего соединения найдите экземпляры <key_column> IS NULLв WHEREпредложении.


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

Извлеките только согласованные строки, то есть 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

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


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

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

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

заказы

  • Номер заказа
  • Имя покупателя

Информация для заказа

  • OrderDetailID
  • Номер заказа
  • Наименование товара
  • Кол-во
  • Цена

Запрос

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

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

Если вы измените его на ВНЕШНЮЮ ВЛЕВО

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

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

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


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

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

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

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

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

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

объяснение

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

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

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

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

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

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

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

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

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

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

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

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 для правые столбцы.

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 для этого столбца в дополнение к несогласованным строкам.

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

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

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

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

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

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

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

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

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

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

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

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

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

SQL Fiddle

См. Эти примеры в реальном времени на SQLFiddle.com .


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

  • Внутреннее объединение 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

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

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

Левое соединение ->

псевдокод

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 в результате объединения.

Пример :

Предположим теперь, что для двух таблиц

1.employees , 2.phone_numbers_employees

employees : id , name 

phone_numbers_employees : id , phone_num , emp_id   

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

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

Принимать записи из 2 таблиц ТОЛЬКО ЕСЛИ Первичный ключ таблицы employee (его идентификатор) соответствует внешнему ключу таблицы Child_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;

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

Левые соединения :

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

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;

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


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

Источник: Visual-Representation-of-SQL-Joins подробно описанный CL Moffatt


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

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

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



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

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

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

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


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

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

Внешняя связь.

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


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

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

Существует три типа внешних соединений:

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


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

Внутреннее соединение: - Внутреннее соединение создает новую таблицу результатов путем объединения значений столбцов двух таблиц ( 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:

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

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

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

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

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

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

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

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

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

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

Операции Oracle Join


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

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

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

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


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

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

Лучшее, что я мог сделать за полчаса, я до сих пор не думаю, что он адекватно показывает, что нули есть из-за отсутствия ключевых значений в TableBили что OUTER JOINна самом деле является объединением, а не объединением:


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

Примечание:
В 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

INNER JOIN:
Если вы хотите добавить фильтр к приведенным выше результатам на основе отношения, которое 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 [OUTER] JOIN:
Если вы хотите иметь все строки одной из таблиц в приведенном выше результате - с одним и тем же отношением, вы можете использовать LEFT JOIN:
(для RIGHT 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:
Когда вы также хотите, чтобы все строки другой таблицы в ваших результатах вы могли использовать 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

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


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

  • JOINтак же, как INNER JOINи означает показывать только записи, общие для обеих таблиц. Независимо от того, являются ли записи обычными, определяются поля в предложении join. Например:

    FROM t1
    JOIN t2 on t1.ID = t2.ID
    

    означает показывать только записи, где одно и то же IDзначение существует в обеих таблицах.

  • LEFT JOINтакая же, как LEFT OUTER JOINи означает показывать все записи из левой таблицы (т.е. той, которая предшествует в инструкции SQL), независимо от существования совпадающих записей в правой таблице.

  • RIGHT JOINто же самое, что RIGHT OUTER JOINи означает противоположность LEFT JOIN, т. е. показывает все записи из второй (правой) таблицы и только совпадающие записи из первой (левой) таблицы.

Источник: В чем разница между LEFT, RIGHT, INNER, OUTER, JOIN?


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

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

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

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

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

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


Точный алгоритм INNER JOIN, LEFT/RIGHT OUTER JOINявляются следующие:

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

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

  • ... ON T1.title = T2.title AND T1.version < T2.version(=> см. этот пост в качестве примера использования: выберите только строки с максимальным значением в столбце )
  • ... ON T1.y IS NULL
  • ... ON 1 = 0 (как образец)

Примечание: Left Join = Left Outer Join, Right Join = Right Outer Join.







outer-join