таблицы - объединение таблиц sql




В чем разница между Left, Right, Outer и Inner Joins? (7)

Мне интересно, как отличить все эти разные объединения ...


Вот графический вид всех объединений, который дает ясные визуальные объяснения.

и здесь Visual-Representation-of-SQL-Joins подробно объясняется CL Moffatt


Есть только 4 вида:

  1. Внутреннее соединение : наиболее распространенный тип. Выходная строка создается для каждой пары входных строк, которые соответствуют условиям соединения.
  2. Левое внешнее соединение : то же, что и внутреннее соединение, за исключением того, что если есть строка, для которой не может быть найдена соответствующая строка в таблице справа, выводится строка, содержащая значения из таблицы слева, с NULL для каждое значение в таблице справа. Это означает, что каждая строка из таблицы слева появится как минимум на выходе.
  3. Правое внешнее соединение : то же, что и левое внешнее соединение, за исключением того, что роли таблиц меняются на противоположные.
  4. Полное внешнее соединение : комбинация левого и правого внешних соединений. Каждая строка из обеих таблиц будет отображаться на выходе хотя бы один раз.

«Кросс-соединение» или «декартовое соединение» - это просто внутреннее соединение, для которого не были указаны условия соединения, в результате чего выводятся все пары строк.

Спасибо RusselH за указание FULL joins, которое я пропустил.


Разница SQL JOINS:

Очень просто запомнить:

INNER JOIN показывает только записи, общие для обеих таблиц.

OUTER JOIN все содержимое обеих таблиц объединены вместе, либо они совпадают, либо нет.

LEFT JOIN такой же, как LEFT OUTER JOIN - (Выберите записи из первой (самой левой) таблицы с соответствующими записями правой таблицы.)

RIGHT JOIN - это то же самое, что и RIGHT OUTER JOIN - (Выберите записи из второй (самой правой) таблицы с соответствующими левыми табличными записями.)


Сделать его более заметным может помочь. Один пример:

Таблица 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

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

  • INNER объединяет две таблицы и возвращает результаты, в которых существует совпадение. Записи из 1-й таблицы дублируются, когда они соответствуют нескольким результатам во втором. Сочетания INNER, как правило, уменьшают количество наборов результатов, но поскольку записи могут быть дублированы, это не гарантируется.
  • CROSS объединяет две таблицы и возвращает все возможные комбинации строк из обеих таблиц. Вы можете получить много результатов от такого рода объединений, которые могут даже не иметь смысла, поэтому используйте с осторожностью.
  • OUTER объединяет две таблицы и возвращает данные, когда доступно совпадение, или значения NULL в противном случае. Как и при соединении INNER, это будет дублировать строки в одной таблице, когда оно соответствует нескольким записям в другой таблице. Соединения OUTER, как правило, увеличивают количество наборов результатов, поскольку они сами по себе не удаляют записи из набора. Вы также должны квалифицировать соединение OUTER, чтобы определить, когда и где добавить значения NULL:
    • LEFT означает, что все записи из 1-й таблицы не имеют значения и вставляют значения NULL, когда вторая таблица не соответствует.
    • RIGHT означает противоположное: сохраняйте все записи со второй таблицы независимо от того, что и вставляйте значения NULL, когда он не соответствует 1-й таблице.
    • FULL означает сохранить все записи из обеих таблиц и вставить значение NULL в обе таблицы, если нет совпадения.

Часто вы увидите, что ключевое слово OUTER будет опущено из синтаксиса. Вместо этого это будет просто «LEFT JOIN», «RIGHT JOIN» или «FULL JOIN». Это делается потому, что соединения INNER и CROSS не имеют никакого значения в отношении LEFT, RIGHT или FULL, и поэтому они достаточны сами по себе, чтобы однозначно указать соединение OUTER.

Вот пример того, когда вы можете использовать каждый тип:

  • INNER : вы хотите вернуть все записи из таблицы «Счет» вместе с соответствующими «InvoiceLines». Это предполагает, что каждый действительный счет-фактура будет иметь по крайней мере одну строку.
  • OUTER : вы хотите вернуть все записи «InvoiceLines» для конкретного счета-фактуры вместе со своими соответствующими записями «InventoryItem». Это бизнес, который также продает услугу, так что не все InvoiceLines будут иметь IventoryItem.
  • CROSS : У вас есть таблица цифр с 10 строками, каждая из которых имеет значения от 0 до 9. Вы хотите создать таблицу диапазонов дат для объединения, так что вы получите одну запись за каждый день в пределах диапазона. Посредством CROSS-присоединения к этой таблице с собой многократно вы можете создать столько последовательных целых чисел, сколько вам нужно (при условии, что вы начинаете с 10 до 1-й степени, каждое соединение добавляет 1 к экспоненте). Затем используйте функцию DATEADD (), чтобы добавить эти значения в базовую дату для диапазона.

LEFT JOIN и RIGHT JOIN - это типы OUTER JOIN .

INNER JOIN по умолчанию - строки из обеих таблиц должны соответствовать условию объединения.


Простой пример : Допустим, у вас есть таблица « Students и таблица « Lockers . В SQL первая таблица, указанная вами в соединении, - это таблица LEFT , а вторая, Lockers , является таблицей RIGHT .

Каждый студент может быть назначен в шкафчик, поэтому в таблице Student LockerNumber столбец LockerNumber . Более одного ученика потенциально могут находиться в одном шкафчике, но особенно в начале учебного года, у вас могут быть несколько студентов, не имеющих шкафчиков, и некоторые шкафчики, у которых нет назначенных учеников.

Ради этого примера, скажем, у вас 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 в таблице учеников, поэтому вы в основном получаете большой гигантский список всех возможных спариваний между lockernumber , независимо от того, действительно ли он существует.
Возвращает 5000 строк (100 студентов x 50 шкафчиков). Может быть полезно (с фильтрацией) в качестве отправной точки, чтобы соответствовать новым ученикам с пустыми шкафчиками.





join