Из Википедии, бесплатной энциклопедии
Перейти к навигации Перейти к поиску

Предложение соединения SQL - соответствующее операции соединения в реляционной алгебре - объединяет столбцы из одной или нескольких таблиц в реляционной базе данных . Он создает набор, который можно сохранить как таблицу или использовать как есть. A - это средство для объединения столбцов из одной (самосоединение) или нескольких таблиц с использованием значений, общих для каждой из них. ANSI -эталонных пять SQL для задают типов : , , , и . В особом случае таблица (базовая таблица, представление или объединенная таблица) может сама с собой соединяться .JOINJOININNERLEFT OUTERRIGHT OUTERFULL OUTERCROSSJOIN

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

Образцы таблиц [ править ]

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

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

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

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

СОЗДАТЬ  ТАБЛИЦУ  отдел ( DepartmentID  INT  ПЕРВИЧНЫЙ  КЛЮЧ  НЕ  NULL ,  Название отдела VARCHAR ( 20 ));СОЗДАТЬ  ТАБЛИЦУ  сотрудник  ( Фамилия  VARCHAR ( 20 ), DepartmentID  INT  ССЫЛКИ  Департамент ( DepartmentID ));INSERT  INTO  отделЦЕННОСТИ  ( 31 ,  «Продажи» ), ( 33 ,  «Инжиниринг» ), ( 34 ,  'Канцелярский' ), ( 35 ,  «Маркетинг» );INSERT  INTO  сотрудникЦЕННОСТИ  ( 'Рафферти' ,  31 ), ( 'Джонс' ,  33 ), ( 'Гейзенберг' ,  33 ), ( 'Робинзон' ,  34 ), ( 'Смит' ,  34 ), ( 'Вильямс' ,  NULL );

Перекрестное соединение [ править ]

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

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

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

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

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

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

ВЫБРАТЬ  * ОТ  сотрудника  ВНУТРЕННЕЕ  СОЕДИНЕНИЕ  отдела  ВКЛ  1 = 1 ;

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

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

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

Внутреннее соединение [ править ]

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

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

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

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

ВЫБЕРИТЕ  сотрудника . Фамилия ,  сотрудник . DepartmentID ,  отдел . Имя отдела  ОТ  сотрудника  ВНУТРЕННЕЕ  СОЕДИНЕНИЕ  департамента  НА сотруднике . DepartmentID  =  отдел . DepartmentID ;

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

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

ВЫБЕРИТЕ  сотрудника . Фамилия ,  сотрудник . DepartmentID ,  отдел . Название отдела  ОТ  сотрудника ,  отдел ГДЕ  сотрудник . DepartmentID  =  отдел . DepartmentID ;

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

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

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

Внутреннее соединение и значения NULL [ править ]

Программисты должны проявлять особую осторожность при объединении таблиц в столбцы, которые могут содержать значения NULL , поскольку NULL никогда не будет соответствовать никакому другому значению (даже самому NULL), если только условие соединения явно не использует предикат комбинации, который сначала проверяет, что столбцы объединения NOT 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 [ править ]

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

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

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

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

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

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

Однако эта USINGконструкция - больше, чем просто синтаксический сахар , поскольку набор результатов отличается от набора результатов версии с явным предикатом. В частности, любые столбцы, упомянутые в USINGсписке, будут отображаться только один раз с неполным именем, а не один раз для каждой таблицы в объединении. В приведенном выше случае будет один DepartmentIDстолбец и нет employee.DepartmentIDили department.DepartmentID.

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

Естественное соединение [ править ]

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

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

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

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

,

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

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

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

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

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

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

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

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

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

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

Внешнее соединение [ править ]

В объединенной таблице сохраняется каждая строка, даже если другой соответствующей строки не существует. Внешние соединения подразделяются на левые внешние соединения, правые внешние соединения и полные внешние соединения, в зависимости от того, какие строки таблицы сохраняются: левая, правая или обе (в данном случае левая и правая относятся к двум сторонам JOINключевого слова). Подобно внутренним соединениям , можно дополнительно подразделить все типы внешних соединений на равные соединения , естественные соединения , ( θ -соединения ) и т. Д. [8]ON <predicate>

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

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

Левое внешнее соединение [ править ]

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

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

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

ВЫБЕРИТЕ  * ОТ  сотрудник  ЛЕВОГО  OUTER  JOIN  отдела  ПО  сотруднику . DepartmentID  =  отдел . DepartmentID ;

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

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 в случае отсутствия соответствующего предиката соединения). Например, это позволяет нам найти каждого сотрудника и его или ее отдел, но все же показать отделы, в которых нет сотрудников.

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

ВЫБЕРИТЕ  * ОТ  сотрудника  RIGHT  OUTER  JOIN  отдел  ПО  работника . DepartmentID  =  отдел . DepartmentID ;

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

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

Полное внешнее соединение [ править ]

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

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

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

ВЫБЕРИТЕ  * ОТ  сотрудника  ПОЛНОГО  OUTER  JOIN  отдела  ПО  сотруднику . DepartmentID  =  отдел . DepartmentID ;

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

