Как соединить несколько таблиц в sql

Использование в запросе нескольких источников записей

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

A		B
a	b	c	d
1	2	2	4
2	1	3	3

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

a	b	c	d
1	2	2	4
1	2	3	3
2	1	2	4
2	1	3	3

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

Теперь результатом выполнения этого запроса будет следующая таблица:

a	b	c	d
2	1	2	4

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

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

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

Пример. Найти номер модели и производителя ПК, имеющих цену менее $600:

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

model	maker
1232	A
1260	E

Иногда в предложении FROM требуется указать одну и ту же таблицу несколько раз. В этом случае обязательным является переименование.

Пример. Вывести пары моделей, имеющих одинаковые цены:

model_1	model_2
1232	1233
1232	1260

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

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

Явные операции соединения

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

Соединение может быть либо внутренним (INNER), либо одним из внешних (OUTER). Служебные слова INNER и OUTER можно опускать, поскольку внешнее соединение однозначно определяется его типом — LEFT (левое), RIGHT (правое) или FULL (полное), а просто JOIN будет означать внутреннее соединение.

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

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

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

maker	model_1	model_2	price
A	1232	1232	600.0
A	1232	1232	400.0
A	1232	1232	350.0
A	1232	1232	350.0
A	1233	1233	600.0
A	1233	1233	950.0
A	1233	1233	980.0
B	1121	1121	850.0
B	1121	1121	850.0
B	1121	1121	850.0
E	1260	1260	350.0

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

Пример. Привести все модели ПК, их производителей и цену:

Обратите внимание на то, что по сравнению с предыдущим примером, пришлось использовать предложение WHERE для отбора только производителей ПК. В противном случае в результирующий набор попали бы также и модели ПК-блокнотов и принтеров. В рассмотренном ранее примере это условие было бы излишним, т.к. соединялись только те строки, у которых совпадали номера моделей, и одной из таблиц была таблица PC, содержащая только ПК. В результате выполнения запроса получим:

maker	model_1	model_2	price
A	1232	1232	600.0
A	1232	1232	400.0
A	1232	1232	350.0
A	1232	1232	350.0
A	1233	1233	600.0
A	1233	1233	950.0
A	1233	1233	980.0
B	1121	1121	850.0
B	1121	1121	850.0
B	1121	1121	850.0
E	2111	NULL	NULL
E	2112	NULL	NULL
E	1260	1260	350.0

Поскольку моделей 2111 и 2112 из таблицы Product нет в таблице PC, в полях из таблицы PC содержится NULL.
Соединение RIGHT JOIN обратно соединению LEFT JOIN, т.е. в результирующий набор попадут все строки из второй таблицы, которые будут соединяться только с теми строками из первой таблицы, для которых выполняется условие соединения. В нашем случае левое соединение

Product LEFT JOIN PC ON PC.model = Product.model

будет эквивалентно правому соединению

PC RIGHT JOIN Product ON PC.model = Product.model

даст те же результаты, что и внутреннее соединение, поскольку в правой таблице (PC) нет таких моделей, которые отсутствовали бы в левой таблице (Product), что вполне естественно для типа связи «один-ко-многим», которая имеется между таблицами PC и Product. Наконец, при полном соединении (FULL JOIN) в результирующую таблицу попадут не только те строки, которые имеют одинаковые значения в сопоставляемых столбцах, но и все остальные строки исходных таблиц, не имеющие соответствующих значений в другой таблице. В этих строках все столбцы той таблицы, в которой не было найдено соответствия, заполняются NULL-значениями. Полное соединение представляет собой комбинацию левого и правого внешних соединений.
Так запрос для таблиц A и B, приведенных в начале главы,

даст следующий результат:

a	b	c	d
1	2	NULL	NULL
2	1	2	4
NULL	NULL	3	3

Заметим, что это соединение симметрично, т.е. «A FULL JOIN B» эквивалентно «B FULL JOIN A». Обратите также внимание на обозначение A.*, что означает «все поля таблицы А».

Несколько условий соединения

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

Например, получим информацию о должностях произвольных 5-ти сотрудников:

SELECT e.first_name, e.last_name, r.rank_id, r.store_id, r.name as rank_name FROM employee e JOIN rank r ON r.rank_id = e.rank_id AND r.store_id = e.store_id LIMIT 5 

first_name	last_name	rank_id	store_id	rank_name
Алексей	Иванов	CHIEF	100	Директор
Любовь	Блинова	SELLER	100	Продавец
Глеб	Тарасов	MANAGER	100	Менеджер
Петр	Корсаков	CEO	201	Директор
Никки	Зайцева	MANAGER	201	Менеджер

Обрати внимание на условие соединения таблиц employee e и rank r

 ON r.rank_id = e.rank_id AND r.store_id = e.store_id 

Почему мы использовали два столбца? Ответ на вопрос можно найти в описании таблицы rank :

В каждом магазине своя иерархия сотрудников. . Запись в rank идентифицируется составным суррогатным ключом (store_id, rank_id).

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

В условии соединения могут использоваться и другие логические операторы:

• AND — логическое И;
• OR — логическое ИЛИ;
• NOT — отрицание.

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

SQL Server выполняет операции сортировки, взаимодействия, объединения и разности с помощью технологии сортировки и хэш-соединения в памяти. С помощью этого типа плана запросов SQL Server поддерживает секционирование вертикальных таблиц.

SQL Server реализует операции логического соединения, как определено синтаксисом Transact-SQL:

• внутреннее соединение,
• левое внешнее соединение.
• Правое внешнее соединение
• Полное внешнее соединение
• Перекрестное соединение

Дополнительные сведения о синтаксисе соединения см. в разделе Предложение FROM и JOIN, APPLY, PIVOT (Transact-SQL).

SQL Server использует четыре типа операций физического соединения для выполнения операций логического соединения:

• Соединения вложенных циклов
• Соединения слиянием.
• Хэш-соединения.
• Адаптивные соединения (начиная с SQL Server 2017 (14.x))

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

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

Соединение определяет способ связывания двух таблиц в запросе следующим образом:

• для каждой таблицы указываются столбцы, используемые в соединении. В типичном условии соединения указывается внешний ключ из одной таблицы и связанный с ним ключ из другой таблицы;
• Указывается логический оператор (например, = или <>), используемый для сравнения значений из столбцов.

Соединения выражаются логически с помощью следующего синтаксиса Transact-SQL:

• ВНУТРЕННЕЕ СОЕДИНЕНИЕ
• LEFT [ OUTER ] JOIN
• RIGHT [ OUTER ] JOIN
• FULL [ OUTER ] JOIN
• CROSS JOIN

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

То, что условия соединения задаются в предложении FROM , помогает отделить их от условий поиска, которые могут быть заданы в предложении WHERE . Объединение рекомендуется задавать именно таким способом. Ниже приведен упрощенный синтаксис соединения с использованием предложения FROM стандарта ISO:

FROM first_table < join_type >second_table [ ON ( join_condition ) ] 

Join_type указывает, какой тип соединения выполняется: внутреннее, внешнее или перекрестное соединение. join_condition определяет предикат, который будет вычисляться для каждой пары соединяемых строк. Ниже приведен пример предложения FROM с заданным соединением:

FROM Purchasing.ProductVendor INNER JOIN Purchasing.Vendor ON ( ProductVendor.BusinessEntityID = Vendor.BusinessEntityID ) 

Ниже приведена простая инструкция SELECT , использующая это соединение:

SELECT ProductID, Purchasing.Vendor.BusinessEntityID, Name FROM Purchasing.ProductVendor INNER JOIN Purchasing.Vendor ON (Purchasing.ProductVendor.BusinessEntityID = Purchasing.Vendor.BusinessEntityID) WHERE StandardPrice > $10 AND Name LIKE N'F%'; GO 

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

Если один запрос содержит ссылки на несколько таблиц, то все ссылки столбцов должны быть однозначными. В предыдущем примере как таблица ProductVendor , так и таблица Vendor содержат столбец с именем BusinessEntityID . Имена столбцов, совпадающие в двух или более таблицах, на которые ссылается запрос, должны уточняться именем таблицы. Все ссылки на столбец Vendor в этом примере являются полными.

