Присоединиться (SQL) - Join (SQL)

An SQL присоединиться пункт - соответствующий операция соединения в реляционной алгебре - комбайны столбцы от одного или нескольких столы в отношениях база данных. Он создает набор, который можно сохранить как таблицу или использовать как есть. А ПРИСОЕДИНИТЬСЯ средство для объединения столбцы из одной (самосоединение) или нескольких таблиц, используя общие для каждой таблицы значения. ANSI -стандартный SQL определяет пять типов ПРИСОЕДИНИТЬСЯ: ВНУТРЕННИЙ, СЛЕВА ВНЕШНИЙ, СПРАВА НАРУЖНЫЙ, ПОЛНЫЙ НАРУЖНЫЙ и ПЕРЕСЕКАТЬ. Как частный случай, таблица (базовая таблица, Посмотреть, или объединенная таблица) может ПРИСОЕДИНИТЬСЯ себе в самостоятельное присоединение.

Программист объявляет ПРИСОЕДИНИТЬСЯ оператор для определения строк для объединения. Если оцененный предикат истинен, объединенная строка создается в ожидаемом формате, в наборе строк или во временной таблице.

Образцы таблиц

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

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

Таблица сотрудников

Фамилия	DepartmentID
Рафферти	31
Джонс	33
Гейзенберг	33
Робинсон	34
Смит	34
Уильямс	НОЛЬ

Таблица отдела

DepartmentID	Название отдела
31	Продажи
33	Инженерное дело
34	Канцелярский
35	Маркетинг

Примечание. В приведенной выше таблице «Сотрудники» сотрудник «Вильямс» еще не назначен ни в один отдел. Также обратите внимание, что в отдел «Маркетинг» нет сотрудников.

Это оператор SQL для создания вышеупомянутых таблиц.

 1СОЗДАЙТЕ СТОЛ отделение( 2    DepartmentID INT НАЧАЛЬНЫЙ КЛЮЧ НЕТ НОЛЬ, 3    Название отдела VARCHAR(20) 4); 5 6СОЗДАЙТЕ СТОЛ наемный рабочий ( 7    Фамилия VARCHAR(20), 8    DepartmentID INT РЕКОМЕНДАЦИИ отделение(DepartmentID) 9);1011ВСТАВЛЯТЬ В отделение12ЗНАЧЕНИЯ (31, 'Продажи'),13       (33, «Инжиниринг»),14       (34, 'Канцелярский'),15       (35, «Маркетинг»);1617ВСТАВЛЯТЬ В наемный рабочий18ЗНАЧЕНИЯ ('Рафферти', 31),19       ('Джонс', 33),20       ('Гейзенберг', 33),21       ('Робинзон', 34),22       ('Смит', 34),23       ('Уильямс', НОЛЬ);

Перекрестное соединение

CROSS JOIN возвращает Декартово произведение строк из таблиц в объединении. Другими словами, он будет создавать строки, которые объединяют каждую строку из первой таблицы с каждой строкой из второй таблицы.^[1]

Пример явного перекрестного соединения:

ВЫБРАТЬ *ИЗ наемный рабочий ПЕРЕСЕКАТЬ ПРИСОЕДИНИТЬСЯ отделение;

Пример неявного перекрестного соединения:

ВЫБРАТЬ *ИЗ наемный рабочий, отделение;

Перекрестное соединение можно заменить внутренним соединением с условием Always-true:

ВЫБРАТЬ *ИЗ наемный рабочий ВНУТРЕННИЙ ПРИСОЕДИНИТЬСЯ отделение НА 1=1;

Employee.LastName	Employee.DepartmentID	Department.DepartmentName	Department.DepartmentID
Рафферти	31	Продажи	31
Джонс	33	Продажи	31
Гейзенберг	33	Продажи	31
Смит	34	Продажи	31
Робинсон	34	Продажи	31
Уильямс	НОЛЬ	Продажи	31
Рафферти	31	Инженерное дело	33
Джонс	33	Инженерное дело	33
Гейзенберг	33	Инженерное дело	33
Смит	34	Инженерное дело	33
Робинсон	34	Инженерное дело	33
Уильямс	НОЛЬ	Инженерное дело	33
Рафферти	31	Канцелярский	34
Джонс	33	Канцелярский	34
Гейзенберг	33	Канцелярский	34
Смит	34	Канцелярский	34
Робинсон	34	Канцелярский	34
Уильямс	НОЛЬ	Канцелярский	34
Рафферти	31	Маркетинг	35
Джонс	33	Маркетинг	35
Гейзенберг	33	Маркетинг	35
Смит	34	Маркетинг	35
Робинсон	34	Маркетинг	35
Уильямс	НОЛЬ	Маркетинг	35

