Что такое код join


Содержание

Инструкция JOIN

Ранее мы рассмотрели применение инструкции SELECT для выборки данных из одной таблицы базы данных. Если бы возможности языка Transact-SQL ограничивались поддержкой только таких простых инструкций SELECT, то присоединение в запросе двух или больше таблиц для выборки из них данных было бы невозможно. Следственно, все данные базы данных требовалось бы хранить в одной таблице. Хотя такой подход является вполне возможным, ему присущ один значительный недостаток — хранимые таким образом данные характеризуются высокой избыточностью.

Язык Transact-SQL устраняет этот недостаток, предоставляя для этого оператор соединения JOIN, который позволяет извлекать данные более чем из одной таблицы. Этот оператор, наверное, является наиболее важным оператором для реляционных систем баз данных, поскольку благодаря ему имеется возможность распределять данные по нескольким таблицам, обеспечивая, таким образом, важное свойство систем баз данных — отсутствие избыточности данных.

Оператор UNION, который мы рассмотрели ранее, также позволяет выполнять запрос по нескольким таблицам. Но этот оператор позволяет присоединить несколько инструкций SELECT, тогда как оператор соединения JOIN соединяет несколько таблиц с использованием всего лишь одной инструкции SELECT. Кроме этого, оператор UNION объединяет строки таблиц, в то время как оператор JOIN соединяет столбцы.

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

декартово произведение или перекрестное соединение;

тета-соединение, самосоединение и полусоединение.

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

Две синтаксические формы реализации соединений

Для соединения таблиц можно использовать две разные синтаксические формы оператора соединения:

явный синтаксис соединения (синтаксис соединения ANSI SQL:1992);

неявный синтаксис соединения (синтаксис соединения «старого стиля»).

Синтаксис соединения ANSI SQL:1992 был введен стандартом SQL92 и определяет операции соединения явно, т.е. используя соответствующее имя для каждого типа операции соединения. При явном объявлении соединения используются следующие ключевые слова:

LEFT [OUTER] JOIN;

RIGHT [OUTER] JOIN;

FULL [OUTER] JOIN.

Ключевое слово CROSS JOIN определяет декартово произведение двух таблиц. Ключевое слово INNER JOIN определяет естественное соединение двух таблиц, а LEFT OUTER JOIN и RIGHT OUTER JOIN определяют одноименные операции соединения. Наконец, ключевое слово FULL OUTER JOIN определяет соединение правого и левого внешнего соединений. Все эти операции соединения рассматриваются в последующих разделах.

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

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

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

Термины «естественное соединение» (natural join) и «соединение по эквивалентности» (equi-join) часто используют синонимично, но между ними есть небольшое различие. Операция соединения по эквивалентности всегда имеет одну или несколько пар столбцов с идентичными значениями в каждой строке. Операция, которая устраняет такие столбцы из результатов операции соединения по эквивалентности, называется естественным соединением. Наилучшим способом объяснить естественное соединение можно посредством примера:

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

В этом примере в инструкции SELECT для выборки указаны все столбцы таблиц для сотрудника Employee и отдела Department. Предложение FROM инструкции SELECT определяет соединяемые таблицы, а также явно указывает тип операции соединения — INNER JOIN. Предложение ON является частью предложения FROM и указывает соединяемые столбцы в обеих таблицах. Выражение «Employee.DepartamentNumber = Department.Number» определяет условие соединения, а оба столбца условия называются столбцами соединения.

Результат выполнения этого запроса:

Эквивалентный запрос с применением неявного синтаксиса («старого стиля») будет выглядеть следующим образом:

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

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

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

Далее, из этой таблицы удаляются все строки, которые не удовлетворяют условию соединения «Employee.Number = Department.Number». Оставшиеся строки представляют результат первого примера выше. Соединяемые столбцы должны иметь идентичную семантику, т.е. оба столбца должны иметь одинаковое логическое значение. Соединяемые столбцы не обязательно должны иметь одинаковое имя (или даже одинаковый тип данных), хотя часто так и бывает.

Система базы данных не может определить логическое значение столбца. Например, она не может определить, что между столбцами номера проекта и табельного номера сотрудника нет ничего общего, хотя оба они имеют целочисленный тип данных. Поэтому система базы данных может только проверить тип данных и длину строк. Компонент Database Engine требует, что соединяемые столбцы имели совместимые типы данных, например INT и SMALLINT.

База данных SampleDb содержит три пары столбцов, где каждый столбец в паре имеет одинаковое логическое значение (а также одинаковые имена). Таблицы Employee и Department можно соединить по столбцам Employee.DepartmentNumber и Department.Number. Столбцами соединения таблиц Employee и Works_on являются столбцы Employee.Id и Works_on.EmpId. Наконец, таблицы Project и Works_on можно соединить по столбцам Project.Number и Works_on.ProjectNumber.

Имена столбцов в инструкции SELECT можно уточнить. В данном контексте под уточнением имеется в виду, что во избежание неопределенности относительно того, какой таблице принадлежит столбец, в имя столбца включается имя его таблицы (или псевдоним таблицы), отделенное точкой:

В большинстве инструкций SELECT столбцы не требуют уточнения, хотя обычно рекомендуется применять уточнение столбцов с целью улучшения понимания кода. Если же имена столбцов в инструкции SELECT неоднозначны (как, например, столбцы Number в таблицах Project и Department) использование уточненных имен столбцов является обязательным.

В инструкции SELECT с операцией соединения, кроме условия соединения предложение WHERE может содержать и другие условия, как это показано в примере ниже:

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

В примере ниже показано еще одно применение внутреннего соединения:

Соединение более чем двух таблиц

Теоретически количество таблиц, которые можно соединить в инструкции SELECT, неограниченно. (Но одно условие соединения совмещает только две таблицы!) Однако для компонента Database Engine количество соединяемых таблиц в инструкции SELECT ограничено 64 таблицами.

В примере ниже показано соединение трех таблиц базы данных SampleDb:

В этом примере происходит выборка имен и фамилий всех аналитиков (Job = ‘Аналитик’), чей отдел находится в Санкт-Петербурге (Location = ‘Санкт-Петербург’). Результат запроса, приведенного в примере выше, можно получить только в том случае, если соединить, по крайней мере, три таблицы: Works_on, Employee и Department. Эти таблицы можно соединить, используя две пары столбцов соединения:

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

Декартово произведение


В предшествующем разделе мы рассмотрели возможный способ создания естественного соединения. На первом шаге этого процесса каждая строка таблицы Employee соединяется с каждой строкой таблицы Department. Эта операция называется декартовым произведением (cartesian product). Запрос для создания соединения таблиц Employee и Department, используя декартово произведение, показан в примере ниже:

Декартово произведение соединяет каждую строку первой таблицы с каждой строкой второй. В общем, результатом декартового произведения первой таблицы с n строками и второй таблицы с m строками будет таблица с n*m строками. Таким образом, результирующий набор запроса в примере выше имеет 7 х 3 = 21 строку (эти строки содержат дублированные значения).

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

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

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

В примере ниже показана выборка всей информации для сотрудников, которые проживают и работают в одном и том же городе. Здесь используется таблица EmployeeEnh, которую мы создали в статье «Инструкция SELECT: расширенные возможности» при обсуждении оператора UNION.

Результат выполнения этого запроса:

В этом примере получение требуемых строк осуществляется посредством естественного соединения. Если бы в этот результат потребовалось включить сотрудников, проживающих в других местах, то нужно было применить левое внешнее соединение. Данное внешнее соединение называется левым потому, что оно возвращает все строки из таблицы с левой стороны оператора сравнения, независимо от того, имеются ли совпадающие строки в таблице с правой стороны. Иными словами, данное внешнее соединение возвратит строку с левой таблицы, даже если для нее нет совпадения в правой таблице, со значением NULL соответствующего столбца для всех строк с несовпадающим значением столбца другой, правой, таблицы. Для выполнения операции левого внешнего соединения компонент Database Engine использует оператор LEFT OUTER JOIN.

Операция правого внешнего соединения аналогична левому, но возвращаются все строки таблицы с правой части выражения. Для выполнения операции правого внешнего соединения компонент Database Engine использует оператор RIGHT OUTER JOIN.

В этом примере происходит выборка сотрудников (с включением полной информации) для таких городов, в которых сотрудники или только проживают (столбец City в таблице EmployeeEnh), или проживают и работают. Результат выполнения этого запроса:

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

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

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

Любую операцию внешнего соединения можно эмулировать, используя оператор UNION совместно с функцией NOT EXISTS. Таким образом, запрос, показанный в примере ниже, эквивалентен запросу левого внешнего соединения, показанному ранее. В данном запросе осуществляется выборка сотрудников (с включением полной информации) для таких городов, в которых сотрудники или только проживают или проживают и работают:

Первая инструкция SELECT объединения определяет естественное соединение таблиц EmployeeEnh и Department по столбцам соединения City и Location. Эта инструкция возвращает все города для всех сотрудников, в которых сотрудники и проживают и работают. Дополнительно, вторая инструкция SELECT объединения возвращает все строки таблицы EmployeeEnh, которые не отвечают условию в естественном соединении.

Другие формы операций соединения

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

Тета-соединение

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

Результат выполнения этого запроса:

В этом примере сравниваются соответствующие значения столбцов City и Location. В каждой строке результата значение столбца City сравнивается в алфавитном порядке с соответствующим значением столбца Location.

Самосоединение, или соединение таблицы самой с собой

Кроме соединения двух или больше разных таблиц, операцию естественного соединения можно применить к одной таблице. В данной операции таблица соединяется сама с собой, при этом один столбец таблицы сравнивается сам с собой. Сравнивание столбца с самим собой означает, что в предложении FROM инструкции SELECT имя таблицы употребляется дважды. Поэтому необходимо иметь возможность ссылаться на имя одной и той же таблицы дважды. Это можно осуществить, используя, по крайней мере, один псевдоним. То же самое относится и к именам столбцов в условии соединения в инструкции SELECT. Для того чтобы различить столбцы с одинаковыми именами, необходимо использовать уточненные имена.