Если имя столбца не дублируется в двух или более таблицах, указанных в запросе, то ссылки на него уточнять именем таблицы не обязательно. Это показано в предыдущем примере. Подобное предложение SELECT иногда трудно понять, поскольку в нем нет ничего, что указывало бы на таблицы, из которых берутся столбцы. Запрос гораздо легче читать, если все столбцы указаны с именами соответствующих таблиц. Запрос будет читаться еще легче, если используются псевдонимы таблиц, особенно когда имена таблиц сами должны уточняться именами базы данных и владельца. Ниже приведен тот же пример, но чтобы упростить чтение, используются псевдонимы таблиц, уточняющие названия столбцов.

SELECT pv.ProductID, v.BusinessEntityID, v.Name FROM Purchasing.ProductVendor AS pv INNER JOIN Purchasing.Vendor AS v ON (pv.BusinessEntityID = v.BusinessEntityID) WHERE StandardPrice > $10 AND Name LIKE N'F%'; 

В предыдущем примере условие соединения задается в предложении FROM , что является рекомендуемым способом. В следующем запросе это же условие соединения указывается в предложении WHERE :

SELECT pv.ProductID, v.BusinessEntityID, v.Name FROM Purchasing.ProductVendor AS pv, Purchasing.Vendor AS v WHERE pv.BusinessEntityID=v.BusinessEntityID AND StandardPrice > $10 AND Name LIKE N'F%'; 

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

Хотя обычно в условиях соединения для сравнения используется оператор равенства (=), можно указать другие операторы сравнения или реляционные операторы, равно как другие предикаты. Дополнительные сведения см. в статьях «Операторы сравнения» (Transact-SQL) и WHERE (Transact-SQL).

При присоединении к SQL Server оптимизатор запросов выбирает наиболее эффективный метод (из нескольких возможностей) обработки соединения. Это включает выбор наиболее эффективного типа физического соединения, порядок объединения таблиц и даже использование типов операций логического соединения, которые нельзя выразить напрямую с помощью синтаксиса Transact-SQL, например полусоединения и анти-полусоединения. При физическом выполнении различных соединений можно использовать много разных оптимизаций, поэтому их нельзя надежно прогнозировать. Дополнительные сведения о полусоединениях и антиполусоединениях см. в справочнике по логическим и физическим операторам Showplan.

Столбцы, используемые в условии соединения, не обязательно должны иметь одинаковые имена или одинаковый тип данных. Однако если типы данных не совпадают, то они должны быть совместимыми или допускать в SQL Server неявное преобразование. Если типы данных не допускают неявное преобразование, то условия соединения должны явно преобразовывать эти типы данных с помощью функции CAST . Дополнительные сведения о неявных и явных преобразованиях см. в разделе «Преобразование типов данных» (ядро СУБД).

Большинство запросов, использующих соединение, можно переписать с помощью вложенных запросов и наоборот. Дополнительные сведения о вложенных запросах см. в разделе Вложенные запросы.

Таблицы невозможно соединять непосредственно по столбцам ntext, text или image. Однако соединить таблицы по столбцам ntext, text или image можно косвенно, с помощью SUBSTRING . Например, SELECT * FROM t1 JOIN t2 ON SUBSTRING(t1.textcolumn, 1, 20) = SUBSTRING(t2.textcolumn, 1, 20) выполняет двух табличное внутреннее соединение на первых 20 символов каждого текстового столбца в таблицах t1 и t2 .
Другая возможность сравнения столбцов ntext и text из двух таблиц заключается в сравнении длины столбцов с предложением WHERE , например: WHERE DATALENGTH(p1.pr_info) = DATALENGTH(p2.pr_info)

Общие сведения о соединениях вложенных циклов

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

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

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

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

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

Соединения слиянием.

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

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

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

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

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

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

Хэш-соединения.

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

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

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