Само по себе перекрестное соединение не применяет никаких предикатов для фильтрации строк из объединенной таблицы. Результаты перекрестного соединения можно отфильтровать с помощью КУДА предложение, которое затем может произвести эквивалент внутреннего соединения.

в SQL: 2011 Стандартные перекрестные соединения являются частью дополнительного пакета F401 «Расширенная соединенная таблица».

Обычное использование предназначено для проверки производительности сервера.

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

Диаграмма Венна, представляющая оператор SQL Inner Join между таблицами A и B.

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

Результат объединения можно определить как результат первого взятия Декартово произведение (или же Перекрестное соединение ) всех строк в таблицах (объединяя каждую строку в таблице A с каждой строкой в ​​таблице B), а затем возвращая все строки, которые удовлетворяют предикату соединения. Фактические реализации SQL обычно используют другие подходы, такие как хеш присоединяется или же соединения сортировки-слияния, поскольку вычисление декартова произведения происходит медленнее и часто требует чрезмерно большого объема памяти для хранения.

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

«Явная нотация соединения» использует ПРИСОЕДИНИТЬСЯ ключевое слово, которому может предшествовать ВНУТРЕННИЙ ключевое слово, чтобы указать таблицу, к которой нужно присоединиться, и НА ключевое слово, чтобы указать предикаты для соединения, как в следующем примере:

ВЫБРАТЬ наемный рабочий.Фамилия, наемный рабочий.DepartmentID, отделение.Название отдела ИЗ наемный рабочий ВНУТРЕННИЙ ПРИСОЕДИНИТЬСЯ отделение НАнаемный рабочий.DepartmentID = отделение.DepartmentID;

Employee.LastName	Employee.DepartmentID	Department.DepartmentName
Робинсон	34	Канцелярский
Джонс	33	Инженерное дело
Смит	34	Канцелярский
Гейзенберг	33	Инженерное дело
Рафферти	31	Продажи

«Нотация неявного соединения» просто перечисляет таблицы для соединения в ИЗ пункт ВЫБРАТЬ заявление, разделяя их запятыми. Таким образом, он определяет перекрестное соединение, а КУДА Предложение может применять дополнительные предикаты-фильтры (которые работают аналогично предикатам соединения в явной нотации).

Следующий пример эквивалентен предыдущему, но на этот раз с использованием неявной нотации соединения:

ВЫБРАТЬ наемный рабочий.Фамилия, наемный рабочий.DepartmentID, отделение.Название отдела ИЗ наемный рабочий, отделениеКУДА наемный рабочий.DepartmentID = отделение.DepartmentID;

Запросы, приведенные в приведенных выше примерах, будут соединяться с таблицами Employee и Department с использованием столбца DepartmentID обеих таблиц. Если идентификаторы отдела этих таблиц совпадают (т. Е. Удовлетворяется предикат соединения), запрос объединит Фамилия, DepartmentID и Название отдела столбцы из двух таблиц в строку результатов. Если DepartmentID не совпадает, строка результатов не создается.

Таким образом, результат исполнение запроса выше будет:

Employee.LastName	Employee.DepartmentID	Department.DepartmentName
Робинсон	34	Канцелярский
Джонс	33	Инженерное дело
Смит	34	Канцелярский
Гейзенберг	33	Инженерное дело
Рафферти	31	Продажи

Сотрудник «Вильямс» и отдел «Маркетинг» не отображаются в результатах выполнения запроса. Ни у одного из них нет совпадающих строк в другой соответствующей таблице: «Williams» не имеет связанного отдела, и ни один сотрудник не имеет идентификатора отдела 35 («Маркетинг»). В зависимости от желаемых результатов такое поведение может быть тонкой ошибкой, которой можно избежать, заменив внутреннее соединение на внешнее соединение.

Внутреннее соединение и значения NULL