Соединение таблицы с самой собой демонстрируется в примере ниже:

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

Здесь предложение FROM содержит два псевдонима для таблицы Department: t1 и t2. Первое условие в предложении WHERE определят столбцы соединения, а второе — удаляет ненужные дубликаты, обеспечивая сравнение каждого отдела с другими отделами.

Полусоединение

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

Результат выполнения запроса:

Как можно видеть, список выбора SELECT в полусоединении содержит только столбцы из таблицы Employee. Это и есть характерной особенностью операции полусоединения. Эта операция обычно применяется в распределенной обработке запросов, чтобы свести к минимуму объем передаваемых данных. Компонент Database Engine использует операцию полусоединения для реализации функциональности, называющейся соединением типа «звезда».

Оператор соединения JOIN

Оператор JOIN используется для выполнения операции соединения данных из двух наборов в один результирующий набор. Может быть использовано несколько разных типов операций соединения при выполнении SELECT. Какие строки войдут в результирующий набор зависит от типа операции соединения и от явно определенного условия соединения. Условие соединения, т.е. условие сопоставления строк таблиц друг с другом, представляет собой логическое выражение.

При необходимости объединении нескольких таблиц операция соединения должна применяться последовательно несколько раз.

Синтаксис оператора JOIN

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

В большинстве СУБД при использовании оператора JOIN в сочетании с ключевыми словами LEFT, RIGHT, FULL можно опустить операнд OUTER. Операнд INNER также в большинстве СУБД можно не использовать.

Если названия столбцов, по которым происходит соединение таблиц, совпадают, то вместо ON можно использовать USING. Для оператора CROSS JOIN условие не указывается.

Тестовые таблицы для проверки JOIN

Для дальнейших примеров создадим две простые тестовые таблицы. Справочные таблицы пользователей и их автомобилей. В первой таблице будет храниться идентификатор пользователя и его имя (nick). Во второй таблице список автомобилей (идентификатор, наименование) и идентификатор их владельцев/пользователей. Необходимо отметить, что пользователь может иметь несколько автомобилей или не иметь вообще.

Таблица пользователей

Таблица автомобилей

Внутреннее соединение, INNER JOIN


INNER JOIN — это оператор внутреннего соединения двух таблиц. Он является симметричным, поэтому порядок таблиц для оператора неважен.

Применяется INNER JOIN для получения только тех строк, для которых существует соответствие записей в главной и присоединяемой таблице. Алгоритм формирования результата : каждая строка главной таблицы сопоставляется с каждой строкой присоединяемой таблицы. После этого проверяется условие соединения. Если условие истинно, в результирующий набор добавляется соответствующая «соединённая» строка.

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

Чтобы получить данные, которые не подходят по условию, необходимо использовать внешнее объединение — OUTER JOIN.

Внешнее объединение, OUTER JOIN

При соединении двух таблиц оператором OUTER JOIN в результирующий набор в обязательном порядке войдут строки либо одной из таблиц, либо обеих таблиц. Ключевое слово OUTER можно опустить. Запись LEFT JOIN идентична LEFT OUTER JOIN.

Существует два типа внешнего объединения. Это LEFT OUTER JOIN и RIGHT OUTER JOIN. Работают данные операторы одинаково. Разница заключается в том, что при использовании LEFT JOIN основной таблицей является таблица, указанная после оператора FROM. К строкам данной таблицы при заданных условиях добавляются данные присоединяемой таблицы. Для оператора RIGHT OUTER JOIN все с точностью до наоборот.

Оператор внешнего соединения OUTER JOIN не является симметричным, поэтому порядок установления связи между таблицами для оператора важен.

Пример использования оператора LEFT OUTER JOIN

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

Оператор LEFT OUTER JOIN с фильтрацией

Добавив в код предыдущего примере условие «where a.name is null». В выборке останется только одна запись «ostap», так как только у него не определен автомобиль.

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

Оператор перекрёстного соединения, CROSS JOIN

CROSS JOIN — это оператор перекрёстного соединения (декартово произведение). Оператор является симметричным и порядок таблиц для оператора неважен.

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

В результатах набора приведены только первые 12 строк.

Операторы Inner Join и Outer (left, right, full) Join в SQL (Oracle)

Ключевое слово join в SQL используется при построении select выражений. Инструкция Join позволяет объединить колонки из нескольких таблиц в одну. Объединение происходит временное и целостность таблиц не нарушается. Существует три типа join-выражений:

В свою очередь, outer join может быть left, right и full (слово outer обычно опускается).

В качестве примера (DBMS Oracle) создадим две простые таблицы и сконструируем для них SQL-выражения с использованием join.

В первой таблице будет хранится ID пользователя и его nick-name, а во второй — ID ресурса, имя ресурса и ID пользователя, который может этот ресурс администрировать.

Содержимое таблиц пусть будет таким:

Конструкция join выглядит так:

Где join_type — тип join-выражения, table_name — имя таблицы, которая присоединяется к результату, condition — условие объединения таблиц.

Кострукция join располагается сразу после select-выражения. Можно использовать несколько таких конструкций подряд для объединения соответствующего кол-ва таблиц. Логичнее всего использовать join в том случае, когда таблица имеет внешний ключ (foreign key).

Inner join необходим для получения только тех строк, для которых существует соответствие записей главной таблицы и присоединяемой. Иными словами условие condition должно выполняться всегда. Пример:

Результат будет таким:

В случае с left join из главной таблицы будут выбраны все записи, даже если в присоединяемой таблице нет совпадений, то есть условие condition не учитывает присоединяемую (правую) таблицу. Пример:

Результат выполнения запроса:

Результат показывает все ресурсы и их администраторов, вне зависимотсти от того есть они или нет.

Right join отображает все строки удовлетворяющие правой части условия condition, даже если они не имеют соответствия в главной (левой) таблице:

А результат будет следующим:

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

Full outer join (ключевое слово outer можно опустить) необходим для отображения всех возможных комбинаций строк из нескольких таблиц. Иными словами, это объединение результатов left и right join.

А результат будет таким:

Некоторые СУБД не поддерживают такую функциональность (например, MySQL), в таких случаях обычно используют объединение двух запросов:

Наконец, cross join. Этот тип join еще называют декартовым произведением (на английском — cartesian product). Настоятельно рекомендую использовать его с умом, так как время выполнения запроса с увеличением числа таблиц и строк в них растет нелинейно. Вот пример запроса, который аналогичен cross join:

Конструкция Join (в сочетании с другими SQL конструкциями, например, group by) часто встречается при программировании под базы данных. Думаю, эта статья будет вам полезна.

Кстати, для проверки своих знаний в области баз данных (и в частности Oracle) рекомендую воспользоваться этим сайтом онлайн тестирования — Тесты по базам данных.

Что такое код join

В прошлой теме было рассмотрено неявное соединение таблиц. Оно производилось на основе простой выборки неявно путем сведения данных. Для явного соединения данных из двух таблиц применяется оператор JOIN . Общий формальный синтаксис применения оператора INNER JOIN:

Илон Маск рекомендует:  Имя фрейма


После оператора JOIN идет название второй таблицы, из которой надо добавить данные в выборку. Перед JOIN может использоваться необязательное ключевое слово INNER . Его наличие или отсутствие ни на что не влияет. Затем после ключевого слова ON указывается условие соединения. Это условие устанавливает, как две таблицы будут сравниваться. В большинстве случаев для соединения применяется первичный ключ главной таблицы и внешний ключ зависимой таблицы.

Возьмем таблицы с данными из прошлой темы:

Используя JOIN, выберем все заказы и добавим к ним информацию о товарах:

Поскольку таблицы могут содержать столбцы с одинаковыми названиями, то при указании столбцов для выборки указывается их полное имя вместе с именем таблицы, например, «Orders.ProductCount».

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

Подобным образом мы можем присоединять и другие таблицы. Например, добавим к заказу информацию о покупателе из таблицы Customers:

Благодаря соединению таблиц мы можем использовать их столбцы для фильтрации выборки или ее сортировки:

Условия после ключевого слова ON могут быть более сложными по составу:

В данном случае выбираем все заказы на товары, производителем которых является Apple.

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

8 способов объединения (JOIN) таблиц в SQL. Часть 1

Можно смело сказать, что операция объединения (JOIN) является наиболее мощной функциональной особенностью языка SQL. Эта операция — предмет зависти для всех нереляционных СУБД, поскольку ее концепция очень проста, но при этом широко применима в случаях, когда нужно объединить два набора данных.

Простыми словами, объединение двух таблиц заключается в объединении каждой строки первой таблицы с каждой строкой второй таблицы, для которых истинно значение некоторого предиката. Иллюстрация из мастер-класса по SQL демонстрирует эту концепцию:

Обратите также внимание на следующую статью, посвященную использованию диаграмм Венна (Venn diagram) для объяснения операции JOIN.

На рисунке выше представлена схема операции внутреннего объединения (INNER JOIN) в сравнении с различными операциями внешнего объединения (OUTER JOIN), но это далеко не все возможные варианты. Далее мы рассмотрим каждый из них в отдельности.

Обратите внимание, когда в данной статье мы говорим «X следует перед Y», имеется в виду, что «X логически следует перед Y». То есть, оптимизатор СУБД может выполнить Y раньше, чем X, в целях обеспечения более высокой производительности при неизменном результате. Подробнее о синтаксическом и логическом порядке операций вы моете прочитать в следующей статье.

Итак, давайте последовательно рассмотрим все типы объединений!

Перекрестное объединение (CROSS JOIN)