Хэш-соединение имеет два входа: конструктивный и пробный. Оптимизатор запросов распределяет роли таким образом, при котором меньшему входу присваивается значение «конструктивный».

Хэш-соединения используются во многих операциях совпадающих множеств: внутреннее соединение; левое, правое и полное внешнее соединение; левое и правое полусоединение; пересечение; соединение; разность. Дополнительно модификация хэш-соединения применяется для двойного удаления и группирования, например SUM(salary) GROUP BY department . В указанной модификации используется общий вход как для конструктивной, так и для пробной ролей.

В представленных ниже разделах описываются различные типы хэш-соединений: хэш-соединения в памяти, поэтапные и рекурсивные хэш-соединения.

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

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

Хэш-соединение грейс

Если размер конструктивного входного значения превышает максимально допустимый объем памяти, то хэш-соединение проводится в несколько шагов. Указанный процесс называется плавным хэш-соединением. Каждый шаг состоит из конструктивной и пробной частей. Исходные конструктивные и пробные входные данные разбиваются на несколько файлов (для этого используются хэш-функции ключей). При использовании хэш-функции для хэш-ключей обеспечивается гарантия нахождения соединяемых записей в общей паре файлов. Таким образом, задача соединения двух объемных входных значений разбивается на несколько более мелких задач. Затем хэш-соединение применяется к каждой паре разделенных файлов.

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

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

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

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

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

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

Хэш-спасение

Термин «аварийная остановка хэша» иногда используется для описания поэтапных и рекурсивных хэш-соединений.

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

Дополнительные сведения об аварийных остановках хэша см. в разделе Класс событий Hash Warning.

Адаптивные соединения

Адаптивные соединения в пакетном режиме позволяют отложить выбор метода Хэш-соединение или Соединение вложенными цикламидо завершения сканирования первых входных данных. Оператор адаптивного соединения определяет пороговое значение, по которому принимается решение о переключении на план вложенного цикла. Таким образом, во время выполнения план запроса может динамически переключаться на более эффективную стратегию соединения без перекомпиляции.

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

Решение для среды выполнения зависит от следующего:

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

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

SELECT [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity] FROM [Fact].[Order] AS [fo] INNER JOIN [Dimension].[Stock Item] AS [si] ON [fo].[Stock Item Key] = [si].[Stock Item Key] WHERE [fo].[Quantity] = 360; 

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

Обратите внимание на следующие моменты в плане:

1. Просмотр индекса columnstore, используемый с целью предоставления строк для этапа сборки хэш-соединения.
2. Новый оператор адаптивного соединения. Он определяет пороговое значение, по которому принимается решение о переключении на план вложенного цикла. В этом примере порог равен 78 строкам. Если число строк >= 78, будет использоваться хэш-соединение. Если значение меньше порога, будет использоваться соединение вложенными циклами.
3. Так как запрос возвращает 336 строк, это превысило пороговое значение, поэтому вторая ветвь представляет этап проверки стандартной операции соединения хэша. Обратите внимание, что статистика активных запросов показывает строки, передаваемые через операторы — в данном случае это «672 из 672».
4. И последняя ветвь — поиск кластеризованного индекса, используемый соединением вложенными циклами, если порог не был превышен. Обратите внимание, что мы видим число строк «0 из 336» (ветвь не используется).

Теперь контрастируйте план с тем же запросом, но если Quantity значение имеет только одну строку в таблице:

SELECT [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity] FROM [Fact].[Order] AS [fo] INNER JOIN [Dimension].[Stock Item] AS [si] ON [fo].[Stock Item Key] = [si].[Stock Item Key] WHERE [fo].[Quantity] = 361; 

Запрос возвращает одну строку. Если включить функцию «Статистика активных запросов», отобразится следующий план:

Обратите внимание на следующие моменты в плане:

• При возврате одной строки видно, что теперь через поиск кластеризованного индекса передаются строки.
• А так как этап сборки хэш-соединения не продолжается, никакие строки через вторую ветвь не передаются.

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

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

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

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

Отслеживание действия адаптивного соединения

Оператор адаптивного соединения имеет следующие атрибуты оператора плана:

Атрибут плана	Description
AdaptiveThresholdRows	Показывает пороговое значение, используемое для переключения с хэш-соединения на соединение вложенными циклами.
EstimatedJoinType	К какому типу, вероятнее всего, относится соединение.
ActualJoinType	В фактическом плане показывает, какой итоговый алгоритм соединения был выбран на базе порогового значения.

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

Хранилище запросов захватывает и может принудительно применить план адаптивного соединения в пакетном режиме.

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

Чтобы логическое соединение стало допустимым для адаптивного соединения в пакетном режиме, должны выполняться следующие условия:

• Уровень совместимости базы данных имеет значение 140 или больше.
• Запрос является инструкцией SELECT (инструкции для изменения данных сейчас недопустимы).
• Соединение может выполняться посредством как индексированного соединения вложенными циклами, так и физического алгоритма хэш-соединения.
• Хэш-соединение использует пакетный режим, включенный через наличие индекса columnstore в запросе в целом, индексированную таблицу columnstore, на которую ссылается непосредственно соединение, или с помощью режима пакетной службы в rowstore.
• Созданные альтернативные решения соединения вложенными циклами и хэш-соединения должны иметь одинаковый первый дочерний элемент (внешняя ссылка).

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

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

Отключение адаптивных соединений без изменения уровня совместимости

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

Чтобы отключить адаптивные соединения для всех запросов, поступающих из базы данных, выполните следующую команду в контексте соответствующей базы данных:

-- SQL Server 2017 ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = ON; -- Azure SQL Database, SQL Server 2019 and later versions ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ADAPTIVE_JOINS = OFF; 

Чтобы снова включить адаптивные соединения для всех запросов, выполняемых из базы данных, выполните следующую команду в контексте соответствующей базы данных:

-- SQL Server 2017 ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = OFF; -- Azure SQL Database, SQL Server 2019 and later versions ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ADAPTIVE_JOINS = ON; 

Вы также можете отключить адаптивные соединения для определенного запроса, назначив DISABLE_BATCH_MODE_ADAPTIVE_JOINS в качестве указания запроса USE HINT. Рассмотрим пример.

SELECT s.CustomerID, s.CustomerName, sc.CustomerCategoryName FROM Sales.Customers AS s LEFT OUTER JOIN Sales.CustomerCategories AS sc ON s.CustomerCategoryID = sc.CustomerCategoryID OPTION (USE HINT('DISABLE_BATCH_MODE_ADAPTIVE_JOINS')); 

Указание запроса USE HINT имеет приоритет над конфигурацией, областью действия которой является база данных, или флагом трассировки.

Значения NULL и соединения

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

Ниже приведены две таблицы, которые содержатся NULL в столбце, который будет участвовать в соединении:

table1 table2 a b c d ------- ------ ------- ------ 1 one NULL two NULL three 4 four 4 join4 

Соединение, которое сравнивает значения столбца с столбцом c , не получает совпадение по столбцам со значениями NULL :

SELECT * FROM table1 t1 JOIN table2 t2 ON t1.a = t2.c ORDER BY t1.a; GO 

Возвращается только одна строка со значением 4 в столбцах a : c

a b c d ----------- ------ ----------- ------ 4 join4 4 four (1 row(s) affected) 

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

SELECT * FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.a = t2.c ORDER BY t1.a; GO 

a b c d ----------- ------ ----------- ------ NULL three NULL NULL 1 one NULL NULL 4 join4 4 four (3 row(s) affected) 

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

Далее

• Справочник по логическим и физическим операторам Showplan
• Операторы сравнения (Transact-SQL)
• Преобразование типов данных (ядро СУБД)
• Вложенные запросы
• Адаптивное соединение
• Предложение FROM плюс JOIN, APPLY, PIVOT (Transact-SQL)

Глава 8. ЗАПРАШИВАНИЕ НЕСКОЛЬКИХ ТАБЛИЦ
ТАК ЖЕ, КАК ОДНОЙ

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

ОБЪЕДИНЕНИЕ ТАБЛИЦ

