На основе статьи Крейга Фридмана: Введение в соединения Присоединение (JOIN) — одна из важнейших операций, выполняемых системами управления реляционными базами данных (СУБД).
СУРБД используют соединения для сопоставления строк одной таблицы со строками другой таблицы.
Например, связи можно использовать для сопоставления продаж клиентам или книг авторам.
Без связей были бы отдельные списки продаж и клиентов или книг и авторов, но невозможно было бы определить, какие клиенты что купили или какому автору заказали.
Вы можете явно соединить две таблицы, указав обе таблицы в предложении FROM запроса.
Вы также можете объединить две таблицы, используя различные подзапросы.
Наконец, SQL Server может добавить соединение в план запроса во время оптимизации для достижения своих целей.
Это первая из серии статей, которые я планирую сосредоточить на связях.
Я собираюсь посвятить эту статью основам соединений, описывая назначение операторов логического соединения, поддерживаемых SQL Server. Вот они:
- Внутреннее соединение
- Внешнее соединение
- Перекрестное соединение
- Перекрестное применение
- Полусоединение
- Антиполусоединение
create table Customers (Cust_Id int, Cust_Name varchar(10)) insert Customers values (1, 'Craig') insert Customers values (2, 'John Doe') insert Customers values (3, 'Jane Doe') create table Sales (Cust_Id int, Item varchar(10)) insert Sales values (2, 'Camera') insert Sales values (3, 'Computer') insert Sales values (3, 'Monitor') insert Sales values (4, 'Printer')
Внутренние соединения
Внутренние соединения являются наиболее распространенным типом соединения.Внутреннее соединение просто находит пары строк, которые соединены и удовлетворяют предикату соединения.
Например, в приведенном ниже запросе используется предикат соединения «S.Cust_Id = C.Cust_Id», чтобы найти все сведения о продажах и клиентах с одинаковыми значениями Cust_Id: select *
from Sales S inner join Customers C
on S.Cust_Id = C.Cust_Id
Cust_Id Item Cust_Id Cust_Name
----------- ---------- ----------- ----------
2 Camera 2 John Doe
3 Computer 3 Jane Doe
3 Monitor 3 Jane Doe
Примечания:
Cust_Id = 3 купил два элемента, поэтому он отображается в двух строках набора результатов.Cust_Id=1 ничего не покупал и поэтому не отображается в результате.
При Cust_Id=4 товар также был продан, но поскольку такого покупателя в таблице нет, информация о такой продаже в результате не появилась.
Внутренние связи полностью коммутативны.
«Внутреннее соединение B» и «Внутреннее соединение B A» эквивалентны.
Внешние соединения
Допустим, мы хотели бы увидеть список всех продаж; даже те, которые не имеют соответствующих записей о клиентах.Вы можете написать запрос с внешним соединением, который будет отображать все строки в одной или обеих объединяемых таблицах, даже если для предиката соединения не существует соответствующих строк.
Например: select *
from Sales S left outer join Customers C
on S.Cust_Id = C.Cust_Id
Cust_Id Item Cust_Id Cust_Name
----------- ---------- ----------- ----------
2 Camera 2 John Doe
3 Computer 3 Jane Doe
3 Monitor 3 Jane Doe
4 Printer NULL NULL
Обратите внимание, что сервер возвращает NULL вместо данных о клиенте, поскольку проданный товар «Принтер» не имеет соответствующей записи о клиенте.
Обратите внимание на последнюю строку, в которой пропущенные значения заполнены NULL. Используя полное внешнее соединение, вы можете найти всех клиентов (независимо от того, купили ли они что-либо) и все продажи (независимо от того, есть ли у них соответствующий существующий клиент): select *
from Sales S full outer join Customers C
on S.Cust_Id = C.Cust_Id
Cust_Id Item Cust_Id Cust_Name
----------- ---------- ----------- ----------
2 Camera 2 John Doe
3 Computer 3 Jane Doe
3 Monitor 3 Jane Doe
4 Printer NULL NULL
NULL NULL 1 Craig
В следующей таблице показано, какие строки объединенных таблиц попадут в результирующий набор (оставшаяся таблица может иметь NULL-замены), и она охватывает все типы внешних объединений:
| Сложный | Выход. |
| Левое внешнее соединение B | Все строки А |
| Правое внешнее соединение B | Все строки Б |
| Полное внешнее соединение B | Все линии А и Б |
Кроме того, «Левое внешнее соединение A B» и «B Правое внешнее соединение A» эквивалентны.
Перекрестные соединения
Перекрестное соединение выполняет полное декартово произведение двух таблиц.То есть это соответствие между каждой строкой одной таблицы и каждой строкой другой таблицы.
Для перекрестного соединения вы не можете определить предикат соединения с помощью предложения ON, хотя вы можете использовать предложение WHERE для достижения почти того же результата, что и внутреннее соединение.
Поперечные соединения используются довольно редко.
Никогда не рекомендуется пересекать две большие таблицы, поскольку это потребует очень дорогостоящих операций и приведет к очень большому набору результатов.
select *
from Sales S cross join Customers C
Cust_Id Item Cust_Id Cust_Name
----------- ---------- ----------- ----------
2 Camera 1 Craig
3 Computer 1 Craig
3 Monitor 1 Craig
4 Printer 1 Craig
2 Camera 2 John Doe
3 Computer 2 John Doe
3 Monitor 2 John Doe
4 Printer 2 John Doe
2 Camera 3 Jane Doe
3 Computer 3 Jane Doe
3 Monitor 3 Jane Doe
4 Printer 3 Jane Doe
КРЕСТОВОЕ ПРИМЕНЕНИЕ
В SQL Server 2005 мы добавили оператор CROSS APPLY, который позволяет вам объединить таблицу с помощью табличной функции (TVF), где TVF будет иметь параметр, который будет меняться для каждой строки.
Например, приведенный ниже запрос вернет тот же результат, что и внутреннее соединение, показанное ранее, но с использованием TVF и CROSS APPLY: create function dbo.fn_Sales(@Cust_Id int)
returns @Sales table (Item varchar(10))
as
begin
insert @Sales select Item from Sales where Cust_Id = @Cust_Id
return
end
select *
from Customers cross apply dbo.fn_Sales(Cust_Id)
Cust_Id Cust_Name Item
----------- ---------- ----------
2 John Doe Camera
3 Jane Doe Computer
3 Jane Doe Monitor
Также мы можем использовать внешний вызов — OUTER APPLY, который позволяет нам найти всех клиентов вне зависимости от того, купили они что-то или нет. Это будет похоже на внешнее соединение.
select *
from Customers outer apply dbo.fn_Sales(Cust_Id)
Cust_Id Cust_Name Item
----------- ---------- ----------
1 Craig NULL
2 John Doe Camera
3 Jane Doe Computer
3 Jane Doe Monitor
Полусоединение и антиполусоединение
Полусоединение — полуобъединение возвращает строки только из одной из соединяемых таблиц, не выполняя всего соединения.Антиполусоединение возвращает те строки таблицы, которые не подходят для присоединения к другой таблице; те.
они вернут NULL при обычном внешнем соединении.
В отличие от других операторов соединения, здесь не существует явного синтаксиса для указания выполнения полуобъединения, но SQL Server во многих случаях использует полуобъединения с точки зрения выполнения.
Например, полуобъединение можно использовать в плане подзапроса с EXISTS: select *
from Customers C
where exists (
select *
from Sales S
where S.Cust_Id = C.Cust_Id
)
Cust_Id Cust_Name
----------- ----------
2 John Doe
3 Jane Doe
В отличие от предыдущих примеров, полуобъединение возвращает только данные о клиентах.
В плане запроса вы можете видеть, что SQL Server действительно использует полусоединение: |--Вложенные циклы(Левое полусоединение, WHERE:([S].
[Cust_Id]=[C].
[Cust_Id])) |--Сканирование таблицы(ОБЪЕКТ:([Клиенты] AS [C])) |--Сканирование таблицы(ОБЪЕКТ:([Продажи] AS [S])) Есть левые и правые полусоединения.
Левое полуобъединение возвращает строки из левой (первой) таблицы, соответствующие строкам из правой (второй) таблицы, а правое полуобъединение возвращает строки из правой таблицы, соответствующие строкам из левой таблицы.
Аналогично, анти-полусоединение можно использовать для обработки подзапроса с NOT EXISTS.
Добавление
Во всех примерах, представленных в статье, использовались предикаты соединения, которые сравнивали, равны ли оба столбца каждой из соединяемых таблиц.Этот тип предиката соединения обычно называется «соединением эквивалентности».
Возможны и другие предикаты соединения (например, неравенства), но соединения по эквивалентности являются наиболее распространенными.
SQL Server имеет множество альтернативных вариантов оптимизации соединений по эквивалентности и оптимизации соединений с более сложными предикатами.
SQL Server более гибок в выборе порядка и алгоритма соединения при оптимизации внутренних соединений, чем при оптимизации внешних соединений и CROSS APPLY. Итак, если вы возьмете два запроса, которые отличаются только тем, что один использует исключительно внутренние соединения, а другой — внешние соединения и/или CROSS APPLY, SQL Server сможет найти лучший план выполнения для запроса, который использует только внутренние соединения.
Теги: #базы данных #sql #Microsoft SQL Server #sql-сервер
-
Защитники Внутренней Речи
19 Oct, 24 -
Вся Правда О Google Summer Of Code — Часть 2
19 Oct, 24 -
Фотоотчет С Выставки Web-Studio 2009
19 Oct, 24 -
Выпущен Jquery 1.6.2
19 Oct, 24 -
Как Добавить Функциональность Firefox В Ie7
19 Oct, 24