CROSS JOIN является базовым вариантом объединения и представляет собой декартово произведение (Cartesian product). Эта операция просто объединяет каждую строку первой таблицы с каждой строкой второй таблицы. Лучший пример, иллюстрирующий декартово произведение, представлен в Википедии. В этом примере мы получаем колоду карт, выполнив «перекрестное объединение» таблицы достоинств и таблицы мастей.

В реальных сценариях операция CROSS JOIN может быть очень полезна при создании отчетов. Например, мы можем сгенерировать набор дат (например, дни в месяце) (days) и выполнить перекрестное объединение со всеми отделами (departments), имеющимися в базе данных. В результате мы получим полную таблицу день/отдел. Используя синтаксис PostgreSQL:

Представим себе, что мы имеем следующие данные:

Результат операции CROSS JOIN будет выглядеть следующим образом:

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

Свойства

Как мы уже сказали, операция CROSS JOIN представляет собой декартово произведение. Соответственно, в математической нотации для описания данной операции используется знак умножения: A × B, или в нашем случае days × departments.

Как и в случае «обычного» арифметического умножения, если одна из двух таблиц пустая (имеет нулевой размер), результат также будет пустым. Это абсолютно логично. Если мы объединим 31 день и 0 отделов, мы получим 0 комбинаций день/отдел. Аналогично, если мы объединим пустой диапазон дат с любым количеством отделов, мы также получим 0 комбинаций день/отдел. Другими словами:

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

До того, как синтаксис операции JOIN был стандартизирован ANSI, чтобы реализовать CROSS JOIN, программисты просто использовали список разделенных запятыми таблиц в предложении FROM. Рассмотренный выше запрос эквивалентен следующему:

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

Внутреннее объединение (INNER JOIN) или тета-объединение (THETA JOIN)

Развивая идею предыдущей операции CROSS JOIN, операция INNER JOIN (или просто JOIN, иногда также THETA JOIN) позволяет выполнять фильтрацию результата декартова произведения на основе некоторого предиката. Как правило, мы помещаем этот предикат в предложение ON. Таким образом, запрос принимает следующий вид:

В большинстве СУБД ключевое слово INNER является необязательным, поэтому мы просто не указываем его.

Операция INNER JOIN позволяет нам использовать произвольные предикаты в предложении ON, что опять же очень удобно при создании отчетов. Аналогично CROSS JOIN мы объединяем все дни со всеми отделами, но потом оставляем только те комбинации день/отдел, для которых данный отдел уже существовал в данный день.

Используем те же исходные данные:

Получим следующий результат:

Результат операции содержит данные, начиная с 10 января. Более ранние даты были отфильтрованы.

Свойства

Операция INNER JOIN представляет собой операцию CROSS JOIN с фильтрацией. Это означает, что если одна из таблиц пустая, то результат также гарантированно будет пустым. По причине наличия предиката, результат операции INNER JOIN может быть меньшего объема, чем результат операции CROSS JOIN. Другими словами:

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

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

Безусловно, это просто запутывание кода, но ведь у нас могут быть свои причины, не так ли? Сделав еще один шаг, мы можем написать следующий запрос, который также является эквивалентным, поскольку большинство оптимизаторов способны распознать равнозначность и выполнить INNER JOIN:


Как мы уже говорили, CROSS JOIN это лишь удобный синтаксис для списка разделенных запятыми таблиц. Во фрагменте кода, представленном ниже, мы также используем предложение WHERE, чтобы сформировать запрос, которым программисты часто пользовались до того, как синтаксис JOIN был стандартизирован:

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

Объединение на основе равенства (EQUI JOIN)

Иногда в литературе встречается термин EQUI JOIN. На самом деле, «EQUI» не является ключевым словом SQL, а просто обозначает специальный вариант записи особого случая операции INNER JOIN.

Следует отметить, что не совсем правомерно называть EQUI JOIN особым случаем, поскольку эту операцию мы выполняем чаще всего в SQL и OLTP приложениях, когда просто объединяем таблицы на основе отношения первичного/внешнего ключа. Например:

Представленный выше запрос извлекает всех актеров и фильмы, в которых они снимались. В нем присутствуют две операции INNER JOIN. Первая из них объединяет таблицу актеров actor и соответствующие записи из таблицы film_actor, содержащей информацию об отношениях фильм/актер (поскольку каждый актер может играть во множестве фильмов, а в каждом фильме может играть множество актеров). Вторая операция INNER JOIN выполняет объединение с таблицей film, содержащей информацию о фильмах.

Свойства

Данная операция имеет те же свойства, что и «обычная» операция INNER JOIN. То есть EQUI JOIN также является декартовым произведением (CROSS JOIN) с отфильтрованным результатом. В частности, в нашем случае результат содержит только те комбинации актер/фильм, для которых данный актер действительно играл в данном фильме. Таким образом, мы снова имеем соотношение:

Объем результата может быть равен полному декартову произведению таблиц actor и film только в том случае, если каждый актер играл в каждом фильме, что маловероятно.

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

Опять же, мы могли бы записать операцию EQUI JOIN, используя CROSS JOIN или список разделенных запятыми таблиц, но это уже не интересно. Значительно больший интерес представляют два варианта альтернативного синтаксиса, представленные ниже, один из которых является очень полезным.