Программисты должны проявлять особую осторожность при объединении таблиц по столбцам, которые могут содержать НОЛЬ значений, поскольку NULL никогда не будет соответствовать никакому другому значению (даже самому NULL), если только условие соединения явно не использует предикат комбинации, который сначала проверяет, что столбцы соединения НЕ НОЛЬ перед применением оставшихся условий предиката. Внутреннее соединение можно безопасно использовать только в базе данных, которая ссылочная целостность или где столбцы соединения гарантированно не равны NULL. Много обработка транзакции реляционные базы данных полагаются на Атомарность, последовательность, изоляция, долговечность (ACID) стандарты обновления данных для обеспечения целостности данных, что делает внутренние соединения правильным выбором. Однако базы данных транзакций обычно также имеют желательные столбцы соединения, которым разрешено быть NULL. Многие реляционные базы данных отчетов и хранилища данных использовать большой объем Извлечь, преобразовать, загрузить (ETL) пакетные обновления, которые затрудняют или делают невозможным применение ссылочной целостности, что приводит к потенциально NULL-столбцам соединения, которые автор запроса SQL не может изменить, и которые заставляют внутренние соединения пропускать данные без указания на ошибку. Выбор использования внутреннего соединения зависит от дизайна базы данных и характеристик данных. Левое внешнее соединение обычно может быть заменено внутренним соединением, когда столбцы соединения в одной таблице могут содержать значения NULL.

Любой столбец данных, который может иметь значение NULL (пустой), никогда не должен использоваться в качестве ссылки во внутреннем соединении, если только предполагаемый результат не состоит в удалении строк со значением NULL. Если столбцы соединения NULL должны быть намеренно удалены из набор результатов, внутреннее соединение может быть быстрее, чем внешнее соединение, поскольку объединение таблиц и фильтрация выполняются за один шаг. И наоборот, внутреннее соединение может привести к катастрофически низкой производительности или даже к сбою сервера при использовании в запросе большого объема в сочетании с функциями базы данных в предложении SQL Where.^[2][3][4] Функция в предложении SQL Where может привести к игнорированию базой данных относительно компактных индексов таблиц. База данных может считывать выбранные столбцы из обеих таблиц и объединять их внутри перед уменьшением количества строк с помощью фильтра, который зависит от вычисленного значения, что приводит к относительно огромному объему неэффективной обработки.

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

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

Кроме того, внутренние соединения можно классифицировать как эквивалентные соединения, как естественные соединения или как перекрестные соединения.

Equi-join

An равное соединение это особый тип соединения на основе компаратора, который использует только равенство сравнения в предикате соединения. Использование других операторов сравнения (например, <) дисквалифицирует соединение как равное соединение. Запрос, показанный выше, уже предоставил пример равного соединения:

ВЫБРАТЬ *ИЗ наемный рабочий ПРИСОЕДИНИТЬСЯ отделение  НА наемный рабочий.DepartmentID = отделение.DepartmentID;

Мы можем написать equi-join, как показано ниже,

ВЫБРАТЬ *ИЗ наемный рабочий, отделениеКУДА наемный рабочий.DepartmentID = отделение.DepartmentID;

Если столбцы в равном объединении имеют одинаковое имя, SQL-92 предоставляет необязательную сокращенную запись для выражения эквисоединений посредством С ПОМОЩЬЮ построить:^[5]

ВЫБРАТЬ *ИЗ наемный рабочий ВНУТРЕННИЙ ПРИСОЕДИНИТЬСЯ отделение С ПОМОЩЬЮ (DepartmentID);

В С ПОМОЩЬЮ конструкция - это больше, чем просто синтаксический сахар однако, поскольку набор результатов отличается от набора результатов версии с явным предикатом. В частности, любые столбцы, упомянутые в С ПОМОЩЬЮ list будет отображаться только один раз с неполным именем, а не один раз для каждой таблицы в объединении. В приведенном выше случае будет один DepartmentID столбец и нет employee.DepartmentID или же Department.DepartmentID.

В С ПОМОЩЬЮ Предложение не поддерживается MS SQL Server и Sybase.

Естественное соединение

Естественное соединение - это частный случай равносоединения. Естественное соединение (⋈) - это бинарный оператор который записывается как (р ⋈ S) куда р и S находятся связи.^[6] Результатом естественного соединения является набор всех комбинаций кортежи в р и S которые совпадают по своим общим именам атрибутов. Для примера рассмотрим таблицы Наемный рабочий и Отдел и их естественное соединение:

Наемный рабочий Имя EmpId DeptName Гарри 3415 Финансы Салли 2241 Продажи Джордж 3401 Финансы Харриет 2202 Продажи

Отдел DeptName Управляющий делами Финансы Джордж Продажи Харриет Производство Чарльз

Наемный рабочий ${\displaystyle \bowtie }$ Отдел Имя EmpId DeptName Управляющий делами Гарри 3415 Финансы Джордж Салли 2241 Продажи Харриет Джордж 3401 Финансы Джордж Харриет 2202 Продажи Харриет

Это также можно использовать для определения состав отношений. Например, состав Наемный рабочий и Отдел их соединение, как показано выше, проецируется на все, кроме общего атрибута DeptName. В теория категорий, соединение - это в точности волокнистый продукт.

Естественное соединение, возможно, является одним из наиболее важных операторов, поскольку оно является реляционным аналогом логического И. Обратите внимание, что если одна и та же переменная появляется в каждом из двух предикатов, связанных оператором AND, то эта переменная обозначает одно и то же, и оба появления всегда должны заменяться одним и тем же значением. В частности, естественное соединение позволяет комбинировать отношения, которые связаны иностранный ключ. Например, в приведенном выше примере внешний ключ, вероятно, хранится от Наемный рабочий.DeptName к Отдел.DeptName а затем естественное соединение Наемный рабочий и Отдел объединяет всех сотрудников со своими отделами. Это работает, потому что внешний ключ хранится между атрибутами с тем же именем. Если это не так, например, во внешнем ключе из Отдел.управляющий делами к Наемный рабочий.Имя тогда эти столбцы необходимо переименовать до того, как будет выполнено естественное соединение. Такое соединение иногда также называют равное соединение.

Более формально семантика естественного соединения определяется следующим образом:

${\displaystyle R\bowtie S=\left\{t\cup s\mid t\in R\ \land \ s\in S\ \land \ {\mathit {Fun}}(t\cup s)\right\}}$,

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

Естественное соединение можно смоделировать с помощью примитивов Кодда следующим образом. Позволять c₁, …, c_м быть именами атрибутов, общими для р и S, р₁, …, р_п быть именами атрибутов, уникальными для р и разреши s₁, …, s_k быть атрибутами, уникальными для S. Кроме того, предположим, что имена атрибутов Икс₁, …, Икс_м ни в р ни в S. На первом этапе общие имена атрибутов в S теперь можно переименовать:

${\displaystyle T=\rho _{x_{1}/c_{1},\ldots ,x_{m}/c_{m}}(S)=\rho _{x_{1}/c_{1}}(\rho _{x_{2}/c_{2}}(\ldots \rho _{x_{m}/c_{m}}(S)\ldots ))}$

Затем мы берем декартово произведение и выбираем кортежи, которые нужно объединить:

${\displaystyle U=\pi _{r_{1},\ldots ,r_{n},c_{1},\ldots ,c_{m},s_{1},\ldots ,s_{k}}(P)}$

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

Большинство экспертов согласны с тем, что NATURAL JOIN опасны, и поэтому настоятельно не рекомендуют их использовать.^[7] Опасность возникает из-за непреднамеренного добавления нового столбца с таким же именем, как у другого столбца в другой таблице. Существующее естественное соединение может затем «естественным образом» использовать новый столбец для сравнений, делая сравнения / совпадения с использованием других критериев (из разных столбцов), чем раньше. Таким образом, существующий запрос может дать разные результаты, даже если данные в таблицах не были изменены, а только увеличены. Использование имен столбцов для автоматического определения ссылок на таблицы не является вариантом в больших базах данных с сотнями или тысячами таблиц, где это наложило бы нереалистичное ограничение на соглашения об именах. Базы данных реального мира обычно разрабатываются с иностранный ключ данные, которые заполняются непоследовательно (допускаются значения NULL) из-за бизнес-правил и контекста. Обычной практикой является изменение названий столбцов схожих данных в разных таблицах, и это отсутствие жесткой согласованности превращает естественные объединения в теоретическую концепцию для обсуждения.

Вышеупомянутый пример запроса для внутренних объединений может быть выражен как естественное соединение следующим образом:

ВЫБРАТЬ *ИЗ наемный рабочий ЕСТЕСТВЕННЫЙ ПРИСОЕДИНИТЬСЯ отделение;