Одна из наиболее важных особенностей запросов SQL — их способность определять связи между многочисленными таблицами и выводить информацию из них, в терминах этих связей, всю внутри одной команды. Этот вид операции называется объединением, которое является одним из видов операций в реляционных базах данных. Как установлено в Главе 1, главное в реляционном подходе это связи, которые можно создавать между позициями данных в таблицах. Используя объединения, мы непосредственно связываем информацию с любым числом таблиц и таким образом способны создавать связи между сравнимыми фрагментами данных. При объединении, таблицы, представленные списком в предложении FROM, отделяются запятыми. Предикат запроса может ссылаться к любому столбцу любой связанной таблицы и, следовательно, может использоваться для связи между ими. Обычно предикат сравнивает значения в столбцах различных таблиц, чтобы определить, удовлетворяет ли WHERE установленному условию.

ИМЕНА ТАБЛИЦ И СТОЛБЦОВ

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

Salespeople.snum Salespeople.city Orders.odate

До этого вы могли опускать имена таблиц, потому что вы запрашивали единовременно только одну таблицу, а SQL достаточно интеллектуален, чтобы присвоить соответствующий префикс имени таблицы. Даже когда вы делаете запрос нескольких таблиц, вы ещё можете опускать имена таблиц, если все их столбцы имеют различные имена. Но так бывает не всегда. Например, мы имеем две типовые таблицы со столбцами, называемыми city. Если мы должны связать эти столбцы (кратковременно), мы должны будем указать их с именами Salespeople.city или Customers.city, чтобы SQL мог их различать.

СОЗДАНИЕ ОБЪЕДИНЕНИЯ

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

SELECT Customers.cname, Salespeople.sname, Salespeople.city FROM Salespeople, Customers WHERE Salespeople.city = Customers.city; =============== SQL Execution Log ============ | SELECT Customers.cname, Salespeople.sname, | | Salespeople.city | | FROM Salespeople, Customers | | WHERE Salespeople.city = Customers.city | | ============================================= | | cname cname city | | ------- -------- ---- | | Hoffman Peel London | | Hoffman Peel London | | Liu Serres San Jose | | Cisneros Serres San Jose | | Hoffman Motika London | | Clemens Motika London | ============================================= Рисунок 8.1 Объединение двух таблиц

Так как это city имеется и в таблице Продавцов, и таблице Заказчиков, имена таблиц должны использоваться как префиксы. Хотя это необходимо, только когда два или более полей имеют одно и то же имя, в любом случае это хорошая идея: включать имя таблицы в объединение для лучшего понимания и непротиворечивости. Несмотря на это, мы будем в наших примерах далее использовать имена таблиц только тогда, когда необходимо, так что будет ясно, когда они необходимы, а когда нет. Что SQL в основном делает в объединении, так это исследует каждую комбинацию строк двух или более возможных таблиц и проверяет эти комбинации по их предикатам. В предыдущем примере требовалась строка продавца Peel из таблицы Продавцов и объединение её с каждой строкой таблицы Пользователей, по одной в каждый момент времени. Если комбинация производит значение, которое делает предикат верным, и если поле city из строк таблиц Заказчика равно London, то Peel — это то запрашиваемое значение, которое комбинация выберет для вывода. То же самое будет затем выполнено для каждого продавца в таблице Продавцов (у некоторых из которых не было никаких заказчиков в этих городах).

ОБЪЕДИНЕНИЕ ТАБЛИЦ ЧЕРЕЗ СПРАВОЧНУЮ ЦЕЛОСТНОСТЬ

Эта особенность часто используется просто для эксплуатации связей, встроенных в БД. В предыдущем примере мы установили связь между двумя таблицами в объединении. Это прекрасно. Но эти таблицы уже были соединены через snum-поле. Эта связь называется состоянием справочной целостности, как мы уже говорили в Главе 1. Используя объединение, можно извлекать данные в терминах этой связи. Например, чтобы показать имена всех заказчиков, соответствующих продавцам, которые их обслуживают, мы будем использовать такой запрос:

SELECT Customers.cname, Salespeople.sname FROM Customers, Salespeople WHERE Salespeople.snum = Customers.snum;

