Соединение (SQL)

Предложение SQL join - соответствует операции соединения в реляционной алгебре - объединяет столбцы из одной или нескольких таблиц в реляционной базе данных. Он создает набор, который можно сохранить как таблицу или использовать как есть. СОЕДИНЕНИЕ- это средство для объединения столбцов из одной (самосоединение) или нескольких таблиц с использованием общих для каждой. ANSI -стандартный SQL определяет пять типов JOIN: INNER, LEFT OUTER, RIGHT OUTER, ПОЛНЫЙ ВНЕШНИЙи КРЕСТ. В качестве особого случая таблицы (базовая таблица, представление или объединенная таблица) может JOINсама с собой в самосоединении.

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

• 1 Примеры таблиц
• 2 Перекрестное соединение
• 3 Внутреннее соединение
  • 3.1 Внутреннее соединение и значения NULL
  • 3.2 Экви-соединение
    • 3.2.1 Естественное соединение
• 4 Внешнее соединение
  • 4.1 Левое внешнее соединение
    • 4.1.1 Альтернативные синтаксисы
  • 4.2 Правое внешнее соединение
  • 4.3 Полное внешнее соединение
• 5 Самосоединение
  • 5.1 Пример
• 6 Альтернативы
• 7 Реализация
  • 7.1 Алгоритмы соединения
  • 7.2 Индексы соединения
  • 7.3 Прямое соединение
• 8 См.
• 9 Ссылки
  • 9.1 Цитаты
  • 9.2 Источники
• 10 Лучшие ссылки

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

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

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

LastName	DepartmentID
Rafferty	31
Джонс	33
Гейзенберг	33
Робинсон	34
Смит	34
Уильямс	NULL

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

DepartmentID	DepartmentName
31	Sales
33	Engineering
34	Clerical
35	Marketing

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

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

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

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

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

ВЫБРАТЬ * ОТ сотрудника CROSS JOIN Department;

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

ВЫБРАТЬ * ОТ сотрудника, отдела;

Employee.LastName	Employee.DepartmentID	Department.DepartmentName	Department.DepartmentID
Rafferty	31	Sales	31
Джонс	33	Продажи	31
Гейзенберг	33	Продажи	31
Смит	34	Продажи	31
Робинсон	34	Продажи	31
Уильямс	NULL	Продажи	31
Рафферти	31	Инжиниринг	33
Джонс	33	Инженерное дело	33
Гейзенберг	33	Инжиниринг	33
Смит	34	Инжиниринг	33
Робинсон	34	Инженерное дело	33
Уильямс	NULL	Инжиниринг	33
Rafferty	31	Clerical	34
Jones	33	Clerical	34
Гейзенберг	33	Клерикал	34
Смит	34	Клерикал	34
Робинсон	34	Клерикал	34
Уильямс	NULL	Служба	34
Рафферти	31	Маркетинг	35
Джонс	33	Маркетинг	35
Гейзенберг	33	Маркетинг	35
Смит	34	Маркетинг	35
Робинс он	34	Маркетинг	35
Williams	NULL	Marketing	35

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

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

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

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

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

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

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

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

ВЫБЕРИТЕ employee.LastName, employee.DepartmentID, Department.DepartmentName FROM employee ВНУТРЕННЕЕ СОЕДИНЯТЬ отдел НА employee.DepartmentID = Department.DepartmentID;

Employee.LastName	Employee.DepartmentID	Department.DepartmentName
Robinson	34	Clerical
Jones	33	Engineering
Smith	34	Clerical
Гейзенберг	33	Инженерное дело
Рафферти	31	Продажи

«Нотация неявного соединения» просто перечисляет таблицы для объединения в предложении ИЗинструкции SELECT, разделяя их запятыми. Таким образом, он определяет перекрестное соединение, а предложение WHEREможет применять дополнительные предикаты фильтра (которые аналогичны предикатам соединения в явной нотации).

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

SELECT employee.LastName, employee.DepartmentID, Department.DepartmentName FROM employee, Department WHERE employee.DepartmentID = department. DepartmentID;