Как и в случае явного С ПОМОЩЬЮ предложения, в объединенной таблице встречается только один столбец DepartmentID без квалификатора:

DepartmentID	Employee.LastName	Department.DepartmentName
34	Смит	Канцелярский
33	Джонс	Инженерное дело
34	Робинсон	Канцелярский
33	Гейзенберг	Инженерное дело
31	Рафферти	Продажи

PostgreSQL, MySQL и Oracle поддерживают естественные объединения; Microsoft T-SQL и IBM DB2 нет. Столбцы, используемые в объединении, являются неявными, поэтому код объединения не показывает, какие столбцы ожидаются, а изменение имен столбцов может изменить результаты. в SQL: 2011 стандартные, естественные соединения являются частью дополнительного пакета F401, «Расширенный соединительный стол».

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

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

В объединенной таблице сохраняется каждая строка, даже если другой соответствующей строки не существует. Внешние соединения подразделяются на левые внешние соединения, правые внешние соединения и полные внешние соединения, в зависимости от того, какие строки таблицы сохраняются: левая, правая или обе (в данном случае оставили и верно относятся к двум сторонам ПРИСОЕДИНИТЬСЯ ключевое слово). Нравиться внутренние соединения, можно дополнительно подразделить все типы внешних соединений на равнозначные соединения, естественные соединения, НА <predicate> (θ-присоединиться ), так далее.^[8]

В стандартном SQL не существует неявной нотации для внешних соединений.

Диаграмма Венна, представляющая оператор SQL Left Join между таблицами A и B.

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

Результат левое внешнее соединение (или просто оставил присоединиться) для таблиц A и B всегда содержит все строки «левой» таблицы (A), даже если условие соединения не находит ни одной совпадающей строки в «правой» таблице (B). Это означает, что если НА предложение соответствует 0 (нулю) строк в B (для данной строки в A), объединение все равно вернет строку в результате (для этой строки), но с NULL в каждом столбце из B. левое внешнее соединение возвращает все значения из внутреннего соединения плюс все значения в левой таблице, которые не соответствуют правой таблице, включая строки со значениями NULL (пустыми) в столбце ссылки.

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

Пример левого внешнего соединения ( ВНЕШНИЙ ключевое слово является необязательным), а дополнительная строка результатов (по сравнению с внутренним соединением) выделена курсивом:

ВЫБРАТЬ *ИЗ наемный рабочий ОСТАВИЛИ ВНЕШНИЙ ПРИСОЕДИНИТЬСЯ отделение НА наемный рабочий.DepartmentID = отделение.DepartmentID;

Employee.LastName	Employee.DepartmentID	Department.DepartmentName	Department.DepartmentID
Джонс	33	Инженерное дело	33
Рафферти	31	Продажи	31
Робинсон	34	Канцелярский	34
Смит	34	Канцелярский	34
Уильямс	НОЛЬ	НОЛЬ	НОЛЬ
Гейзенберг	33	Инженерное дело	33

Альтернативные синтаксисы

Oracle поддерживает устаревшие^[9] синтаксис:

ВЫБРАТЬ *ИЗ наемный рабочий, отделениеКУДА наемный рабочий.DepartmentID = отделение.DepartmentID(+)

Sybase поддерживает синтаксис (Microsoft SQL Server устарел этот синтаксис с версии 2000):

ВЫБРАТЬ*ИЗнаемный рабочий,отделениеКУДАнаемный рабочий.DepartmentID*=отделение.DepartmentID

IBM Informix поддерживает синтаксис:

ВЫБРАТЬ *ИЗ наемный рабочий, ВНЕШНИЙ отделениеКУДА наемный рабочий.DepartmentID = отделение.DepartmentID

Диаграмма Венна, представляющая SQL-оператор Right Join между таблицами A и B.

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

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

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

Ниже приведен пример правого внешнего соединения ( ВНЕШНИЙ ключевое слово является необязательным), а дополнительная строка результатов выделена курсивом:

ВЫБРАТЬ *ИЗ наемный рабочий ВЕРНО ВНЕШНИЙ ПРИСОЕДИНИТЬСЯ отделение  НА наемный рабочий.DepartmentID = отделение.DepartmentID;