ВЫБЕРИТЕ  сотрудника . Фамилия ,  сотрудник . DepartmentID ,  отдел . Название отдела ,  отдел . DepartmentID ОТ  сотрудника ВНУТРЕННЕЕ  СОЕДИНЕНИЕ  департамента  НА  сотрудника . DepartmentID  =  отдел . DepartmentIDСОЮЗ  ВСЕВЫБЕРИТЕ  сотрудника . Фамилия ,  сотрудник . DepartmentID ,  литая ( NULL ,  как  VARCHAR ( 20 )),  литой ( NULL  как  целое ) FROM  сотрудника WHERE  NOT  EXISTS  (  ВЫБРАТЬ  *  FROM  отдела  WHERE  сотрудника . DepartmentID  =  отдел . DepartmentID )СОЮЗ  ВСЕВЫБРАТЬ  приведение ( NULL  как  varchar ( 20 )),  приведение ( NULL  как  целое число ),  отдел . Название отдела ,  отдел . DepartmentID ИЗ  отдела, ГДЕ  НЕ  СУЩЕСТВУЕТ  (  ВЫБЕРИТЕ  *  ОТ  сотрудника,  ГДЕ  сотрудник . DepartmentID  =  департамент . DepartmentID )

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

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

Пример [ править ]

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

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

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

ВЫБРАТЬ  F . EmployeeID ,  F . LastName ,  S . EmployeeID ,  S . LastName ,  F . Страна FROM  Employee  F  INNER  JOIN  Employee  S  ON  F . Страна  =  S . Страна WHERE  F . EmployeeID  <  S . EmployeeID ORDER  BY  F . EmployeeID ,  S . EmployeeID;

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

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

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

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

Альтернативы [ править ]

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

ВЫБЕРИТЕ  сотрудника . Фамилия ,  сотрудник . DepartmentID ,  отдел . DepartmentName ОТ  сотрудника ВЛЕВО  ВНЕШНИЙ  СОЕДИНЕНИЕ  департамента  НА  сотруднике . DepartmentID  =  отдел . DepartmentID ;

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

ВЫБЕРИТЕ  сотрудника . Фамилия ,  сотрудник . DepartmentID ,  отдел . Имя отдела ОТ  сотрудника ВНУТРЕННЕЕ  СОЕДИНЕНИЕ  департамента  НА  сотруднике . DepartmentID  =  отдел . DepartmentIDСОЮЗ  ВСЕВЫБЕРИТЕ  сотрудника . Фамилия ,  сотрудник . DepartmentID ,  литая ( NULL ,  как  VARCHAR ( 20 )) ОТ  сотрудника ГДЕ  НЕ  СУЩЕСТВУЕТ  (  ВЫБЕРИТЕ  *  FROM  отдела  WHERE  сотрудника . DepartmentID  =  отдел . DepartmentID )

Реализация [ править ]

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

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

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

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

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

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

Алгоритмы присоединения [ править ]

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

Присоединиться к индексам [ править ]

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

В реализации Teradata указанные столбцы, агрегатные функции для столбцов или компоненты столбцов даты из одной или нескольких таблиц задаются с использованием синтаксиса, аналогичного определению представления базы данных : до 64 столбцов / выражений столбцов можно указать в одном присоединиться к index. При желании также может быть указан столбец, определяющий первичный ключ составных данных: на параллельном оборудовании значения столбца используются для разделения содержимого индекса по нескольким дискам. Когда исходные таблицы обновляются пользователями в интерактивном режиме, содержимое индекса объединения обновляется автоматически. Любой запрос, предложение 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 ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ
  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. ^ В Юникоде символ бабочки - ⋈ (U + 22C8).
  7. ^ Спросите Тома: «Поддержка соединений ANSI в Oracle». Назад к основам: внутренние соединения »Блог Эдди Авада, заархивированный 19 ноября 2010 г. в Wayback Machine
  8. ^ Зильбершатц, Авраам ; Корт, Хэнк; Сударшан, С. (2002). «Раздел 4.10.2: Типы и условия соединения». Концепции системы баз данных (4-е изд.). п. 166. ISBN. 0072283637.
  9. ^ Oracle Левое внешнее соединение
  10. ^ Шах 2005 , стр. 165
  11. ^ Адаптировано из Pratt 2005 , стр. 115-6
  12. Перейти ↑ Yu & Meng 1998 , p. 213
  13. ^ Индекс объединения растровых изображений Oracle. URL: http://www.dba-oracle.com/art_builder_bitmap_join_idx.htm
  14. ^ Индексы соединения Teradata. «Архивная копия» . Архивировано из оригинала на 2012-12-16 . Проверено 14 июня 2012 .CS1 maint: archived copy as title (link)
  15. ^ «13.2.9.2 Синтаксис JOIN» . Справочное руководство по MySQL 5.7 . Корпорация Oracle . Проверено 3 декабря 2015 .

Источники [ править ]

  • Пратт, Филип Дж. (2005), Руководство по SQL, седьмое издание , Thomson Course Technology, 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, дата обращения 03.03.2009

Внешние ссылки [ править ]

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