Предложение соединения в SQL - соответствующее операции соединения в реляционной алгебре - объединяет столбцы из одной или нескольких таблиц в новую таблицу. ANSI -эталонных пять SQL для задают типов JOIN
: INNER
, LEFT OUTER
, RIGHT OUTER
, FULL OUTER
и CROSS
.
Примеры таблиц
Для объяснения типов соединений в остальной части этой статьи используются следующие таблицы:
Фамилия | DepartmentID |
---|---|
Рафферти | 31 год |
Джонс | 33 |
Гейзенберг | 33 |
Робинсон | 34 |
Смит | 34 |
Уильямс | NULL |
DepartmentID | Название отдела |
---|---|
31 год | Продажи |
33 | Инженерное дело |
34 | Канцелярский |
35 год | Маркетинг |
Department.DepartmentID
является первичным ключом из Department
таблицы, в то время как Employee.DepartmentID
это внешний ключ .
Отметим, что в Employee
«Вильямс» еще не закреплен за отделом. Также в отдел «Маркетинг» не закреплены сотрудники.
Это оператор 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]
Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
---|---|---|---|
Рафферти | 31 год | Продажи | 31 год |
Джонс | 33 | Продажи | 31 год |
Гейзенберг | 33 | Продажи | 31 год |
Смит | 34 | Продажи | 31 год |
Робинсон | 34 | Продажи | 31 год |
Уильямс | NULL | Продажи | 31 год |
Рафферти | 31 год | Инженерное дело | 33 |
Джонс | 33 | Инженерное дело | 33 |
Гейзенберг | 33 | Инженерное дело | 33 |
Смит | 34 | Инженерное дело | 33 |
Робинсон | 34 | Инженерное дело | 33 |
Уильямс | NULL | Инженерное дело | 33 |
Рафферти | 31 год | Канцелярский | 34 |
Джонс | 33 | Канцелярский | 34 |
Гейзенберг | 33 | Канцелярский | 34 |
Смит | 34 | Канцелярский | 34 |
Робинсон | 34 | Канцелярский | 34 |
Уильямс | NULL | Канцелярский | 34 |
Рафферти | 31 год | Маркетинг | 35 год |
Джонс | 33 | Маркетинг | 35 год |
Гейзенберг | 33 | Маркетинг | 35 год |
Смит | 34 | Маркетинг | 35 год |
Робинсон | 34 | Маркетинг | 35 год |
Уильямс | NULL | Маркетинг | 35 год |
Пример явного перекрестного соединения:
ВЫБРАТЬ * ОТ сотрудника CROSS JOIN отдела ;
Пример неявного перекрестного соединения:
ВЫБРАТЬ * ОТ сотрудника , отдела ;
Перекрестное соединение можно заменить внутренним соединением с условием Always-true:
ВЫБРАТЬ * ОТ сотрудника ВНУТРЕННЕЕ СОЕДИНЕНИЕ отдела ВКЛ 1 = 1 ;
CROSS JOIN
сам по себе не применяет никаких предикатов для фильтрации строк из объединенной таблицы. Результаты CROSS JOIN
можно отфильтровать с помощью WHERE
предложения, которое затем может создать эквивалент внутреннего соединения.
В стандарте SQL: 2011 перекрестные соединения являются частью необязательного пакета F401 «Расширенная объединенная таблица».
Обычное использование предназначено для проверки производительности сервера. [ почему? ]
Внутреннее соединение
Внутреннее соединение требует , чтобы каждая строки в двух соединяемых таблицах , чтобы иметь соответствующие значения столбцов, и обычно используются присоединиться к операции в приложениях , но не следует считать лучшим выбором во всех ситуациях. Внутреннее соединение создает новую таблицу результатов путем объединения значений столбцов двух таблиц (A и B) на основе предиката соединения. Запрос сравнивает каждую строку A с каждой строкой B, чтобы найти все пары строк, которые удовлетворяют предикату соединения. Когда предикат соединения удовлетворяется путем сопоставления значений, отличных от NULL, значения столбцов для каждой сопоставленной пары строк A и B объединяются в строку результата.
Результат объединения может быть определен как результат первого использования декартова произведения (или перекрестного соединения ) всех строк в таблицах (объединение каждой строки в таблице A с каждой строкой в таблице B) и последующего возврата всех строк, удовлетворяющих требованиям присоединиться к предикату. В реальных реализациях SQL обычно используются другие подходы, такие как хеш-соединения или соединения сортировки-слияния , поскольку вычисление декартова произведения происходит медленнее и часто требует для хранения недопустимо большого количества памяти.
SQL определяет два разных синтаксических способа выражения объединений: «явную нотацию соединения» и «неявную нотацию соединения». «Нотация неявного соединения» больше не считается лучшей практикой, хотя системы баз данных все еще поддерживают ее.
«Явная нотация соединения» использует JOIN
ключевое слово, которому может предшествовать INNER
ключевое слово, чтобы указать таблицу для соединения, и ON
ключевое слово, чтобы указать предикаты для соединения, как в следующем примере:
ВЫБЕРИТЕ сотрудника . Фамилия , сотрудник . DepartmentID , отдел . Имя отдела ОТ сотрудника ВНУТРЕННЕЕ СОЕДИНЕНИЕ департамента НА сотруднике . DepartmentID = отдел . DepartmentID ;
Employee.LastName | Employee.DepartmentID | Department.DepartmentName |
---|---|---|
Робинсон | 34 | Канцелярский |
Джонс | 33 | Инженерное дело |
Смит | 34 | Канцелярский |
Гейзенберг | 33 | Инженерное дело |
Рафферти | 31 год | Продажи |
«Нотация неявного соединения» просто перечисляет таблицы для объединения в FROM
предложении SELECT
оператора, используя запятые для их разделения. Таким образом, он определяет перекрестное соединение , и в WHERE
предложении могут применяться дополнительные предикаты фильтра (которые работают аналогично предикатам соединения в явной нотации).
Следующий пример эквивалентен предыдущему, но на этот раз с использованием неявной нотации соединения:
ВЫБЕРИТЕ сотрудника . Фамилия , сотрудник . DepartmentID , отдел . Название отдела ОТ сотрудника , отдел ГДЕ сотрудник . DepartmentID = отдел . DepartmentID ;
Запросы, приведенные в приведенных выше примерах, будут соединяться с таблицами Employee и Department с использованием столбца DepartmentID обеих таблиц. Если DepartmentID этих таблиц совпадает (т. Е. Выполняется предикат соединения), запрос объединит столбцы LastName , DepartmentID и DepartmentName из двух таблиц в строку результатов. Если DepartmentID не совпадает, строка результатов не создается.
Таким образом, результатом выполнения вышеуказанного запроса будет:
Employee.LastName | Employee.DepartmentID | Department.DepartmentName |
---|---|---|
Робинсон | 34 | Канцелярский |
Джонс | 33 | Инженерное дело |
Смит | 34 | Канцелярский |
Гейзенберг | 33 | Инженерное дело |
Рафферти | 31 год | Продажи |
Сотрудник «Вильямс» и отдел «Маркетинг» не отображаются в результатах выполнения запроса. Ни у одного из них нет совпадающих строк в другой соответствующей таблице: «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.
Естественное соединение
Естественное соединение - это частный случай равносоединения. Естественное соединение (⋈) - это бинарный оператор, который записывается как ( R ⋈ S ), где 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 без квалификатора:
DepartmentID | Employee.LastName | Department.DepartmentName |
---|---|---|
34 | Смит | Канцелярский |
33 | Джонс | Инженерное дело |
34 | Робинсон | Канцелярский |
33 | Гейзенберг | Инженерное дело |
31 год | Рафферти | Продажи |
PostgreSQL, MySQL и Oracle поддерживают естественные объединения; Microsoft T-SQL и IBM DB2 этого не делают. Столбцы, используемые в объединении, являются неявными, поэтому код объединения не показывает, какие столбцы ожидаются, а изменение имен столбцов может изменить результаты. В стандарте SQL: 2011 естественные объединения являются частью необязательного пакета F401 «Расширенная объединенная таблица».
Во многих средах баз данных имена столбцов контролируются внешним поставщиком, а не разработчиком запросов. Естественное соединение предполагает стабильность и согласованность имен столбцов, которые могут изменяться во время обновлений версии, требуемых поставщиком.
Внешнее соединение
В объединенной таблице сохраняется каждая строка, даже если другой соответствующей строки не существует. Внешние соединения подразделяются на левые внешние соединения, правые внешние соединения и полные внешние соединения, в зависимости от того, какие строки таблицы сохраняются: левая, правая или обе (в данном случае левая и правая относятся к двум сторонам JOIN
ключевого слова). Подобно внутренним соединениям , можно дополнительно подразделить все типы внешних соединений на равные соединения , естественные соединения , ( θ -соединения ) и т. Д. [8]ON
В стандартном SQL не существует неявной нотации для внешних соединений.
Левое внешнее соединение
Результат левого внешнего соединения (или просто левого соединения ) для таблиц A и B всегда содержит все строки «левой» таблицы (A), даже если условие соединения не находит ни одной совпадающей строки в «правой» таблице. (В). Это означает, что если ON
предложение соответствует 0 (нулю) строк в B (для данной строки в A), соединение все равно вернет строку в результате (для этой строки), но с NULL в каждом столбце из B. A слева. внешнее соединение возвращает все значения из внутреннего соединения плюс все значения в левой таблице, которые не соответствуют правой таблице, включая строки со значениями NULL (пустыми) в столбце ссылки.
Например, это позволяет нам найти отдел сотрудника, но по-прежнему показывает сотрудников, которые не были назначены в отдел (в отличие от приведенного выше примера внутреннего соединения, где неназначенные сотрудники были исключены из результата).
Пример левого внешнего соединения ( OUTER
ключевое слово необязательно) с дополнительной строкой результатов (по сравнению с внутренним соединением), выделенной курсивом:
ВЫБЕРИТЕ * ОТ сотрудник ЛЕВОГО OUTER JOIN отдела ПО сотруднику . DepartmentID = отдел . DepartmentID ;
Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
---|---|---|---|
Джонс | 33 | Инженерное дело | 33 |
Рафферти | 31 год | Продажи | 31 год |
Робинсон | 34 | Канцелярский | 34 |
Смит | 34 | Канцелярский | 34 |
Уильямс | NULL | NULL | NULL |
Гейзенберг | 33 | Инженерное дело | 33 |
Альтернативные синтаксисы
Oracle поддерживает устаревший синтаксис [9] :
ВЫБЕРИТЕ * ОТ сотрудника , отдела ГДЕ сотрудник . DepartmentID = отдел . DepartmentID ( + )
Sybase поддерживает синтаксис ( Microsoft SQL Server не поддерживает этот синтаксис с версии 2000):
ВЫБРАТЬ * ОТ сотрудника , отдела ГДЕ сотрудник . DepartmentID * = отдел . DepartmentID
IBM Informix поддерживает синтаксис:
ВЫБЕРИТЕ * ОТ сотрудника , ВНЕШНИЙ отдел, ГДЕ сотрудник . DepartmentID = отдел . DepartmentID
Правое внешнее соединение
Правое внешнее объединение (или право присоединиться ) близко напоминает левое внешнее соединение, за исключением лечения таблиц вспять. Каждая строка из «правой» таблицы (B) появится в объединенной таблице хотя бы один раз. Если подходящей строки из «левой» таблицы (A) не существует, NULL появится в столбцах из A для тех строк, которые не совпадают в B.
Правое внешнее соединение возвращает все значения из правой таблицы и совпадающие значения из левой таблицы (NULL в случае отсутствия соответствующего предиката соединения). Например, это позволяет нам найти каждого сотрудника и его или ее отдел, но все же показать отделы, в которых нет сотрудников.
Ниже приведен пример правого внешнего соединения ( OUTER
ключевое слово необязательно) с дополнительной строкой результатов, выделенной курсивом:
ВЫБЕРИТЕ * ОТ сотрудника RIGHT OUTER JOIN отдел ПО работника . DepartmentID = отдел . DepartmentID ;
Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
---|---|---|---|
Смит | 34 | Канцелярский | 34 |
Джонс | 33 | Инженерное дело | 33 |
Робинсон | 34 | Канцелярский | 34 |
Гейзенберг | 33 | Инженерное дело | 33 |
Рафферти | 31 год | Продажи | 31 год |
NULL | NULL | Маркетинг | 35 год |
Правое и левое внешние соединения функционально эквивалентны. Ни один из них не обеспечивает какой-либо функциональности, чем другой, поэтому правое и левое внешнее объединение могут заменять друг друга, если порядок в таблице меняется.
Полное внешнее соединение
Концептуально полное внешнее соединение сочетает в себе эффект применения как левого, так и правого внешних объединений. Если строки в FULL OUTER JOINed таблицах не совпадают, набор результатов будет иметь значения NULL для каждого столбца таблицы, в котором отсутствует соответствующая строка. Для тех строк, которые действительно совпадают, в результирующем наборе будет создана одна строка (содержащая столбцы, заполненные из обеих таблиц).
Например, это позволяет нам видеть каждого сотрудника, который находится в отделе, и каждый отдел, в котором есть сотрудник, но также видеть каждого сотрудника, который не является частью отдела, и каждый отдел, в котором нет сотрудника.
Пример полного внешнего соединения ( OUTER
ключевое слово необязательно):
ВЫБЕРИТЕ * ОТ сотрудника ПОЛНОГО OUTER JOIN отдела ПО сотруднику . DepartmentID = отдел . DepartmentID ;
Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
---|---|---|---|
Смит | 34 | Канцелярский | 34 |
Джонс | 33 | Инженерное дело | 33 |
Робинсон | 34 | Канцелярский | 34 |
Уильямс | NULL | NULL | NULL |
Гейзенберг | 33 | Инженерное дело | 33 |
Рафферти | 31 год | Продажи | 31 год |
NULL | NULL | Маркетинг | 35 год |
Некоторые системы баз данных не поддерживают полную функциональность внешнего соединения напрямую, но они могут имитировать ее с помощью внутреннего соединения и выбора 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
таблицу, например следующую:
EmployeeID | Фамилия | Страна | DepartmentID |
---|---|---|---|
123 | Рафферти | Австралия | 31 год |
124 | Джонс | Австралия | 33 |
145 | Гейзенберг | Австралия | 33 |
201 | Робинсон | Соединенные Штаты | 34 |
305 | Смит | Германия | 34 |
306 | Уильямс | Германия | NULL |
Пример запроса решения может быть следующим:
ВЫБРАТЬ 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 ;
В результате создается следующая таблица.
EmployeeID | Фамилия | EmployeeID | Фамилия | Страна |
---|---|---|---|---|
123 | Рафферти | 124 | Джонс | Австралия |
123 | Рафферти | 145 | Гейзенберг | Австралия |
124 | Джонс | 145 | Гейзенберг | Австралия |
305 | Смит | 306 | Уильямс | Германия |
В этом примере:
F
иS
являются псевдонимами для первой и второй копий таблицы сотрудников.- Условие
F.Country = S.Country
исключает пары между сотрудниками в разных странах. В примере вопроса нужны только пары сотрудников из одной страны. - Условие
F.EmployeeID < S.EmployeeID
исключает пары, в которых значениеEmployeeID
первого сотрудника больше или равно значениюEmployeeID
второго сотрудника. Другими словами, эффект этого условия состоит в том, чтобы исключить повторяющиеся пары и самопары. Без него была бы сгенерирована следующая менее полезная таблица (в таблице ниже отображается только часть результата "Германия"):
EmployeeID | Фамилия | EmployeeID | Фамилия | Страна |
---|---|---|---|---|
305 | Смит | 305 | Смит | Германия |
305 | Смит | 306 | Уильямс | Германия |
306 | Уильямс | 305 | Смит | Германия |
306 | Уильямс | 306 | Уильямс | Германия |
Только одна из двух средних пар необходима для ответа на исходный вопрос, а самая верхняя и самая нижняя пары не представляют интереса в этом примере.
Альтернативы
Эффект внешнего соединения также может быть получен с помощью UNION ALL между INNER JOIN и SELECT строк в «основной» таблице, которые не удовлетворяют условию соединения. Например,
ВЫБЕРИТЕ сотрудника . Фамилия , сотрудник . DepartmentID , отдел . DepartmentName ОТ сотрудника ВЛЕВО ВНЕШНИЙ СОЕДИНЕНИЕ департамента НА сотруднике . DepartmentID = отдел . DepartmentID ;
также можно записать как
ВЫБЕРИТЕ сотрудника . Фамилия , сотрудник . DepartmentID , отдел . Имя отдела ОТ сотрудника ВНУТРЕННЕЕ СОЕДИНЕНИЕ департамента НА сотруднике . DepartmentID = отдел . DepartmentIDСОЮЗ ВСЕВЫБЕРИТЕ сотрудника . Фамилия , сотрудник . DepartmentID , литая ( NULL , как VARCHAR ( 20 )) ОТ сотрудника ГДЕ НЕ СУЩЕСТВУЕТ ( ВЫБЕРИТЕ * FROM отдела WHERE сотрудника . DepartmentID = отдел . DepartmentID )
Выполнение
Большая часть работы в системах баз данных была направлена на эффективную реализацию объединений, потому что реляционные системы обычно требуют объединения, но сталкиваются с трудностями в оптимизации их эффективного выполнения. Проблема возникает из-за того, что внутренние соединения работают как коммутативно, так и ассоциативно . На практике это означает, что пользователь просто предоставляет список таблиц для объединения и используемые условия объединения, а перед системой базы данных стоит задача определить наиболее эффективный способ выполнения операции. Оптимизатор запросов определяет , как выполнить запрос , содержащий соединения. Оптимизатор запросов имеет две основные свободы:
- Порядок объединения: поскольку он объединяет функции коммутативно и ассоциативно, порядок, в котором система объединяет таблицы, не меняет окончательный набор результатов запроса. Однако порядок соединения может иметь огромное влияние на стоимость операции соединения, поэтому выбор наилучшего порядка соединения становится очень важным.
- Метод соединения : при наличии двух таблиц и условия соединения несколько алгоритмов могут создать результирующий набор соединения. Какой алгоритм работает наиболее эффективно, зависит от размеров входных таблиц, количества строк из каждой таблицы, соответствующих условию соединения, и операций, необходимых для остальной части запроса.
Многие алгоритмы соединения по-разному обрабатывают свои входные данные. Можно ссылаться на входы соединения как на «внешний» и «внутренний» операнды соединения, или «левый» и «правый», соответственно. Например, в случае вложенных циклов система базы данных будет сканировать все внутреннее отношение для каждой строки внешнего отношения.
Планы запросов, включающие объединения, можно классифицировать следующим образом: [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)
Рекомендации
Цитаты
- ^ SQL ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ
- ^ Грег Робиду, «Избегайте функций SQL Server в предложении WHERE для повышения производительности», MSSQL Tips, 3 мая 2007 г.
- ^ Патрик Вольф, "Внутри Oracle APEX". Осторожно при использовании функций PL / SQL в операторе SQL ", 30 ноября 2006 г.
- ^ Грегори А. Ларсен, «Рекомендации по T-SQL - не используйте функции скалярного значения в списке столбцов или предложениях WHERE», 29 октября 2009 г.,
- ^ Упрощение объединений с помощью ключевого слова USING
- ^ В Юникоде символ бабочки - ⋈ (U + 22C8).
- ^ Спросите Тома: «Поддержка соединений ANSI в Oracle». Назад к основам: внутренние соединения »Блог Эдди Авада, заархивированный 19 ноября 2010 г. в Wayback Machine
- ^ Зильбершатц, Авраам ; Корт, Хэнк; Сударшан, С. (2002). «Раздел 4.10.2: Типы и условия соединения». Концепции системы баз данных (4-е изд.). п. 166. ISBN. 0072283637.
- ^ Oracle Левое внешнее соединение
- ^ Шах 2005 , стр. 165
- ^ Адаптировано из Pratt 2005 , стр. 115-6
- Перейти ↑ Yu & Meng 1998 , p. 213
- ^ Индекс объединения растровых изображений Oracle. URL: http://www.dba-oracle.com/art_builder_bitmap_join_idx.htm
- ^ Индексы соединения Teradata. «Архивная копия» . Архивировано из оригинала на 2012-12-16 . Проверено 14 июня 2012 .CS1 maint: заархивированная копия как заголовок ( ссылка )
- ^ «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 объединяется