Employee.LastName	Employee.DepartmentID	Department.DepartmentName	Department.DepartmentID
Смит	34	Канцелярский	34
Джонс	33	Инженерное дело	33
Робинсон	34	Канцелярский	34
Гейзенберг	33	Инженерное дело	33
Рафферти	31	Продажи	31
НОЛЬ	НОЛЬ	Маркетинг	35

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

Диаграмма Венна, представляющая оператор SQL Full Join между таблицами A и B.

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

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

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

Пример полного внешнего соединения ( ВНЕШНИЙ ключевое слово не обязательно):

ВЫБРАТЬ *ИЗ наемный рабочий ПОЛНЫЙ ВНЕШНИЙ ПРИСОЕДИНИТЬСЯ отделение  НА наемный рабочий.DepartmentID = отделение.DepartmentID;

Employee.LastName	Employee.DepartmentID	Department.DepartmentName	Department.DepartmentID
Смит	34	Канцелярский	34
Джонс	33	Инженерное дело	33
Робинсон	34	Канцелярский	34
Уильямс	НОЛЬ	НОЛЬ	НОЛЬ
Гейзенберг	33	Инженерное дело	33
Рафферти	31	Продажи	31
НОЛЬ	НОЛЬ	Маркетинг	35

Некоторые системы баз данных не поддерживают полную функциональность внешнего соединения напрямую, но они могут имитировать ее, используя внутреннее соединение и UNION ALL, выбирая «отдельные строки таблицы» из левой и правой таблиц соответственно. Тот же пример может выглядеть следующим образом:

ВЫБРАТЬ наемный рабочий.Фамилия, наемный рабочий.DepartmentID,       отделение.Название отдела, отделение.DepartmentIDИЗ наемный рабочийВНУТРЕННИЙ ПРИСОЕДИНИТЬСЯ отделение НА наемный рабочий.DepartmentID = отделение.DepartmentIDСОЮЗ ВСЕВЫБРАТЬ наемный рабочий.Фамилия, наемный рабочий.DepartmentID,       В ролях(НОЛЬ в качестве варчар(20)), В ролях(НОЛЬ в качестве целое число)ИЗ наемный рабочийКУДА НЕТ СУЩЕСТВУЮТ (    ВЫБРАТЬ * ИЗ отделение             КУДА наемный рабочий.DepartmentID = отделение.DepartmentID)СОЮЗ ВСЕВЫБРАТЬ В ролях(НОЛЬ в качестве варчар(20)), В ролях(НОЛЬ в качестве целое число),       отделение.Название отдела, отделение.DepartmentIDИЗ отделениеКУДА НЕТ СУЩЕСТВУЮТ (    ВЫБРАТЬ * ИЗ наемный рабочий             КУДА наемный рабочий.DepartmentID = отделение.DepartmentID)

Самостоятельное присоединение

Самосоединение - это присоединение таблицы к самой себе.^[10]

Пример

Если бы были две отдельные таблицы для сотрудников и запрос, который запрашивал сотрудников в первой таблице, имеющих ту же страну, что и сотрудники во второй таблице, для поиска таблицы ответов можно было бы использовать обычную операцию объединения. Однако вся информация о сотрудниках содержится в одной большой таблице.^[11]

Рассмотрим модифицированный Наемный рабочий таблица, такая как следующая:

Таблица сотрудников

EmployeeID	Фамилия	Страна	DepartmentID
123	Рафферти	Австралия	31
124	Джонс	Австралия	33
145	Гейзенберг	Австралия	33
201	Робинсон	Соединенные Штаты	34
305	Смит	Германия	34
306	Уильямс	Германия	НОЛЬ

Пример запроса решения может быть следующим:

ВЫБРАТЬ F.EmployeeID, F.Фамилия, S.EmployeeID, S.Фамилия, F.СтранаИЗ Наемный рабочий F ВНУТРЕННИЙ ПРИСОЕДИНИТЬСЯ Наемный рабочий S НА F.Страна = S.СтранаКУДА F.EmployeeID < S.EmployeeIDПОРЯДОК К F.EmployeeID, S.EmployeeID;

В результате создается следующая таблица.

Таблица сотрудников после самостоятельного присоединения по странам

EmployeeID	Фамилия	EmployeeID	Фамилия	Страна
123	Рафферти	124	Джонс	Австралия
123	Рафферти	145	Гейзенберг	Австралия
124	Джонс	145	Гейзенберг	Австралия
305	Смит	306	Уильямс	Германия

