В чем разница между левым, правым, внешним и внутренним соединениями?
Мне интересно, как дифференцировать все эти разные соединения...
10 ответов
Простой пример: допустим, у вас есть Students стол и Lockers Таблица. В SQL первая таблица, которую вы указываете в соединении, Students, это левая таблица, а вторая, Lockers, это правая таблица.
Каждый студент может быть назначен на шкафчик, так что есть LockerNumber столбец в Student Таблица. В одном шкафчике может находиться более одного ученика, но особенно в начале учебного года у вас могут быть некоторые поступающие ученики без шкафчиков и некоторые шкафчики, которым не назначены ученики.
Для примера давайте предположим, что у вас 100 учеников, 70 из которых имеют запирающиеся шкафчики. Всего у вас 50 шкафчиков, в 40 из которых есть как минимум 1 студент, а в 10 шкафах нет студентов.
INNER JOIN эквивалентно "покажи мне всех учеников со шкафчиками".
Любые студенты без шкафчиков или любые шкафчики без студентов отсутствуют.
Возвращает 70 строк
LEFT OUTER JOIN будет "показывать мне всех студентов с соответствующими шкафчиками, если они у них есть".
Это может быть общий список студентов или может использоваться для идентификации студентов без шкафчика.
Возвращает 100 строк
RIGHT OUTER JOIN будет "показывать мне все шкафчики и назначенных им учеников, если они есть".
Это может быть использовано для идентификации шкафчиков, у которых нет назначенных учеников, или шкафчиков, у которых слишком много учеников.
Возвращает 80 строк (список из 70 учеников в 40 шкафчиках плюс 10 шкафчиков без ученика)
FULL OUTER JOIN будет глупым и, вероятно, не очень полезным.
Что-то вроде "покажи мне всех студентов и все шкафчики и сопоставь их, где сможешь"
Возвращает 110 строк (все 100 учеников, в том числе без шкафчиков. Плюс 10 шкафчиков без учеников)
CROSS JOIN также довольно глупо в этом сценарии.
Он не использует связанный lockernumber поле в таблице учеников, так что в итоге вы получите большой список всех возможных парных связей ученика и шкафчика, независимо от того, существует ли он на самом деле.
Возвращает 5000 строк (100 студентов х 50 шкафчиков). Может быть полезным (с фильтрацией) в качестве отправной точки для сопоставления новых учеников с пустыми шкафчиками.
Вот графическое представление всех соединений, которое дает четкие визуальные объяснения.
а здесь визуальное представление SQL-соединений подробно объясняется CL Моффатом
Существует три основных типа соединения:
INNERОбъединение сравнивает две таблицы и возвращает результаты только при наличии совпадения. Записи из первой таблицы дублируются, когда они совпадают с несколькими результатами во второй таблице. Соединения INNER, как правило, уменьшают наборы результатов, но поскольку записи могут дублироваться, это не гарантируется.CROSSОбъединение сравнивает две таблицы и возвращает все возможные комбинации строк из обеих таблиц. Вы можете получить много результатов от такого рода объединений, которые могут даже не иметь смысла, поэтому используйте их с осторожностью.OUTERОбъединение сравнивает две таблицы и возвращает данные, когда сопоставление доступно или значения NULL в противном случае. Как и в случае с INNER join, это приведет к дублированию строк в одной таблице, когда совпадет с несколькими записями в другой таблице. Объединения OUTER обычно увеличивают наборы результатов, поскольку сами по себе не удаляют записи из набора. Вы также должны квалифицировать OUTER соединение, чтобы определить, когда и где добавлять значения NULL:LEFTозначает сохранить все записи из 1-й таблицы независимо от того, что и вставлять значения NULL, когда 2-я таблица не совпадает.RIGHTозначает обратное: сохранить все записи из 2-й таблицы независимо от того, что они есть, и вставить значения NULL, если 1-я таблица не совпадает.FULLозначает сохранить все записи из обеих таблиц и вставить значение NULL в любую из таблиц, если совпадений нет.
Часто вы видите, будет OUTER ключевое слово опущено в синтаксисе. Вместо этого это будет просто "LEFT JOIN", "RIGHT JOIN" или "FULL JOIN". Это сделано потому, что соединения INNER и CROSS не имеют смысла в отношении LEFT, RIGHT или FULL, и поэтому сами по себе этого достаточно для однозначного указания соединения OUTER.
Вот пример того, когда вы можете использовать каждый тип:
INNER: Вы хотите вернуть все записи из таблицы "Счета-фактуры" вместе с соответствующими "линиями счетов-фактур". Это предполагает, что каждый действительный Счет будет иметь по крайней мере одну строку.OUTER: Вы хотите вернуть все записи "InvoiceLines" для конкретного счета-фактуры вместе с соответствующими записями "InventoryItem". Это бизнес, который также продает сервис, так что не все InvoiceLines будут иметь IventoryItem.CROSS: У вас есть таблица цифр с 10 строками, каждая из которых содержит значения от "0" до "9". Вы хотите создать таблицу диапазонов дат, к которой нужно присоединиться, чтобы в результате вы получили одну запись на каждый день в пределах диапазона. Соединяя эту таблицу CROSS с собой несколько раз, вы можете создать столько последовательных целых чисел, сколько вам нужно (если вы начинаете с 10 до 1 степени, каждое соединение добавляет 1 к показателю степени). Затем используйте функцию DATEADD(), чтобы добавить эти значения к базовой дате для диапазона.
Есть только 4 вида:
- Внутреннее соединение: самый распространенный тип. Выходная строка создается для каждой пары входных строк, соответствующих условиям соединения.
- Внешнее левое соединение: то же самое, что и внутреннее соединение, за исключением того, что если есть какая-либо строка, для которой не может быть найдено ни одной соответствующей строки в таблице справа, выводится строка, содержащая значения из таблицы слева, с
NULLдля каждого значения в таблице справа. Это означает, что каждая строка из таблицы слева появится хотя бы один раз в выходных данных. - Правое внешнее соединение: то же самое, что и левое внешнее соединение, за исключением случаев, когда роли таблиц меняются местами.
- Полное внешнее соединение: комбинация левого и правого внешних соединений. Каждая строка из обеих таблиц появится в выходных данных хотя бы один раз.
"Перекрестное соединение" или "декартово соединение" - это просто внутреннее соединение, для которого не заданы условия соединения, в результате чего выводятся все пары строк.
Спасибо RusselH за указание ПОЛНЫХ соединений, которые я пропустил.
SQL присоединяется к разнице:
Очень просто запомнить:
INNER JOIN показывать только записи, общие для обеих таблиц.
OUTER JOIN все содержимое обеих таблиц объединено, либо они совпадают, либо нет.
LEFT JOIN такой же как LEFT OUTER JOIN - (Выберите записи из первой (самой левой) таблицы с соответствующими записями правой таблицы.)
RIGHT JOIN такой же как RIGHT OUTER JOIN - (Выберите записи из второй (самой правой) таблицы с соответствующими записями левой таблицы.)
Проверьте Join (SQL) в Википедии
- Внутреннее соединение - при наличии двух таблиц внутреннее соединение возвращает все строки, которые существуют в обеих таблицах
левое / правое (внешнее) объединение - данные две таблицы возвращают все строки, которые существуют в левой или правой таблице вашего объединения, плюс строки с другой стороны будут возвращены, когда предложение соединения соответствует, или для NULL будет возвращено значение эти столбцы
Full Outer - две таблицы возвращают все строки и возвращают нули, когда ни левого, ни правого столбца нет
Cross Joins - Декартово соединение и может быть опасным, если его не использовать осторожно
Сделать это более заметным может помочь. Один пример:
Таблица 1:
ID_STUDENT STUDENT_NAME
1 Raony
2 Diogo
3 Eduardo
4 Luiz
Таблица 2:
ID_STUDENT LOCKER
3 l1
4 l2
5 l3
Что я получаю, когда я делаю:
-Inner join of Table 1 and Table 2:
- Inner join returns both tables merged only when the key
(ID_STUDENT) exists in both tables
ID_STUDENT STUDENT_NAME LOCKER
3 Eduardo l1
4 Luiz l2
-Left join of Table 1 and Table 2:
- Left join merges both tables with all records form table 1, in
other words, there might be non-populated fields from table 2
ID_ESTUDANTE NOME_ESTUDANTE LOCKER
1 Raony -
2 Diogo -
3 Eduardo l1
4 Luiz l2
-Right join of table 1 and table 2:
- Right join merges both tables with all records from table 2, in
other words, there might be non-populated fields from table 1
ID_STUDENT STUDENT_NAME LOCKER
3 Eduardo l1
4 Luiz l2
5 - l3
-Outter join of table 1 and table 2:
- Returns all records from both tables, in other words, there
might be non-populated fields either from table 1 or 2.
ID_STUDENT STUDENT_NAME LOCKER
1 Raony -
2 Diogo -
3 Eduardo l1
4 Luiz l2
5 - l3
LEFT JOIN а также RIGHT JOIN типы OUTER JOINs.
INNER JOIN по умолчанию - строки из обеих таблиц должны соответствовать условию соединения.
Внутреннее соединение: показывать только строки, когда есть данные из обеих таблиц.
Внешнее объединение: (влево / вправо): Показать все результаты из левой / правой таблицы с парными строками, если они существуют или нет.
Сначала вы должны понять, что делает join? Мы соединяем несколько таблиц и получаем конкретный результат из объединенных таблиц. Самый простой способ сделать это - перекрестное соединение.
Допустим, таблица A имеет два столбца A и B. И таблица B содержит три столбца C и D. Если мы применим перекрестное соединение, то получится много бессмысленных строк. Затем мы должны сопоставить, используя первичный ключ, чтобы получить фактические данные.
Слева: он вернет все записи из левой таблицы и сопоставленные записи из правой таблицы.
Справа: он вернется напротив левого соединения. Он вернет все записи из правой таблицы и сопоставленные записи из левой таблицы.
Внутренний: это как пересечение. Он будет возвращать только совпадающие записи из обеих таблиц.
Наружно: И это как союз. Он вернет все доступные записи из обеих таблиц.
Иногда нам не нужны все данные, а также нам нужны только общие данные или записи. мы можем легко получить это, используя эти методы соединения. Помните, что левое и правое соединение также являются внешним соединением.
Вы можете получить все записи, просто используя перекрестное соединение. Но это может быть дорого, когда дело доходит до миллионов записей. Поэтому сделайте это проще, используя левое, правое, внутреннее или внешнее соединение.
Спасибо