Запросы, приведенные в приведенных выше примерах, будут соединяться с таблицами «Сотрудник» и «Отдел» с использованием столбца «Идентификатор отдела» разных таблиц. Если DepartmentID этих таблиц совпадает, запрос объединит столбцы LastName, DepartmentID и DepartmentName из двух таблиц в строку результатов. Если DepartmentID не совпадает, строка создается не создается.

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

Employee.LastName	Employee.DepartmentID	Department.DepartmentName
Робинсон	34	Конторский служащий
Джонс	33	Инженерно-технический отдел
Смит	34	Конторский служащий
Гейзенберг	33	Инженерно-технический отдел
Рафферти	31	Отдел продаж

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

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

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

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

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

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

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

Equi-join

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

ВЫБРАТЬ * ОТ сотрудника СОЕДИНЯТЬ отдел НА сотруднике.DepartmentID = Department.DepartmentID;

Мы можем написать эквивалентное соединение, как показано ниже:

ВЫБРАТЬ * ОТ сотрудника, отдела ГДЕ сотрудник.DepartmentID = Department.DepartmentID;

Если столбцы в равном объединении имеют одинаковое имя, SQL-92 предоставляет необязательную сокращенную нотацию для выражения равных объединений посредством конструкции USING:

ВЫБРАТЬ * ОТ сотрудника ВНУТРЕННЕЕ СОЕДИНЕНИЕ отдела ИСПОЛЬЗОВАНИЕ (DepartmentID);

Конструкция ИСПОЛЬЗОВАНИЕ- это больше, чем просто синтаксический сахар, однако, поскольку набор результатов отличается от набора версии с явным предикатом. В частности, любые столбцы, указанные в списке ИСПОЛЬЗОВАНИЕ, будут только один раз с неполным именем, а не один раз для каждой таблицы в объединении. В приведенном выше случае будет один столбец DepartmentIDи не будет employee.DepartmentIDили Department.DepartmentID.

Предложение USINGне поддерживается MS SQL Server и Sybase.

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

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

Сотрудник Имя EmpId DeptName Гарри 3415 Финансы Салли 2241 Продажи Джордж 3401 Финансы Harriet 2202 Продажи

Отдел DeptName Менеджер Финансы Джордж Продажи Харриет Производство Чарльз

Сотрудник $\bowtie \; \{\backslash \; displaystyle\; \backslash \; bowtie\}$Dept Name EmpId DeptName Менеджер Гарри 3415 Финансы Джордж Салли 2241 Продажи Харриет Джордж 3401 Финансы Джордж Гарриет 2202 Продажи Харриет

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

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

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

$R\; \bowtie \; S\; =\; \{t\; \cup \; s\; \mid \; t\; \in \; R\; \wedge \; s\; \in \; S\; \wedge \; F\; un\; (t\; \cup \; s)\}\; \{\; \backslash \; Displaystyle\; R\; \backslash \; Bowtie\; S\; =\; \backslash \; left\; \backslash \; \{t\; \backslash \; cup\; s\; \backslash \; mid\; t\; \backslash \; in\; R\; \backslash \; \backslash \; land\; \backslash \; s\; \backslash \; in\; S\; \backslash \; \backslash \; land\; \backslash \; \{\backslash \; mathit\; \{Fun\}\}\; (t\; \backslash \; cup\; s)\; \backslash \; right\; \backslash \}\; \}$,

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

Естественное соединение можно смоделировать с помощью примитивов Кодда следующим образом. Пусть c 1,…, c m будут именами атрибутов, общими для R и S, r 1,…, r n быть имена атрибутов, уникальные для R, и пусть s 1,…, s k будут атрибутами, уникальными для S. Кроме предположим, что имена атрибутов x 1,…, X m не входят ни в R, ни в S. На первом этапе общих атрибутов атрибутов в S теперь можно переименовать:

$T\; =\; \rho \; x\; 1\; /\; c\; 1,\dots ,\; xm\; /\; см\; (S)\; знак\; равно\; \rho \; Икс\; 1\; /\; с\; 1\; (\rho \; Икс\; 2\; /\; с\; 2\; (\dots \; \rho \; хм\; /\; см\; (S)\dots ))\; \{\backslash \; Displaystyle\; T\; =\; \backslash \; rho\; \_\; \{x\_\; \{1\}\; /\; c\_\; \{1\},\; \backslash \; ldots,\; x\_\; \{m\}\; /\; c\_\; \{m\}\}\; (S)\; =\; \backslash \; rho\; \_\; \{x\_\; \{1\}\; /\; c\_\; \{1\}\}\; (\backslash \; rho\; \_\; \{x\_\; \{2\}\; /\; c\_\; \{2\}\}\; (\backslash \; ldots\; \backslash \; rho\; \_\; \{x\_\; \{m\}\; /\; c\_\; \{m\}\}\; (S)\; \backslash \; ldots))\}$

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

$U\; =\; \pi \; r\; 1,\dots ,\; rn,\; c\; 1,\dots ,\; см,\; s\; 1,\dots ,\; sk\; (P)\; \{\backslash \; displaystyle\; U\; =\; \backslash \; pi\; \_\; \{r\_\; \{1\},\; \backslash \; ldots,\; r\_\; \{n\},\; c\_\; \{1\},\; \backslash \; ldots,\; c\_\; \{m\; \},\; s\_\; \{1\},\; \backslash \; ldots,\; s\_\; \{k\}\}\; (P)\}$

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

. Большинство экспертов согласны с тем, что ЕСТЕСТВЕННЫЕ СОЕДИНЕНИЯ опасны, и поэтому настоятельно не рекомендуют их использование. Опасность возникает из-за непреднамеренного добавления нового столбца с таким же именем, как у другого столбца в другой таблице. Существующее естественное соединение может затем «естественно» использовать новый столбец для сравнений, делая сравнения / совпадения с использованием других критериев (из разных столбцов), чем раньше. Таким образом, существующий запрос может дать разные результаты, даже если данные в таблицах не были изменены, а только увеличены. Использование имен столбцов для автоматического определения ссылок на таблицы не является вариантом в больших базах данных с сотнями или тысячами таблиц, где это наложило бы нереалистичное ограничение на соглашения об именах. Реальные базы данных обычно разрабатываются скоторые используют данные внешнего ключа, не заполняются согласованно (разрешены значения NULL) из-за бизнес-правил и контекста. Обычной практикой является изменение названий столбцов схожих данных в разных таблицах, и это отсутствие жесткой согласования превращает естественное объединение в теоретическую концепцию для обсуждения.

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

SELECT * FROM Сотрудник отдела NATURAL JOIN;

Как и в случае явного предложения USING, в объединенной таблице присутствует только один столбец DepartmentID без квалификатора:

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

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

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

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

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

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

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

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

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

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

SELECT * ОТ сотрудника ВЛЕВО ВНЕШНИЙ ПРИСОЕДИНИТЬСЯ к сотруднику.DepartmentID = Department.DepartmentID;

Имя сотрудника	Сотрудник.Ид отдела	Дотдел.Название отдела	Подразделение.ИД
Джонс	33	Инжиниринг	33
Рафферти	31	отдел продаж	31
Робинсон	34	служащий	34
Смит	34	служащий	34
Уильямс	NULL	NULL	NULL
Гейзенберг	33	Инженерное дело	33

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

Oracle поддерживает устаревший синтаксис:

SELECT * FROM сотрудник, отдел WHERE employee.DepartmentID = Department.DepartmentID (+)

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

SELECT * FROM employee, Department WHERE employee.DepartmentID * = Department.DepartmentID

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

SELECT * FROM employee, ВНЕШНИЙ отдел WHERE employee.DepartmentID = Department.DepartmentID

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

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

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

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

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

SELECT * FROM employee RIGHT ВНЕШНЕЕ СОЕДИНЕНИЕ с отделом НА employee.DepartmentID = Department.DepartmentID;