В этом примере:

• F и S находятся псевдонимы для первой и второй копии таблицы сотрудников.
• Условие F.Country = Южная страна исключает пары между сотрудниками в разных странах. В примере вопроса нужны только пары сотрудников из одной страны.
• Условие F.EmployeeID исключает пары, в которых EmployeeID первого сотрудника больше или равно EmployeeID второго сотрудника. Другими словами, эффект этого условия состоит в том, чтобы исключить повторяющиеся пары и самопары. Без него была бы сгенерирована следующая менее полезная таблица (в таблице ниже отображается только часть результата "Германия"):

EmployeeID	Фамилия	EmployeeID	Фамилия	Страна
305	Смит	305	Смит	Германия
305	Смит	306	Уильямс	Германия
306	Уильямс	305	Смит	Германия
306	Уильямс	306	Уильямс	Германия

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

Альтернативы

Эффект внешнего соединения также может быть получен с помощью UNION ALL между INNER JOIN и SELECT строк в «основной» таблице, которые не удовлетворяют условию соединения. Например,

ВЫБРАТЬ наемный рабочий.Фамилия, наемный рабочий.DepartmentID, отделение.Название отделаИЗ наемный рабочийОСТАВИЛИ ВНЕШНИЙ ПРИСОЕДИНИТЬСЯ отделение НА наемный рабочий.DepartmentID = отделение.DepartmentID;

также можно записать как

ВЫБРАТЬ наемный рабочий.Фамилия, наемный рабочий.DepartmentID, отделение.Название отделаИЗ наемный рабочийВНУТРЕННИЙ ПРИСОЕДИНИТЬСЯ отделение НА наемный рабочий.DepartmentID = отделение.DepartmentIDСОЮЗ ВСЕВЫБРАТЬ наемный рабочий.Фамилия, наемный рабочий.DepartmentID, В ролях(НОЛЬ в качестве варчар(20))ИЗ наемный рабочийКУДА НЕТ СУЩЕСТВУЮТ (    ВЫБРАТЬ * ИЗ отделение             КУДА наемный рабочий.DepartmentID = отделение.DepartmentID)

Выполнение

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

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

Многие алгоритмы соединения по-разному обрабатывают свои входные данные. Можно ссылаться на входные данные для соединения как на «внешний» и «внутренний» операнды соединения или «левый» и «правый» соответственно. В случае вложенных циклов, например, система базы данных будет сканировать все внутреннее отношение для каждой строки внешнего отношения.

Планы запросов, включающие объединения, можно классифицировать следующим образом:^[12]

левый

использование базовой таблицы (а не другого соединения) в качестве внутреннего операнда каждого соединения в плане

глубоко

использование базовой таблицы в качестве внешнего операнда каждого соединения в плане

кустистый

ни влево, ни вправо; оба входа в соединение сами могут быть результатом объединений

Эти названия происходят от внешнего вида план запроса если нарисовано как дерево, с отношением внешнего соединения слева и внутренним отношением справа (в соответствии с соглашением).

Алгоритмы соединения

Существуют три основных алгоритма для выполнения операции соединения: соединение вложенного цикла, сортировка-слияние и хеш-соединение.

Присоединить индексы

Индексы соединения индексы базы данных которые облегчают обработку запросов на соединение в хранилища данных: в настоящее время (2012 г.) они доступны в реализациях Oracle^[13] и Терадата.^[14]

В реализации Teradata указанные столбцы, агрегатные функции для столбцов или компоненты столбцов даты из одной или нескольких таблиц указываются с использованием синтаксиса, аналогичного определению просмотр базы данных: до 64 столбцов / выражений столбцов можно указать в одном индексе соединения. Необязательно столбец, определяющий первичный ключ составных данных также можно указать: на параллельном оборудовании значения столбцов используются для разделения содержимого индекса по нескольким дискам. Когда исходные таблицы обновляются пользователями в интерактивном режиме, содержимое индекса объединения обновляется автоматически. Любой запрос, чей Предложение WHERE определяет любую комбинацию столбцов или выражений столбцов, которые являются точным подмножеством тех, которые определены в индексе соединения (так называемый «покрывающий запрос»), вызовет обращение к индексу соединения, а не к исходным таблицам и их индексам во время запроса исполнение.