Предложение USING заменяет предложение ON и позволяет указать набор столбцов, которые должны присутствовать в обеих объединяемых таблицах. Если наша база данных была хорошо спроектирована (как, например, база данных Sakila), то есть, если каждый внешний ключ имеет такое же имя, как и соответствующий первичный ключ (например, actor.actor_ >

  • Derby
  • Firebird
  • HSQLDB
  • Ingres
  • MariaDB
  • MySQL
  • Oracle
  • PostgreSQL
  • SQLite
  • Vertica

Следующие СУБД, к сожалению, не поддерживают данный синтаксис:

  • Access
  • Cubrid
  • DB2
  • H2
  • HANA
  • Informix
  • SQL Server
  • Sybase ASE
  • Sybase SQL Anywhere

Запрос с предложением USING (почти) идентичен запросу с предложением ON, однако значительно более удобен для написания и восприятия. Мы сказали «почти», потому что согласно спецификации некоторых СУБД (и стандарту SQL) столбец, используемый в предложении USING, не должен иметь квалификатор. Например:

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

Если мы хотим выполнить объединение по original_language_id, нам придется использовать предложение ON.

Альтернативный синтаксис: Естественное объединение (NATURAL JOIN)

Более экстремальным и значительно менее полезным вариантом синтаксиса операции EQUI JOIN является синтаксис на основе предложения NATURAL JOIN. Рассмотренный выше синтаксис на основе USING можно «улучшить», заменив USING на NATURAL JOIN следующим образом:

Обратите внимание, в этом запросе нет необходимости указывать какие-либо критерии объединения, поскольку предложение NATURAL JOIN автоматически определяет столбцы, имеющие одинаковые имена в обеих объединяемых таблица, и помещает их в «скрытое» предложение USING. Если первичные и внешние ключи имеют одинаковые имена, этот подход может показаться полезным, однако это не так.

В базе данных Sakila, каждая таблица имеет столбец last_update, который автоматически используется предложением NATURAL JOIN. Таким образом, запрос NATURAL JOIN эквивалентен следующему запросу, который, конечно же, не имеет никакого смысла:

Итак, сразу же забудьте о NATURAL JOIN и никогда не используйте этот вариант (за исключением очень редких случаев, таких как объединение диагностических представлений Oracle, например, v$sql NATURAL JOIN v$sql_plan, в целях специализированной аналитики).

Внешнее объединение (OUTER JOIN)

Мы рассмотрели операцию INNER JOIN, возвращающую только те комбинации строк левой/правой таблицы, для которых значение предиката в предложении ON является истинным.

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

Левое внешнее объединение (LEFT OUTER JOIN)

Давайте вернемся к примеру с датами и отделами:

Ключевое слово «OUTER» является необязательным, поэтому мы его не указываем.

Этот запрос отличается от подобного запроса INNER JOIN лишь тем, что всегда будет возвращать хотя бы одну строку для каждого дня, даже если в данный день еще не существовало ни одного отдела. В частности, в нашем примере все отделы были созданы не ранее 10 января, но запрос все равно вернет строки, соответствующие 1–9 января.

Кроме строк, которые мы получили бы с помощью запроса INNER JOIN, в результате запроса LEFT OUTER JOIN также присутствуют строки, соответствующие 1–9 января, с пустыми (NULL) значениями отделов:

Как видите, каждый день хотя бы один раз присутствует в результате запроса. LEFT OUTER JOIN выполняет данную операцию для левой таблицы, то есть возвращает все строки левой таблицы.

Формально, операцию LEFT OUTER JOIN можно выразить операцией INNER JOIN с предложением UNION:

Мы обсудим NOT EXISTS далее в этой статье, когда будем рассматривать операцию SEMI JOIN.

Правое внешнее объединение (RIGHT OUTER JOIN)

Операция RIGHT OUTER JOIN выполняет ту же задачу, что и LEFT OUTER JOIN, но для правой таблицы, то есть возвращает в результате все строки правой таблицы. Немного модифицируем наши данные, добавив пару отделов:

Новые отделы 4 и 5 не попали бы в результат запроса INNER JOIN, поскольку были созданы после 31 января. Однако эти отделы появятся в результате запроса RIGHT OUTER JOIN, поскольку эта операция возвращает все строки правой таблицы.

Выполним следующий запрос:

Получим следующий результат:

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

Полное внешнее объединение (FULL OUTER JOIN)


Существует также операция FULL OUTER JOIN, которая возвращает в результате все строки как левой, так и правой таблицы. Для нашего примера это означает, что каждый день и каждый отдел хотя бы один раз появляются в результате запроса.

Используем те же данные:

Выполним следующий запрос:

Получим следующий результат:

Формально, операцию FULL OUTER JOIN можно выразить операцией INNER JOIN с предложением UNION:

Альтернативный синтаксис: Внешнее объединение на основе равенства (EQUI OUTER JOIN)

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

В результате этого запроса каждый актер будет присутствовать хотя бы один раз, независимо от того, принимал ли он участие в каком-либо фильме. Если мы также хотим извлечь все фильмы, в которых не снимался ни один из данных актеров, мы можем применить FULL OUTER JOIN:

Безусловно, в качестве альтернативы можно было бы использовать NATURAL LEFT JOIN, NATURAL RIGHT JOIN, NATURAL FULL JOIN, но, как мы уже говорили ранее, в таком случае в объединении автоматически был бы учтен столбец last_update, присутствующий во всех таблицах базы данных Sakila (т.е. USING (…, last_update)), что лишает операцию всякого смысла.

Альтернативный синтаксис: Внешнее объединение (OUTER JOIN) в стиле Oracle и SQL Server

До введения стандартного синтаксиса СУБД Oracle и SQL Server поддерживали операцию внешнего объединения в следующем виде:

Можно смело сказать, что этот синтаксис является устаревшим.

Разработчики SQL Server поступили правильно, вначале объявив этот синтаксис нежелательным, и в дальнейшем отказавшись от него. Oracle по-прежнему поддерживает его для обратной совместимости.

Нет никаких аргументов в пользу данного альтернативного синтаксиса. Используйте вместо него стандартный синтаксис ANSI.

Внешнее объединение с разделением (PARTITIONED OUTER JOIN)

Эта операция поддерживается только Oracle. На самом деле, просто удивительно, что другие СУБД до сих пор не реализовали ее. Помните операцию CROSS JOIN, которую мы использовали, чтобы получить все комбинации день/отдел? Так вот, иногда мы хотим получить следующий результат: все комбинации, а также, если выполняется условие, поместить в данную строку соответствующее значение.

Эту операцию трудно объяснить словами. Намного легче сделать это на примере. Ниже представлен запрос, использующий синтаксис Oracle:

Предложение PARTITION BY используется в различных контекстах для решения различных задач (например, для реализации оконных функций (window function)). В нашем случае PARTITION BY означает, что мы «разделяем» наши данные по значениям столбца departments.department, создавая таким образом «подгруппу» для каждого отдела. Затем каждая «подгруппа» получает копию всех дней, независимо от того, выполняется ли условие предиката (в отличие от обычной операции LEFT OUTER JOIN, в результате которой, часть дней имели пустые значения отделов). Представленный выше запрос даст следующий результат:

Как видите, мы имеем 5 «подгрупп», соответствующих 5 отделам. Каждая «подгруппа» объединяет данный отдел с каждым днем, но в отличие от CROSS JOIN, мы получаем результат LEFT OUTER JOIN .. ON .. в том случае, когда выполняется условие предиката. Это действительно полезная функциональность для создания отчетов в Oracle!

Left join (SQL) — пример, подробное описание, ошибки использования

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

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

Подготовка необходимых таблиц

Допустим, в нашей базе данных имеется информация о людях и их недвижимом имуществе. Основная информация основывается на трех таблицах: Peoples (люди), Realty (недвижимость), Realty_peoples (таблица с отношениями, кому из людей какая недвижимость принадлежит). Предположим, в таблицах хранятся следующие данные по людям:

Оператор соединения JOIN SQL

Доброго времени суток! В этой статье по языку SQL мы познакомимся с оператором соединения двух таблиц — JOIN. Как и всегда, разберем практические примеры и посмотрим на различные варианты применения оператора JOIN в SQL.

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

Введение

В прошлых статьях мы уже работали с запросами, которые используют данные из двух и более таблиц: многотабличные запросы и вложенные запросы в SQL. Но те способы имели свои минусы либо по скорости, либо по избыточности данных. Оператор соединения JOIN наиболее выгоден среди этих способов, но его использование подразумевает операции только с двумя таблицами. Тем не менее, запросы выполняются гораздо быстрее, чем вложенные запросы.

В этой статье мы разберем несколько вариантов применения оператора JOIN:

  • OUTER JOIN
    • RIGHT OUTER JOIN
    • LEFT OUTER JOIN
  • Про эти варианты использования мы и поговорим подробнее.

    Оператор INNER JOIN

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

    Вывести сумму заказов и дату, которые проводил продавец с фамилией Колованов. Используйте оператор INNER JOIN.

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

    amt odate
    348 2020-04-08
    80 2020-09-02

    Рассмотрим еще один пример на оператор INNER JOIN, уже сложнее:


    Вывести среднюю суму заказов для каждого продавца.

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

    Средняя цена sname
    214 Колованов
    315.667 Кучеров
    1180 Мозякин
    640 Плотников
    900 Проворов

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

    Также заметьте, что всего в нашей таблицы 8 продавцов, а тут всего 5 строк — просто у других продавцов нет заказов в таблице orders.

    Оператор OUTER JOIN

    В языке SQL оператор OUTER JOIN используется гораздо реже, но иногда является очень полезным. Сейчас мы рассмотрим два варианта использования этого оператора. Оператор осуществляет несимметричное внешнее соединение двух таблиц — то есть порядок таблиц важен.

    Оператор RIGHT OUTER JOIN

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

    И вот, что поменялось в выводе:

    Средняя цена sname
    214 Колованов
    315.667 Кучеров
    NULL Малкин
    1180 Мозякин
    NULL Петров
    640 Плотников
    900 Проворов
    NULL Шипачев

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

    Оператор LEFT OUTER JOIN

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

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

    Итак, нам нужны все покупатели — значит в качестве первой (левой) таблицы возьмем таблицу customers, а затем будем присоединять таблицу orders.

    cname odate
    Чудинов 2020-01-01
    Лосев 2020-04-10
    Краснов 2020-04-08
    Кириллов 2020-06-07
    Колесников 2020-12-04
    Колесников 2020-03-03
    Лермонтов 2020-09-02
    Деснов 2020-03-07
    Кириллов 2020-10-07
    Пушкин 2020-01-08
    Ермолаев NULL
    Белый NULL

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

    Примеры на соединение таблиц в SQL

    1.Напишите запрос, который бы использовал оператор INNER JOIN для получения всех Заказов для покупателя с фамилией Краснов.

    2.Напишите запрос, который бы вывел фамилии и города продавцов, комиссия которых составляет более 20%.

    3.Напишите запрос, который бы вывел суммарную сумму заказов для городов в которых работают продавцы.

    4.Повторите предыдущий запрос, но выведите все города, даже если в них не совершалась сделка.

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

    Заключение

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

    Joins (SQL Server) Joins (SQL Server)

    ОБЛАСТЬ ПРИМЕНЕНИЯ: SQL Server База данных SQL Azure Azure Synapse Analytics (хранилище данных SQL) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

    SQL Server SQL Server выполняет операции сортировки, пересечения, объединения и поиска различий при помощи технологий хэш-соединений и сортировки в оперативной памяти. performs sort, intersect, union, and difference operations using in-memory sorting and hash join technology. При выполнении запросов этого типа SQL Server SQL Server поддерживает вертикальное секционирование таблиц, иногда называемое хранилищем столбцов. Using this type of query plan, SQL Server SQL Server supports vertical table partitioning, sometimes called columnar storage.

    В SQL Server SQL Server применяются операции соединения трех типов: SQL Server SQL Server employs four types of join operations:

    • Соединения вложенных циклов Nested Loops joins
    • Соединения слиянием. Merge joins
    • Хэш-соединения. Hash joins
    • Адаптивные соединения (начиная с SQL Server 2020 (14.x) SQL Server 2020 (14.x) ). Adaptive joins (Starting with SQL Server 2020 (14.x) SQL Server 2020 (14.x) )

    Основные принципы соединения Join Fundamentals

    С помощью соединения можно получать данные из двух или нескольких таблиц на основе логических связей между ними. By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Соединения позволяют указать, как в SQL Server SQL Server должны использоваться данные из одной таблицы для выбора строк из другой таблицы. Joins indicate how SQL Server SQL Server should use data from one table to select the rows in another table.

    Соединение определяет способ связывания двух таблиц в запросе следующим образом: A join condition defines the way two tables are related in a query by:

    • для каждой таблицы указываются столбцы, используемые в соединении. Specifying the column from each table to be used for the join. В типичном условии соединения указывается внешний ключ из одной таблицы и связанный с ним ключ из другой таблицы; A typical join condition specifies a foreign key from one table and its associated key in the other table.
    • указывается логический оператор (например, = или <>,) для сравнения значений столбцов. Specifying a logical operator (for example, = or <>,) to be used in comparing values from the columns.

    Внутренние соединения можно задавать в предложениях FROM и WHERE . Inner joins can be specified in either the FROM or WHERE clauses. Внешние соединения можно задавать только в предложении FROM . Outer joins can be specified in the FROM clause only. Условия соединения сочетаются с условиями поиска WHERE и HAVING для управления строками, выбранными из базовых таблиц, на которые ссылается предложение FROM . The join conditions combine with the WHERE and HAVING search conditions to control the rows that are selected from the base tables referenced in the FROM clause.

    То, что условия соединения задаются в предложении FROM , помогает отделить их от условий поиска, которые могут быть заданы в предложении WHERE . Объединение рекомендуется задавать именно таким способом. Specifying the join conditions in the FROM clause helps separate them from any other search conditions that may be specified in a WHERE clause, and is the recommended method for specifying joins. Ниже приведен упрощенный синтаксис соединения с использованием предложения FROM стандарта ISO: A simplified ISO FROM clause join syntax is:

    join_type указывает на выполняемый тип соединения: внутреннее, внешнее или перекрестное. join_type specifies what kind of join is performed: an inner, outer, or cross join. join_condition определяет предикат, который будет вычисляться для каждой пары соединяемых строк. join_condition defines the predicate to be evaluated for each pair of joined rows. Ниже приведен пример предложения FROM с заданным соединением: The following is an example of a FROM clause join specification:

    Ниже приведена простая инструкция SELECT, использующая это соединение: The following is a simple SELECT statement using this join:


    Инструкция возвращает наименование продукта и сведения о поставщике для всех сочетаний запчастей, поставляемых компаниями с названиями на букву F и стоимостью продукта более 10 долларов. The select returns the product and supplier information for any combination of parts supplied by a company for which the company name starts with the letter F and the price of the product is more than $10.

    Если один запрос содержит ссылки на несколько таблиц, то все ссылки столбцов должны быть однозначными. When multiple tables are referenced in a single query, all column references must be unambiguous. В предыдущем примере в обеих таблицах (ProductVendor и Vendor) есть столбец с именем BusinessEntityID. In the previous example, both the ProductVendor and Vendor table have a column named BusinessEntityID. Имена столбцов, совпадающие в двух или более таблицах, на которые ссылается запрос, должны уточняться именем таблицы. Any column name that is duplicated between two or more tables referenced in the query must be qualified with the table name. Все ссылки на столбец Vendor в этом примере являются уточненными. All references to the Vendor columns in the example are qualified.

    Если имя столбца не дублируется в двух или более таблицах, указанных в запросе, то ссылки на него уточнять именем таблицы не обязательно. When a column name is not duplicated in two or more tables used in the query, references to it do not have to be qualified with the table name. Это показано в предыдущем примере. This is shown in the previous example. Подобную инструкцию SELECT иногда трудно понять, поскольку в ней нет ничего, что указывало бы на таблицы, из которых берутся столбцы. Such a SELECT statement is sometimes difficult to understand because there is nothing to indicate the table that provided each column. Запрос гораздо легче читать, если все столбцы указаны с именами соответствующих таблиц. The readability of the query is improved if all columns are qualified with their table names. Запрос будет читаться еще легче, если используются псевдонимы таблиц, особенно когда имена таблиц сами должны уточняться именами базы данных и владельца. The readability is further improved if table aliases are used, especially when the table names themselves must be qualified with the database and owner names. Ниже приведен тот же пример, но чтобы упростить чтение, используются псевдонимы таблиц, уточняющие названия столбцов. The following is the same example, except that table aliases have been assigned and the columns qualified with table aliases to improve readability:

    В предыдущем примере условие соединения задается в предложении FROM, что является рекомендуемым способом. The previous examples specified the join conditions in the FROM clause, which is the preferred method. В следующем запросе это же условие соединения указывается в предложении WHERE: The following query contains the same join condition specified in the WHERE clause:

    Список выборки для соединения может ссылаться на все столбцы в соединяемых таблицах или на любое подмножество этих столбцов. The select list for a join can reference all the columns in the joined tables, or any subset of the columns. Список выборки не обязательно должен содержать столбцы из каждой таблицы в соединении. The select list is not required to contain columns from every table in the join. Например, в соединении из трех таблиц связующим звеном между одной из таблиц и третьей таблицей может быть только одна таблица, при этом список выборки не обязательно должен ссылаться на столбцы средней таблицы. For example, in a three-table join, only one table can be used to bridge from one of the other tables to the third table, and none of the columns from the middle table have to be referenced in the select list.

    Хотя обычно в условиях соединения для сравнения используется оператор равенства (=), можно указать другие операторы сравнения или реляционные операторы, равно как другие предикаты. Although join conditions usually have equality comparisons (=), other comparison or relational operators can be specified, as can other predicates. Дополнительные сведения см. в разделах Операторы сравнения (Transact-SQL) и WHERE (Transact-SQL). For more information, see Comparison Operators (Transact-SQL) and WHERE (Transact-SQL).

    При обработке соединений в SQL Server SQL Server механизм запросов выбирает наиболее эффективный метод обработки из нескольких возможных. When SQL Server SQL Server processes joins, the query engine chooses the most efficient method (out of several possibilities) of processing the join. При физическом выполнении различных соединений можно использовать много разных оптимизаций, поэтому их нельзя надежно прогнозировать. The physical execution of various joins can use many different optimizations and therefore cannot be reliably predicted.

    Столбцы, используемые в условии соединения, не обязательно должны иметь одинаковые имена или одинаковый тип данных. Columns used in a join condition are not required to have the same name or be the same data type. Однако если типы данных не совпадают, то они должны быть совместимыми или допускать в SQL Server неявное преобразование. However, if the data types are not identical, they must be compatible, or be types that SQL Server can implicitly convert. Если типы данных не допускают неявное преобразование, то условия соединения должны явно преобразовывать эти типы данных с помощью функции CAST . If the data types cannot be implicitly converted, the join condition must explicitly convert the data type using the CAST function. Дополнительные сведения о подразумеваемых и явных преобразованиях см. в статье Преобразование типов данных (ядро СУБД). For more information about implicit and explicit conversions, see Data Type Conversion (Database Engine).

    Большинство запросов, использующих соединение, можно переписать с помощью вложенных запросов и наоборот. Most queries using a join can be rewritten using a subquery (a query nested within another query), and most subqueries can be rewritten as joins. Дополнительные сведения о вложенных запросах см. в разделе Вложенные запросы. For more information about subqueries, see Subqueries.

    Таблицы невозможно соединять непосредственно по столбцам ntext, text или image. Tables cannot be joined directly on ntext, text, or image columns. Однако соединить таблицы по столбцам ntext, text или image можно косвенно, с помощью SUBSTRING . However, tables can be joined indirectly on ntext, text, or image columns by using SUBSTRING .
    Например, SELECT * FROM t1 JOIN t2 ON SUBSTRING(t1.textcolumn, 1, 20) = SUBSTRING(t2.textcolumn, 1, 20) выполняет внутреннее соединение двух таблиц по первым 20 символам текстовых столбцов в таблицах t1 и t2. For example, SELECT * FROM t1 JOIN t2 ON SUBSTRING(t1.textcolumn, 1, 20) = SUBSTRING(t2.textcolumn, 1, 20) performs a two-table inner join on the first 20 characters of each text column in tables t1 and t2.
    Другая возможность сравнения столбцов ntext и text из двух таблиц заключается в сравнении длины столбцов с предложением WHERE , например: WHERE DATALENGTH(p1.pr_info) = DATALENGTH(p2.pr_info) In addition, another possibility for comparing ntext or text columns from two tables is to compare the lengths of the columns with a WHERE clause, for example: WHERE DATALENGTH(p1.pr_info) = DATALENGTH(p2.pr_info)

    Основные сведения о соединениях вложенных циклов Understanding Nested Loops joins

    Если один вход соединения имеет небольшой размер (менее десяти строк), а другой вход сравнительно большой и индексирован по соединяемым столбцам, индексное соединение вложенных циклов является самой быстрой операцией соединения, так как для нее потребуется наименьшее количество операций сравнения и ввода-вывода. If one join input is small (fewer than 10 rows) and the other join input is fairly large and indexed on its join columns, an index nested loops join is the fastest join operation because they require the least I/O and the fewest comparisons.

    Соединение вложенных циклов, называемое также вложенной итерацией, использует один ввод соединения в качестве внешней входной таблицы (на графической схеме выполнения она является верхним входом), а второй в качестве внутренней (нижней) входной таблицы. The nested loops join, also called nested iteration, uses one join input as the outer input table (shown as the top input in the graphical execution plan) and one as the inner (bottom) input table. Внешний цикл использует внешнюю входную таблицу построчно. The outer loop consumes the outer input table row by row. Во внутреннем цикле для каждой внешней строки производится сканирование внутренней входной таблицы и вывод совпадающих строк. The inner loop, executed for each outer row, searches for matching rows in the inner input table.

    В простейшем случае во время поиска целиком просматривается таблица или индекс; это называется упрощенным соединением вложенных циклов. In the simplest case, the search scans an entire table or index; this is called a naive nested loops join. Если при поиске используется индекс, то такой поиск называется индексным соединением вложенных циклов. If the search exploits an index, it is called an index nested loops join. Если индекс создается в качестве части плана запроса (и уничтожается после завершения запроса), то он называется временным индексным соединением вложенных циклов. If the index is built as part of the query plan (and destroyed upon completion of the query), it is called a temporary index nested loops join. Все эти варианты учитываются оптимизатором запросов. All these variants are considered by the Query Optimizer.

    Соединение вложенных циклов является особенно эффективным в случае, когда внешние входные данные сравнительно невелики, а внутренние входные данные велики и заранее индексированы. A nested loops join is particularly effective if the outer input is small and the inner input is preindexed and large. Во многих небольших транзакциях, работающих с небольшими наборами строк, индексное соединение вложенных циклов превосходит как соединения слиянием, так и хэш-соединения. In many small transactions, such as those affecting only a small set of rows, index nested loops joins are superior to both merge joins and hash joins. Однако в больших запросах соединения вложенных циклов часто являются не лучшим вариантом. In large queries, however, nested loops joins are often not the optimal choice.

    Если для атрибута OPTIMIZED оператора соединения вложенными циклами задано значение True, это означает, что оптимизированные соединения вложенными циклами (или пакетная сортировка) используются для уменьшения количества операций ввода-вывода, когда внутренняя таблица имеет большой размер, независимо от того, выполняется ли ее параллельная обработка. When the OPTIMIZED attribute of a Nested Loops join operator is set to True, it means that an Optimized Nested Loops (or Batch Sort) is used to minimize I/O when the inner side table is large, regardless of it being parallelized or not. Такая оптимизация в этом плане выполнения может быть не слишком очевидна при анализе плана, если сама сортировка выполняется как скрытая операция. The presence of this optimization in a given plan may not be very obvious when analyzing an execution plan, given the sort itself is a hidden operation. Но изучив XML-код плана для атрибута OPTIMIZED, можно обнаружить, что соединение вложенными циклами, возможно, попытается изменить порядок входных строк, чтобы повысить производительность операций ввода-вывода. But by looking in the plan XML for the attribute OPTIMIZED, this indicates the Nested Loops join may try to reorder the input rows to improve I/O performance.

    Основные сведения о соединениях слиянием Understanding Merge joins

    Если два входа соединения достаточно велики, но отсортированы по соединяемым столбцам (например, если они были получены просмотром отсортированных индексов), то наиболее быстрой операцией соединения будет соединение слиянием. If the two join inputs are not small but are sorted on their join column (for example, if they were obtained by scanning sorted indexes), a merge join is the fastest join operation. Если оба входа соединения велики и имеют сходные размеры, соединение слиянием с предварительной сортировкой и хэш-соединение имеют примерно одинаковую производительность. If both join inputs are large and the two inputs are of similar sizes, a merge join with prior sorting and a hash join offer similar performance. Однако операции хэш-соединения часто выполняются быстрее, если два входа значительно отличаются по размеру. However, hash join operations are often much faster if the two input sizes differ significantly from each other.

    Соединение слиянием требует сортировки обоих наборов входных данных по столбцам слияния, которые определены предложениями равенства (ON) предиката объединения. The merge join requires both inputs to be sorted on the merge columns, which are defined by the equality (ON) clauses of the join predicate. Оптимизатор запросов обычно просматривает индекс, если для соответствующего набора столбцов такой существует, или устанавливает оператор сортировки под соединением слиянием. The query optimizer typically scans an index, if one exists on the proper set of columns, or it places a sort operator below the merge join. В редких случаях может быть несколько предложений равенства, но столбцы слияния берутся только из некоторых доступных предложений равенства. In rare cases, there may be multiple equality clauses, but the merge columns are taken from only some of the available equality clauses.

    Так как каждый набор входных данных сортируется, оператор Merge Join получает строку из каждого набора входных данных и сравнивает их. Because each input is sorted, the Merge Join operator gets a row from each input and compares them. Например, для операций внутреннего соединения строки возвращаются в том случае, если они равны. For example, for inner join operations, the rows are returned if they are equal. Если они не равны, строка с меньшим значением не учитывается, и из этого набора входных данных берется другая строка. If they are not equal, the lower-value row is discarded and another row is obtained from that input. Этот процесс повторяется, пока не будет выполнена обработка всех строк. This process repeats until all rows have been processed.

    Операция соединения слиянием может быть как обычной, так и операцией типа «многие ко многим». The merge join operation may be either a regular or a many-to-many operation. Соединение слиянием «многие ко многим» использует временную таблицу для хранения строк. A many-to-many merge join uses a temporary table to store rows. При наличии дублирующих значений из каждого набора входных данных один из наборов должен будет сбрасываться на начало дубликатов по мере обработки каждого дубликата из другого набора данных. If there are duplicate values from each input, one of the inputs will have to rewind to the start of the duplicates as each duplicate from the other input is processed.

    При наличии остаточного предиката все строки, удовлетворяющие предикату слияния, определяют остаточный предикат, и возвращаются только те строки, которые ему соответствуют. If a residual predicate is present, all rows that satisfy the merge predicate evaluate the residual predicate, and only those rows that satisfy it are returned.

    Соединение слиянием — очень быстрая операция, но она может оказаться ресурсоемкой, если требуется выполнение операций сортировки. Merge join itself is very fast, but it can be an expensive choice if sort operations are required. Однако если том данных имеет большой объем, и необходимые данные могут быть получены из существующих индексов сбалансированного дерева с выполненной предварительной сортировкой, соединение слиянием является самым быстрым из доступных алгоритмов соединения. However, if the data volume is large and the desired data can be obtained presorted from existing B-tree indexes, merge join is often the fastest available join algorithm.

    Основные сведения о хэш-соединениях Understanding Hash joins

    Хэш-соединения могут эффективно обрабатывать большие, несортированные и неиндексированные входы. Hash joins can efficiently process large, unsorted, nonindexed inputs. Они полезны для получения промежуточных результатов в сложных запросах из-за следующего. They are useful for intermediate results in complex queries because:

    • Промежуточные результаты не индексированы (если только они явным образом не сохранены на диске, а затем проиндексированы) и часто отсортированы не так, как требуется для следующей операции в плане запроса. Intermediate results are not indexed (unless explicitly saved to disk and then indexed) and often are not suitably sorted for the next operation in the query plan.
    • Оптимизаторы запросов оценивают только размеры промежуточных результатов. Query optimizers estimate only intermediate result sizes. Так как для сложных запросов оценки могут быть очень неточны, алгоритмы обработки промежуточных результатов должны быть не только эффективными, но и правильно вырождаться, если объем промежуточных результатов оказался гораздо большим, чем ожидалось. Because estimates can be very inaccurate for complex queries, algorithms to process intermediate results not only must be efficient, but also must degrade gracefully if an intermediate result turns out to be much larger than anticipated.

    Хэш-соединение позволяет уменьшить денормализацию. The hash join allows reductions in the use of denormalization. Денормализация обычно используется для получения более высокой производительности при уменьшении количества операций соединения, несмотря на издержки, вызываемые избыточностью данных, например несогласованных обновлений. Denormalization is typically used to achieve better performance by reducing join operations, in spite of the dangers of redundancy, such as inconsistent updates. Хэш-соединения снижают потребность в денормализации Hash joins reduce the need to denormalize. и позволяют осуществлять вертикальное секционирование (представляющее группы столбцов, содержащиеся в одной таблице, в отдельных файлах или индексах) в качестве доступной возможности при реализации физической структуры базы данных. Hash joins allow vertical partitioning (representing groups of columns from a single table in separate files or indexes) to become a viable option for physical database design.

    Хэш-соединение имеет два входа: конструктивный и пробный. The hash join has two inputs: the build input and probe input. Оптимизатор запросов распределяет роли таким образом, при котором меньшему входу присваивается значение «конструктивный». The query optimizer assigns these roles so that the smaller of the two inputs is the build input.

    Хэш-соединения используются во многих операциях совпадающих множеств: внутреннее соединение; левое, правое и полное внешнее соединение; левое и правое полусоединение; пересечение; соединение; разность. Hash joins are used for many types of set-matching operations: inner join; left, right, and full outer join; left and right semi-join; intersection; union; and difference. Дополнительно модификация хэш-соединения применяется для двойного удаления и группирования, например SUM(salary) GROUP BY department . Moreover, a variant of the hash join can do duplicate removal and grouping, such as SUM(salary) GROUP BY department . В указанной модификации используется общий вход как для конструктивной, так и для пробной ролей. These modifications use only one input for both the build and probe roles.

    В представленных ниже разделах описываются различные типы хэш-соединений: хэш-соединения в памяти, поэтапные и рекурсивные хэш-соединения. The following sections describe different types of hash joins: in-memory hash join, grace hash join, and recursive hash join.

    Хэш-соединения в памяти In-Memory Hash Join

    Перед проведением хэш-соединения производится просмотр или вычисление входного конструктивного значения, а затем в памяти создается хэш-таблица. The hash join first scans or computes the entire build input and then builds a hash table in memory. Каждая строка помещается в сегмент хэша согласно значению, вычисленному для хэш-ключа. Each row is inserted into a hash bucket depending on the hash value computed for the hash key. В случае если конструктивное входное значение имеет размер, меньший объема доступной памяти, то все строки данных могут быть занесены в хэш-таблицу. If the entire build input is smaller than the available memory, all rows can be inserted into the hash table. После описанного конструктивного этапа предпринимается пробный этап. This build phase is followed by the probe phase. Производится построковое считывание или вычисление пробного входного значения, для каждой строки вычисляется значение хэш-ключа, затем происходит сканирование сегмента хэша и поиск совпадений. The entire probe input is scanned or computed one row at a time, and for each probe row, the hash key’s value is computed, the corresponding hash bucket is scanned, and the matches are produced.

    Плавное хэш-соединение Grace Hash Join

    Если размер конструктивного входного значения превышает максимально допустимый объем памяти, то хэш-соединение проводится в несколько шагов. If the build input does not fit in memory, a hash join proceeds in several steps. Указанный процесс называется плавным хэш-соединением. This is known as a grace hash join. Каждый шаг состоит из конструктивной и пробной частей. Each step has a build phase and probe phase. Исходные конструктивные и пробные входные данные разбиваются на несколько файлов (для этого используются хэш-функции ключей). Initially, the entire build and probe inputs are consumed and partitioned (using a hash function on the hash keys) into multiple files. При использовании хэш-функции для хэш-ключей обеспечивается гарантия нахождения соединяемых записей в общей паре файлов. Using the hash function on the hash keys guarantees that any two joining records must be in the same pair of files. Таким образом, задача соединения двух объемных входных значений разбивается на несколько более мелких задач. Therefore, the task of joining two large inputs has been reduced to multiple, but smaller, instances of the same tasks. Затем хэш-соединение применяется к каждой паре разделенных файлов. The hash join is then applied to each pair of partitioned files.

    Рекурсивное хэш-соединение Recursive Hash Join

    Если объем информации, поступающей на конструктивный вход, настолько велик, что для использования обычного внешнего слияния требуется несколько уровней, то операцию разбиения необходимо проводить за несколько шагов на нескольких уровнях. If the build input is so large that inputs for a standard external merge would require multiple merge levels, multiple partitioning steps and multiple partitioning levels are required. Дополнительные шаги разбиения используются только для секций большого объема. If only some of the partitions are large, additional partitioning steps are used for only those specific partitions. Чтобы максимально ускорить проведение всех шагов разбиения, используются емкие асинхронные операции ввода-вывода, в результате чего один поток может занимать сразу несколько жестких дисков. In order to make all partitioning steps as fast as possible, large, asynchronous I/O operations are used so that a single thread can keep multiple disk drives busy.

    В случае незначительного превышения допустимого объема памяти конструктивными входными данными происходит совмещение элементов хэш-соединения в памяти и поэтапных хэш-соединений в общий этап. В результате получается гибридное хэш-соединение. If the build input is only slightly larger than the available memory, elements of in-memory hash join and grace hash join are combined in a single step, producing a hybrid hash join.

    В процессе оптимизации не всегда удается определить тип используемого хэш-соединения. It is not always possible during optimization to determine which hash join is used. Поэтому в SQL Server в первую очередь используются хэш-соединения в памяти, а затем, в зависимости от объемов входной конструктивной информации, осуществляется переход на поэтапное или рекурсивное хэш-соединение. Therefore, SQL Server starts by using an in-memory hash join and gradually transitions to grace hash join, and recursive hash join, depending on the size of the build input.

    В случае неверного определения конструктивного и пробного входов в оптимизаторе запросов их переключение осуществляется динамически. If the Query Optimizer anticipates wrongly which of the two inputs is smaller and, therefore, should have been the build input, the build and probe roles are reversed dynamically. При использовании хэш-соединения осуществляется контроль использования меньшего файла в качестве конструктивного входа. The hash join makes sure that it uses the smaller overflow file as build input. Данная функция называется «переключением ролей». This technique is called role reversal. Переключение ролей происходит внутри хэш-соединения после сброса информации на диск. Role reversal occurs inside the hash join after at least one spill to the disk.

    Переключение ролей происходит независимо от указаний запроса или структуры запроса. Role reversal occurs independent of any query hints or structure. Событие «переключение ролей» не отображается в плане запроса, и сообщение о нем выдается пользователю непосредственно после выполнения. Role reversal does not display in your query plan; when it occurs, it is transparent to the user.

    Аварийная остановка хэша Hash Bailout


    Термин «аварийная остановка хэша» иногда используется для описания поэтапных и рекурсивных хэш-соединений. The term hash bailout is sometimes used to describe grace hash joins or recursive hash joins.

    Наличие рекурсивных хэш-соединений и аварийных остановок снижает производительность сервера. Recursive hash joins or hash bailouts cause reduced performance in your server. Если в трассировке содержится много «событий-предупреждений хэша», необходимо произвести обновление статистических данных соединяемых столбцов. If you see many Hash Warning events in a trace, update statistics on the columns that are being joined.

    Дополнительные сведения об аварийных остановках хэша см. в разделе Класс событий Hash Warning. For more information about hash bailout, see Hash Warning Event Class.

    Основные сведения об адаптивных соединениях Understanding Adaptive joins

    Адаптивные соединения в пакетном режиме позволяют отложить выбор метода Хэш-соединение или Соединение вложенными циклами до завершения сканирования первых входных данных. Batch mode Adaptive Joins enable the choice of a Hash Join or Nested Loops join method to be deferred until after the first input has been scanned. Оператор адаптивного соединения определяет пороговое значение, по которому принимается решение о переключении на план вложенного цикла. The Adaptive Join operator defines a threshold that is used to decide when to switch to a Nested Loops plan. Таким образом, во время выполнения план запроса может динамически переключаться на более эффективную стратегию соединения без перекомпиляции. A query plan can therefore dynamically switch to a better join strategy during execution without having to be recompiled.

    Наиболее полезной эта функция будет для рабочих нагрузок с частыми переключениями между просмотрами входных данных мелких и крупных соединений. Workloads with frequent oscillations between small and large join input scans will benefit most from this feature.

    Решение для среды выполнения зависит от следующего: The runtime decision is based on the following steps:

    • Если число строк во входных данных соединения сборки настолько мало, что соединение вложенными циклами будет эффективнее хэш-соединения, план переключается на алгоритм вложенных циклов. If the row count of the build join input is small enough that a Nested Loops join would be more optimal than a Hash join, the plan switches to a Nested Loops algorithm.
    • Если число строк во входных данных соединения сборки превышает порог, переключение не выполняется и план продолжает использовать хэш-соединение. If the build join input exceeds a specific row count threshold, no switch occurs and your plan continues with a Hash join.

    Следующий запрос используется в качестве наглядного примера адаптивного соединения: The following query is used to illustrate an Adaptive Join example:

    Этот запрос возвращает 336 строк. The query returns 336 rows. Если включить функцию Статистика активных запросов, отобразится следующий план: Enabling Live Query Statistics displays the following plan:

    Обратите внимание на следующие моменты в плане: In the plan, note the following:

    1. Просмотр индекса columnstore, используемый с целью предоставления строк для этапа сборки хэш-соединения. A columnstore index scan used to provide rows for the Hash join build phase.
    2. Новый оператор адаптивного соединения. The new Adaptive Join operator. Он определяет пороговое значение, по которому принимается решение о переключении на план вложенного цикла. This operator defines a threshold that is used to decide when to switch to a Nested Loops plan. В этом примере порог равен 78 строкам. For this example, the threshold is 78 rows. Если число строк >= 78, будет использоваться хэш-соединение. Anything with >= 78 rows will use a Hash join. Если значение меньше порога, будет использоваться соединение вложенными циклами. If less than the threshold, a Nested Loops join will be used.
    3. Так как запрос возвращает 336 строк, порог превышен. Поэтому вторая ветвь представляет пробный этап стандартной операции хэш-соединения. Since the query returns 336 rows, this exceeded the threshold and so the second branch represents the probe phase of a standard Hash join operation. Обратите внимание, что статистика активных запросов показывает строки, передаваемые через операторы — в данном случае это «672 из 672». Notice that Live Query Statistics shows rows flowing through the operators — in this case «672 of 672».
    4. И последняя ветвь — поиск кластеризованного индекса, используемый соединением вложенными циклами, если порог не был превышен. And the last branch is a Clustered Index Seek for use by the Nested Loops join had the threshold not been exceeded. Обратите внимание, что мы видим число строк «0 из 336» (ветвь не используется). Notice that we see «0 of 336» rows displayed (the branch is unused).

    Теперь давайте сравним план с таким же запросом, но для случая, когда значение Quantity будет иметь всего одну строку в таблице: Now contrast the plan with the same query, but when the Quantity value only has one row in the table:

    Запрос возвращает одну строку. The query returns one row. Если включить функцию «Статистика активных запросов», отобразится следующий план: Enabling Live Query Statistics displays the following plan:

    Обратите внимание на следующие моменты в плане: In the plan, note the following:

    • При возврате одной строки видно, что теперь через поиск кластеризованного индекса передаются строки. With one row returned, the Clustered Index Seek now has rows flowing through it.
    • А так как этап сборки хэш-соединения не продолжается, никакие строки через вторую ветвь не передаются. And since the Hash Join build phase did not continue, there are no rows flowing through the second branch.

    Примечания к адаптивным соединениям Adaptive Join remarks

    Адаптивные соединения предъявляют более высокие требования к памяти, чем эквивалентный план соединения вложенными циклами индекса. Adaptive joins introduce a higher memory requirement than an indexed Nested Loops Join equivalent plan. Дополнительная память запрашивается так, как если бы вложенный цикл был хэш-соединением. The additional memory is requested as if the Nested Loops was a Hash join. Существуют также издержки на этапе сборки, такие как стартстопная операция и эквивалентное потоковое соединение вложенными циклами. There is also overhead for the build phase as a stop-and-go operation versus a Nested Loops streaming equivalent join. Эти дополнительные затраты обеспечивают гибкость для сценариев, где количество строк во входных данных сборки может меняться. With that additional cost comes flexibility for scenarios where row counts may fluctuate in the build input.

    Адаптивные соединения в пакетном режиме используются для первого выполнения инструкции. После компиляции последовательные выполнения остаются адаптивными с учетом порога скомпилированных адаптивных соединений и строк времени выполнения, передаваемых через этап сборки внешних входных данных. Batch mode Adaptive joins work for the initial execution of a statement, and once compiled, consecutive executions will remain adaptive based on the compiled Adaptive Join threshold and the runtime rows flowing through the build phase of the outer input.

    Если адаптивное соединение переключается на режим вложенного цикла, оно использует строки, уже считанные сборкой хэш-соединения. If an Adaptive Join switches to a Nested Loops operation, it uses the rows already read by the Hash Join build. Этот оператор не считывает повторно строки по внешней ссылке. The operator does not re-read the outer reference rows again.

    Отслеживание операций адаптивного соединения Tracking Adaptive join activity

    Оператор адаптивного соединения имеет следующие атрибуты оператора плана: The Adaptive Join operator has the following plan operator attributes:

    Атрибут плана Plan attribute Описание Description
    AdaptiveThresholdRows AdaptiveThresholdRows Показывает пороговое значение, используемое для переключения с хэш-соединения на соединение вложенными циклами. Shows the threshold use to switch from a hash join to nested loop join.
    EstimatedJoinType EstimatedJoinType К какому типу, вероятнее всего, относится соединение. What the join type is likely to be.
    ActualJoinType ActualJoinType В фактическом плане показывает, какой итоговый алгоритм соединения был выбран на базе порогового значения. In an actual plan, shows what join algorithm was ultimately chosen based on the threshold.

    Предполагаемый план показывает форму плана адаптивного соединения, а также определенное пороговое значение адаптивного соединения и предполагаемый тип соединения. The estimated plan shows the Adaptive Join plan shape, along with a defined Adaptive Join threshold and estimated join type.

    Хранилище запросов захватывает и может принудительно применить план адаптивного соединения в пакетном режиме. Query Store captures and is able to force a batch mode Adaptive Join plan.

    Допустимые инструкции адаптивного соединения Adaptive join eligible statements

    Чтобы логическое соединение стало допустимым для адаптивного соединения в пакетном режиме, должны выполняться следующие условия: A few conditions make a logical join eligible for a batch mode Adaptive Join:

    • Уровень совместимости базы данных имеет значение 140 или больше. The database compatibility level is 140 or higher.
    • Запрос является инструкцией SELECT (инструкции для изменения данных сейчас недопустимы). The query is a SELECT statement (data modification statements are currently ineligible).
    • Соединение может выполняться посредством как индексированного соединения вложенными циклами, так и физического алгоритма хэш-соединения. The join is eligible to be executed both by an indexed Nested Loops join or a Hash join physical algorithm.
    • Хэш-соединение использует пакетный режим — либо из-за наличия индекса columnstore во всем запросе, либо по той причине, что на таблицу индекса columnstore ссылается само соединение. The Hash join uses Batch mode — either through the presence of a Columnstore index in the query overall or a Columnstore indexed table being referenced directly by the join.
    • Созданные альтернативные решения соединения вложенными циклами и хэш-соединения должны иметь одинаковый первый дочерний элемент (внешняя ссылка). The generated alternative solutions of the Nested Loops join and Hash join should have the same first child (outer reference).

    Строки адаптивного порогового значения Adaptive threshold rows

    На приведенной ниже диаграмме показан пример пересечения между показателем затрат хэш-соединения и таким показателем альтернативного ему соединения вложенными циклами. The following chart shows an example intersection between the cost of a Hash join versus the cost of a Nested Loops join alternative. В этой точке пересечения определяется пороговое значение, что, в свою очередь, определяет фактический алгоритм, используемый для операции соединения. At this intersection point, the threshold is determined that in turn determines the actual algorithm used for the join operation.

    Отключение адаптивных соединений без изменения уровня совместимости Disabling Adaptive joins without changing the compatibility level

    Адаптивные соединения можно отключить в области базы данных или инструкции, сохранив уровень совместимости базы данных 140 или более высокий. Adaptive joins can be disabled at the database or statement scope while still maintaining database compatibility level 140 and higher.
    Чтобы отключить адаптивные соединения для всех запросов, поступающих из базы данных, выполните следующую команду в контексте соответствующей базы данных: To disable Adaptive joins for all query executions originating from the database, execute the following within the context of the applicable database:

    Когда этот параметр включен, он будет иметь соответствующее состояние в представлении sys.database_scoped_configurations. When enabled, this setting will appear as enabled in sys.database_scoped_configurations. Чтобы снова включить адаптивные соединения для всех запросов, выполняемых из базы данных, выполните следующую команду в контексте соответствующей базы данных: To re-enable adaptive joins for all query executions originating from the database, execute the following within the context of the applicable database:

    Вы также можете отключить адаптивные соединения для определенного запроса, назначив DISABLE_BATCH_MODE_ADAPTIVE_JOINS в качестве указания запроса USE HINT. Adaptive joins can also be disabled for a specific query by designating DISABLE_BATCH_MODE_ADAPTIVE_JOINS as a USE HINT query hint. Пример: For example:

    Указание запроса USE HINT имеет приоритет над конфигурацией, областью действия которой является база данных, или флагом трассировки. A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.

    Значения NULL и соединения Null Values and Joins

    Если в столбцах, по которым производится соединение таблиц, есть значение NULL, значения NULL друг с другом совпадать не будут. When there are null values in the columns of the tables being joined, the null values do not match each other. Наличие таких значений в столбце одной из соединяемых таблиц возможно только при использовании внешнего соединения (если только предложение WHERE не исключает значение NULL). The presence of null values in a column from one of the tables being joined can be returned only by using an outer join (unless the WHERE clause excludes null values).

    Ниже приведены две таблицы, каждая из которых может содержать NULL в столбце, по которому проводится соединение: Here are two tables that each have NULL in the column that will participate in the join:


    Соединение, сравнивающее значения в столбце a со значениями столбца c, не создает совпадений, если столбцы имеют значение NULL: A join that compares the values in column a against column c does not get a match on the columns that have values of NULL:

    Возвращена только одна строка со значением 4 в столбцах a и c: Only one row with 4 in column a and c is returned:

    Значения NULL, возвращаемые из базовой таблицы, также сложно отличить от значений NULL, возвращаемых при внешнем соединении. Null values returned from a base table are also difficult to distinguish from the null values returned from an outer join. Например, следующая инструкция SELECT выполняет левое внешнее соединение этих двух таблиц. For example, the following SELECT statement does a left outer join on these two tables:

    Ниже приводится результирующий набор. Here is the result set.

    В результате сложно определить, какие значения NULL получены из данных, а какие означают неуспешное соединение. The results do not make it easy to distinguish a NULL in the data from a NULL that represents a failure to join. Если в соединениях данных присутствуют значения NULL, чаще всего желательно исключить их из результатов с помощью обычного соединения. When null values are present in data being joined, it is usually preferable to omit them from the results by using a regular join.

    Операция INNER JOIN

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

    Синтаксис

    FROM таблица1 INNER JOIN таблица2 ON таблица1. поле1 оператор_сравнения таблица2. поле2

    Операция INNER JOIN состоит из следующих элементов:

    Имена таблиц, содержащих объединяемые записи.

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

    Любой оператор сравнения: (=, , = или <>)

    Замечания

    Операцию INNER JOIN можно использовать в любом предложении FROM. Это самый распространенный тип объединения. С его помощью происходит объединение записей из двух таблиц по связующему полю, если оно содержит одинаковые значения в обеих таблицах.

    При работе с таблицами «Отделы» и «Сотрудники» операцией INNER JOIN можно воспользоваться для выбора всех сотрудников в каждом отделе. Если же требуется выбрать все отделы (включая те из них, в которых нет сотрудников) или всех сотрудников (в том числе и не закрепленных за отделом), можно при помощи операции LEFT JOIN или RIGHT JOIN создать внешнее соединение.

    При попытке связи полей, содержащих данные типа Memo или объекты OLE, возникнет ошибка.

    Можно связать любые два числовых поля аналогичных типов. Например, можно связать поля AutoNumber и Long, так как эти типы аналогичны, однако не поля Single и Double.

    В следующем примере показано, как можно объединить таблицы Categories и Products по полю CategoryID.

    В предыдущем примере Category >Categories.CategoryID .

    В инструкции JOIN можно также связать несколько предложений ON, используя следующий синтаксис:

    SELECT поля
    FROM таблица1 INNER JOIN таблица2
    ON таблица1. таблица1 оператор_сравнения таблица2. поле1 AND
    ON таблица1. поле2 оператор_сравнения таблица2. поле2) OR
    ON таблица1. поле3 оператор_сравнения таблица2. поле3)];

    Ниже приведен пример синтаксиса, с помощью которого можно составлять вложенные инструкции JOIN.

    SELECT поля
    FROM таблица1 INNER JOIN
    ( таблица2 INNER JOIN [( ] таблица3
    [INNER JOIN [( ] таблицаx [INNER JOIN . )]
    ON таблица3. поле3 оператор_сравнения таблицаx. полеx)]
    ON таблица2. поле2 оператор_сравнения та блица3. поле3)
    ON таблица1. поле1 оператор_сравнения таблица2. поле2;

    Операции LEFT JOIN и RIGHT JOIN могут быть вложены в операцию INNER JOIN, но операция INNER JOIN не может быть вложена в операцию LEFT JOIN или RIGHT JOIN.

    Поддерживаемые Oracle типы соединений в SQL: JOIN и другие

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

    Эквисоединение

    При эквисоединении (equi-join) две или более таблиц соединяются на основании условия равенства между столбцами. Другими словами, один и тот же столбец имеет одинаковое значение во всех соединяемых таблицах. Ниже приведен пример применения эквисоединения:

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

    При желании соединить несколько столбцов, можно перечислить их имена в виде разделенного запятыми списка, например: USING ( dept_id , emp_name ).

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

    Естественным соединением (natural join) называется эквисоединение, при котором столбцы, которые должны сопоставляться для выполнения соединения, специально не указываются. Oracle автоматически определяет подлежащие соединению столбцы на основании совпадающих столбцов в двух таблицах. Ниже приведен пример применения естественного соединения:

    В этом примере условием для выполнения соединения служит наличие идентичных значений в столбце last_name в таблицах emp и dept .

    Рефлексивное соединение

    Под рефлексивным соединением (self join) подразумевается соединение таблицы с самой собой за счет использования псевдонимов. В следующем примере осуществляется соединение таблицы employees с самой собой при помощи псевдонима с удалением всех дублированных строк.

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

    Внутреннее соединение (inner join), также называемое простым соединением (simple join), предусматривает возврат всех строк, которые удовлетворяют указанному условию соединения. Раньше в синтаксисе внутреннего соединения для указания того, каким образом должны соединяться таблицы, нужно было использовать конструкцию WHERE , например, так:

    Теперь Oracle позволяет задавать критерии соединения в синтаксисе внутреннего (или простого) соединения за счет применения новой конструкции ON или USING , например:

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

    Внешнее соединение (outer join) применяется для возврата всех строк, которые удовлетворяют указанному условию соединения, плюс некоторых или всех строк из таблицы, в которой нет подходящих строк, удовлетворяющих указанному условию соединения. Существуют три вида внешнего соединения: левое внешнее соединение (left outer join), правое внешнее соединение (right outer join) и полное внешнее соединение (full outer join). В операторе полного внешнего соединения слово OUTER обычно опускается.

    Oracle позволяет использовать операцию внешнего соединения, подразумевающую применение знака плюс (+) для обозначения недостающих значений в одной таблице, но рекомендует лучше использовать вместо нее более новый синтаксис соединения ISO/ANSI. Ниже приведен пример типичного запроса с оператором полного внешнего соединения:

    Понравилась статья? Поделиться с друзьями:
    Кодинг, CSS и SQL