Введение в соединения / Habr

По материалам статьи Craig Freedman: Introduction to Joins

Соединение (JOIN) - одна из самых важных операций, выполняемых реляционными системами управления базами данных (РСУБД). РСУБД используют соединения для того, чтобы сопоставить строки одной таблицы строкам другой таблицы. Например, соединения можно использовать для сопоставления продаж - клиентам или книг - авторам. Без соединений, имелись бы раздельные списки продаж и клиентов или книг и авторов, но невозможно было бы определить, какие клиенты что купили, или какой из авторов был заказан.

Можно соединить две таблицы явно, перечислив обе таблицы в предложении FROM запроса. Также можно соединить две таблицы, используя для этого всё разнообразие подзапросов. Наконец, SQL Server во время оптимизации может добавить соединение в план запроса, преследуя свои цели.

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

Inner join
Outer join
Cross join
Cross apply
Semi-join
Anti-semi-join

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

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 тоже был продан товар, но поскольку в таблице нет такого клиента, сведения о такой продаже не появились в результате.

Внутренние соединения полностью коммутативны. "A inner join B" и "B inner join 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, поскольку для проданного товара 'Printer' нет соответствующей записи клиента. Обратите внимание на последнюю строку, у которой отсутствующие значения заполнены значением 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), она охватывает все типы внешних соединений:

Соединение	Выводятся …
A left outer join B	Все строки A
A right outer join B	Все строки B
A full outer join B	Все строки A и B

Полные внешние соединения коммутативны. Кроме того, "A left outer join B " и "B right outer join 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

CROSS APPLY

В SQL Server 2005 мы добавили оператор CROSS APPLY, с помощью которого можно соединять таблицу с возвращающей табличное значение функцией (table valued function - 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

Полусоединение и анти-полусоединение

Полусоединение - semi-join возвращает строки только одной из соединяемых таблиц, без выполнения соединения полностью. Анти-полусоединение возвращает те строки таблицы, которые не годятся для соединения с другой таблицей; т.е. они в обычном внешнем соединении выдавали бы 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 действительно использует полусоединение:

^{|--Nested Loops(Left Semi Join, WHERE:([S].[Cust_Id]=[C].[Cust_Id]))
|--Table Scan(OBJECT:([Customers] AS [C]))
|--Table Scan(OBJECT:([Sales] AS [S]))}

Существуют левые и правые полусоединения. Левое полусоединение возвращает строки левой (первой) таблицы, которые соответствуют строкам из правой (второй) таблицы, в то время как правое полусоединение возвращает строки из правой таблицы, которые соответствуют строкам из левой таблицы.

Подобным образом может использоваться анти-полусоединение для обработки подзапроса с NOT EXISTS.

Дополнение

Во всех представленных в статье примерах использовались предикаты соединения, который сравнивали, являются ли оба столбца каждой из соединяемых таблицы равными. Такой тип предикатов соединений принято называть "соединением по эквивалентности". Другие предикаты соединений (например, неравенства) тоже возможны, но соединения по эквивалентности распространены наиболее широко. В SQL Server заложено много альтернативных вариантов оптимизации соединений по эквивалентности и оптимизации соединений с более сложными предикатами.

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