Реализация Oracle ограничивается использованием индексы растровых изображений. А индекс соединения растрового изображения используется для столбцов с низкой мощностью (т.е. столбцов, содержащих менее 300 различных значений, согласно документации Oracle): он объединяет столбцы с низкой мощностью из нескольких связанных таблиц. Oracle использует систему инвентаризации, в которой разные поставщики предоставляют разные детали. В схема имеет три связанных таблицы: две «основные таблицы», «Деталь и поставщик», и «подробную таблицу», «Инвентарь». Последняя представляет собой таблицу "многие ко многим", связывающую поставщика с деталью и содержащую наибольшее количество строк. Каждая деталь имеет тип детали, каждый поставщик находится в США и имеет столбец "Штат". В США не более 60 штатов + территорий и не более 300 типов деталей. Индекс соединения растрового изображения определяется с использованием стандартного соединения трех таблиц для трех приведенных выше таблиц и указания столбцов Part_Type и Supplier_State для индекса. Однако он определен в таблице Inventory, даже если столбцы Part_Type и Supplier_State «заимствованы» у поставщика и детали соответственно.

Что касается Teradata, индекс соединения растровых изображений Oracle используется только для ответа на запрос, когда запрос Предложение WHERE определяет столбцы, ограниченные теми, которые включены в индекс соединения.

Прямое соединение

Некоторые системы баз данных позволяют пользователю заставить систему читать таблицы в соединении в определенном порядке. Это используется, когда оптимизатор соединения выбирает чтение таблиц в неэффективном порядке. Например, в MySQL команда STRAIGHT_JOIN читает таблицы в точном порядке, указанном в запросе.^[15]

Смотрите также

• Присоединиться (реляционная алгебра)
• Antijoin
• Установить операции (SQL)

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

Цитаты

1. SQL CROSS JOIN
2. Грег Робиду, «Избегайте функций SQL Server в предложении WHERE для повышения производительности», MSSQL Tips, 3 мая 2007 г.
3. Патрик Вольф, "Внутри Oracle APEX". Предостережения при использовании функций PL / SQL в операторе SQL ", 30 ноября 2006 г.
4. Грегори А. Ларсен, «Лучшие практики T-SQL - не используйте функции скалярных значений в списке столбцов или предложениях WHERE», 29 октября 2009 г.,
5. Упрощение объединений с помощью ключевого слова USING
6. В Unicode, символ бабочки - ⋈ (U + 22C8).
7. Спросите Тома: «Поддержка Oracle соединений ANSI». Назад к основам: внутреннее объединение »Блог Эдди Авада В архиве 2010-11-19 в Wayback Machine
8. Зильбершац, Авраам; Корт, Хэнк; Сударшан, С. (2002). «Раздел 4.10.2: Типы и условия соединения». Концепции системы баз данных (4-е изд.). п. 166. ISBN  0072283637.
9. Левое внешнее соединение Oracle
10. Шах 2005, п. 165
11. Адаптирован из Пратт 2005, стр. 115–6
12. Ю и Мэн 1998, п. 213
13. Индекс объединения растровых изображений Oracle. URL: http://www.dba-oracle.com/art_builder_bitmap_join_idx.htm
14. Индексы соединения Teradata. «Архивная копия». Архивировано из оригинал на 2012-12-16. Получено 2012-06-14.
15. «13.2.9.2 Синтаксис JOIN». Справочное руководство по MySQL 5.7. Корпорация Oracle. Получено 2015-12-03.

Источники

• Пратт, Филлип Дж (2005), Руководство по SQL, седьмое издание, Технологии курсов Томсона, ISBN  978-0-619-21674-0
• Шах, Нилеш (2005) [2002], Системы баз данных с использованием Oracle - упрощенное руководство по SQL и PL / SQL, второе издание (Международный редактор), Pearson Education International, ISBN  0-13-191180-5
• Ю., Клемент Т .; Мэн, Вэйи (1998), Принципы обработки запросов к базе данных для сложных приложений, Морган Кауфманн, ISBN  978-1-55860-434-6, получено 2009-03-03

внешняя ссылка

• Специально для продуктов
  • Sybase ASE 15 присоединяется
  • MySQL 5.7 присоединяется
  • PostgreSQL 9.3 присоединяется
  • Присоединяется в Microsoft SQL Server
  • Присоединяется в MaxDB 7.6
  • Присоединяется к Oracle 12c R1
  • Oracle SQL объединяется