Employee.LastName	Employee.DepartmentID	Department.DepartmentName	Department.DepartmentID
Smith	34	Clerical	34
Джонс	33	Инженерное дело	33
Робинсон	34	Клерикал	34
Гейзенберг	33	Инжиниринг	33
Рафферти	31	Продажи	31
NULL	NULL	Маркетинг	35

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

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

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

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

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

Пример полного внешнего соединения (слово OUTERявляется необязательным):

SELECT * FROM сотрудника ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ отдел НА сотруднике.DepartmentID = Department. DepartmentID;

Employee.LastName	Employee.DepartmentID	Department.DepartmentName	Department.DepartmentID
Smith	34	Clerical	34
Джонс	33	Инженерное дело	33
Робинсон	34	Служба	34
Уильямс	NULL	NULL	NULL
Гейзенберг	33	Инженерное дело	33
Рафферти	31	Продажи	31
ПУСТО	ПУСТО	Маркетинг	35

Некоторые системы Они могут имитировать его с помощью внутреннего и выбора UNION ВСЕ «строки одной таблицы» из и левой таблицы соответственно. Тот же пример может выглядеть следующим образом:

ВЫБРАТЬ employee.LastName, employee.DepartmentID, Department.DepartmentName, Department.DepartmentID ОТ сотрудника ВНУТРЕННИЙ СОЕДИНЯЙСЯ с отделом ON employee.DepartmentID = Department.DepartmentID UNION ALL ВЫБРАТЬ Сотрудник.Фамилия, сотрудник DepartmentID, cast (NULL как varchar (20)), cast (NULL как целое число) FROM employee WHERE NOT EXISTS (SELECT * FROM Department WHERE employee.DepartmentID = Department.DepartmentID) UNION ALL SELECT cast (NULL as varchar (20)), cast (NULL как целое число), Department.DepartmentName, Department.DepartmentID ИЗ отдела, ГДЕ НЕ СУЩЕСТВУЕТ (ВЫБРАТЬ * ОТ сотрудника, ГДЕ сотрудник.DepartmentID = Department.DepartmentID)

Присоединяется самосоединение таблица сама себе.

Пример

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

Рассмотрим измененную таблицу Employee, например следующую:

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

EmployeeID	Фамилия	Страна	DepartmentID
123	Rafferty	Австралия	31
124	Джонс	Австралия	33
145	Гейзенберг	Австралия	33
201	Робинсон	США	34
305	Смит	Германия	34
306	Williams	Германия	NULL

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

SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country FROM Сотрудник F INNER JOIN Сотрудник S ON F.Country = S.Country WHERE F.EmployeeID < S.EmployeeID ORDER BY F.EmployeeID, S.EmployeeID;

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

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

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

Для этого примера:

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

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

Для удовлетворения исходному вопросу, а самые верхние и самые нижние в этом примере не Обратите вниманиеса.

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

ВЫБРАТЬ employee.LastName, employee.DepartmentID, Department.DepartmentName FROM employee LEFT OUTER JOIN Department ON employee.DepartmentID = Department.DepartmentID;

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

SELECT employee.LastName, employee.DepartmentID, Department.DepartmentName FROM employee INNER JOIN Department ON employee.DepartmentID = Department.DepartmentID UNION ALL SELECT employee.LastName, employee.DepartmentID, cast (NULL as varchar (20)) ОТ сотрудника, ГДЕ НЕ СУЩЕСТВУЕТ (ВЫБРАТЬ * ОТ Департамента, ГДЕ сотрудник.DepartmentID = Department.DepartmentID)

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

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

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

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

глубоко слева

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

глубоко вправо

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

кустисто

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

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

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

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

Индексы соединения

Индексы соединения - это индексы базы данных, которые облегчают обработку запросов соединения в хранилищах данных : в настоящее время (2012 г.) они доступны в реализации Oracle и Teradata.

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

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

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

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

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

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

Цитаты

Источники

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