Вывод этого запроса показан на Рисунке 8.2. Это пример объединения, в котором столбцы используются для определения предиката запроса, и в этом случае snum-столбцы из обеих таблиц удалены из вывода. И это прекрасно. Вывод показывает, какие заказчики каким продавцом обслуживаются; значения поля snum, которые устанавливают связь, отсутствуют. Однако, если вы введёте их в вывод, то вы должны или удостовериться, что вывод понятен сам по себе, или должны обеспечить комментарий данных при выводе.

=============== SQL Execution Log ============ | SELECT Customers.cname, Salespeople.sname, | | FROM Salespeople, Customers | | WHERE Salespeople.snum = Customers.snum | | ============================================= | | cname sname | | ------- -------- | | Hoffman Peel | | Giovanni Axelrod | | Liu Serres | | Grass Serres | | Clemens Peel | | Cisneros Rifkin | | Pereira Motika | ============================================= Рисунок 8.2 Объединение продавцов с их заказчикам

ОБЪЕДИНЕНИЕ ТАБЛИЦ ПО РАВЕНСТВУ ЗНАЧЕНИЙ
В СТОЛБЦАХ И ДРУГИЕ ВИДЫ ОБЪЕДИНЕНИЙ

Объединения, которые используют предикаты, основанные на равенствах, называются объединениями по равенству. Все наши примеры в этой главе до настоящего времени относились именно к этой категории, потому что все условия в предложениях WHERE базировались на математических выражениях, использующих знак равенства (=). Строки ‘city = ‘London’ и ‘Salespeople.snum = Orders.snum ‘ — примеры таких типов равенств, найденных в предикатах. Объединения по равенству это, вероятно, наиболее общий вид объединения, но имеются и другие. Вы можете использовать практически любую реляционную операцию в объединении. Здесь дан пример другого вида объединения (вывод показан на Рисунке 8.3):

SELECT sname, cname FROM Salespeople, Customers WHERE sname < cname AND rating < 200; =============== SQL Execution Log ============ | SELECT sname, cname | | FROM Salespeople, Customers | | WHERE sname < cname | | AND rating < 200; | | ============================================= | | sname cname | | -------- ------- | | Peel Pereira | | Motika Pereira | | Axelrod Hoffman | | Axelrod Clemens | | Axelrod Pereira | | | ============================================= Рисунок 8.3 Объединение, основанное на неравенстве

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

ОБЪЕДИНЕНИЕ БОЛЕЕ ДВУХ ТАБЛИЦ

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

SELECT onum, cname, Orders.cnum, Orders.snum FROM Salespeople, Customers, Orders WHERE Customers.city < >Salespeople.city AND Orders.cnum = Customers.cnum AND Orders.snum = Salespeople.snum; =============== SQL Execution Log ============== | | | SELECT onum, cname, Orders.cnum, Orders.snum | | FROM Salespeople, Customers, Orders | | WHERE Customers.city < >Salespeople.city | | AND Orders.cnum = Customers.cnum | | AND Orders.snum = Salespeople.snum; | | =============================================== | | onum cname cnum snum | | ------ ------- ----- ----- | | 3001 Cisneros 2008 1007 | | 3002 Pereira 2007 1004 | | 3006 Cisneros 2008 1007 | | 3009 Giovanni 2002 1003 | | 3007 Grass 2004 1002 | | 3010 Grass 2004 1002 | =============================================== Рисунок 8.4 Объединение трёх таблиц

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

РЕЗЮМЕ

Теперь вы больше не ограничиваетесь просмотром одной таблицы в каждый момент времени. Кроме того, вы можете делать сложные сравнения между любыми полями любого количества таблиц и использовать полученные результаты, чтобы решать, какую информацию вы хотели бы видеть. Фактически эта методика настолько полезна для построения связей, что она часто используется для создания их внутри одиночной таблицы. Это будет правильным: вы сможете объединить таблицу с собой, а это очень удобна вещь. Это будет темой Главы 9.

РАБОТА СО SQL

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

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

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

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