Соединение

Соединение - это процесс, когда две или более таблицы объединяются в одну (было рассмотрено ранее). В операторе SELECT соединение используется для выбора информации из двух или более таблиц².

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

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

INNER JOIN (внутреннее соединение) используется, когда нужно включить все строки из обеих таблиц, удовлетворяющие условию объединения.

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

Блоки данных из двух таблиц объединяются, как только в указанных полях будут найдены совпадающие значения.

Пример.

SELECT R1.a1, R1.a2, S1.b1, S1.b2

FROM R1 inner JOIN S1 ON R1.a2=S1.b1

R.a1	R.a2	S.b1	S.b2
a	1	1	h
a	2	2	g
b	3	3	h
b	1	1	h

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

Пример.

SELECT R.a1, R.a2, S.b1, S.b2

FROM R, S

WHERE R.a2=S.b1

R.a1	R.a2	S.b1	S.b2
a	1	1	h
a	2	2	g
b	3	3	h
b	1	1	h

Запрос

SELECT R1.Группа, R1.ФИО, R2.Оценка, R2.Дисциплина

FROM R1, R2

WHERE R1.ФИО = R2.ФИО

и запрос

SELECT R1.Группа, R1.ФИО, R2.Оценка, R2.Дисциплина

FROM R1 INNER JOIN R2 ON R1.ФИО = R2.ФИО

эквивалентны.

Можно создать вложенные объединения, добавив третью таблицу к результату объединения двух других таблиц.

Пример. Получить сведения о товарах, дате сделок, количестве проданного товара и покупателях.

SELECT Товар.Название, Сделка.Количество, Сделка.

Дата, Клиент.Фирма

FROM Клиент INNER JOIN

(Товар INNER JOIN Сделка

ON Товар.КодТовара=Сделка.КодТовара)

ON Клиент.КодКлиента=Сделка.КодКлиента

или можно записать:

WHERE Товар.КодТовара=Сделка.КодТовара

And Клиент.КодКлиента=Сделка.КодКлиента

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

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

SELECT Т.Название, С.Количество,

С.Дата, К.Фирма

FROM Клиент AS К INNER JOIN

(Товар AS Т INNER JOIN

Сделка AS С

ON Т.КодТовара=С.КодТовара)

ON К.КодКлиента=С.КодКлиента;

Пример.

USE AdventureWorks ;

GO

SELECT p.Name AS ProductName,

NonDiscountSales = (OrderQty * UnitPrice),

Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)

FROM Production.Product As p

INNER JOIN Sales.SalesOrderDetail As sod

ON p.ProductID = sod.ProductID

ORDER BY ProductName DESC ;

GO

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

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

Какая из таблиц будет ведущей, определяет вид соединения. LEFT - левое внешнее соединение, ведущей является таблица, расположенная слева от вида соединения; RIGHT - правое внешнее соединение, ведущая таблица расположена справа от вида соединения.

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

	Отношение R
	R.a1		R.a2
	A		1
	A		2
	B		1
	B		3
	B		4

Отношение S
S.b1	S.b2
1	h
2	g
3	h

SELECT R.a1, R.a2, S.b1, S.b2

FROM R LEFT JOIN S ON R.a2=S.b1

R.a1	R.a2	S.b1	S.b2
a	1	1	h
a	2	2	g
b	1	1	h
b	3	3	h
b	4	null	null

Пример. Вывести информацию о всех товарах. Для проданных товаров будет указана дата сделки и количество. Для непроданных эти поля останутся пустыми.

SELECT Товар.*, Сделка.*

FROM Товар LEFT JOIN Сделка

ON Товар.КодТовара=Сделка.КодТовара;

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

SELECT R.a1, R.a2, S.b1, S.b2

FROM R RIGHT JOIN S ON R.a2=S.b1

	Отношение R
	R.a1		R.a2
	A		1
	A		2
	B		1
	B		3
	B		4

Отношение S
S.b1	S.b2
1	h
2	g
3	h
5	l

R.a1	R.a2		S.b1	S.b2
a	1		1	h
a	2		2	g
b	1		1	h
b	3		3	h
null	null	5		l

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

SELECT R1.a1, R1.a2, S1.b1, S1.b2

FROM R1 full JOIN S1 ON R1.a2=S1.b1

R.a1	R.a2	S.b1	S.b2
A	1	1	H
A	2	2	G
B	1	1	H
B	3	3	H
B	4	NULL	NULL
NULL	NULL	5	l

Использование перекрестных соединений (CROSS JOIN).

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

Следующий пример показывает перекрестное соединение Transact-SQL.

USE AdventureWorks;

GO

SELECT p.SalesPersonID, t.Name AS Territory

FROM Sales.SalesPerson p

CROSS JOIN Sales.SalesTerritory t

ORDER BY p.SalesPersonID;

Результирующий набор содержит 170 строк (в SalesPerson имеется 17 строк, а в таблице SalesTerritory существует 10 строк; 17, умноженное на 10, равно 170).

Однако при добавлении предложения WHERE перекрестное соединение ведет себя как внутреннее соединение. Например, следующие запросы Transact-SQL дают одинаковый результирующий набор.

USE AdventureWorks;

GO

SELECT p.SalesPersonID, t.Name AS Territory

FROM Sales.SalesPerson p

CROSS JOIN Sales.SalesTerritory t

WHERE p.TerritoryID = t.TerritoryID

ORDER BY p.SalesPersonID;

Или

USE AdventureWorks;

GO

SELECT p.SalesPersonID, t.Name AS Territory

FROM Sales.SalesPerson p

INNER JOIN Sales.SalesTerritory t

ON p.TerritoryID = t.TerritoryID

ORDER BY p.SalesPersonID

Составные соединения

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

Например, внешний ключ, определенный в таблице dbo.Table2 на столбцах col1, col2 и ссылающийся на столбцы col1, col2 таблицы dbo.Tablel, и нужно написать запрос, выполняющий соединение на основе связи "первичный ключ— внешний ключ". Элемент запроса будет выглядеть следующим образом:

FROM dbo.Tablel AS Tl

JOIN dbo.Table2 AS T2

ON T1. col1 = T2. col1

AND Tl.col2 = T2.col2

Соединение при условии неравенства.

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

В качестве примера соединения при условии неравенства следующий запрос соединяет два экземпляра таблицы Employees (Сотрудники) для формирования уникальных пар сотрудников:

SELECT

E1.empid, E1.firstname, E1.lastname,

E2.empid, E2.firstname, E2.lastname

FROM HR.Employees AS E1

JOIN HR.Employees AS E2

ON E1.empid < E2.empid;

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

Результат:

Особенности внешнего соединения.

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

Во внешнем соединений одна таблица помечается как "сохраняемая" или главная с помощью ключевых слов left outer join, right outer join или FULL outer join, помещаемых между именами таблиц.

Ключевое слово outer не обязательно. Ключевое слово left означает, что сохраняются строки таблицы слева от ключевых слов, а слово right указывает на сохранение строк таблицы, находящейся справа от ключевых слов. Ключевое слово full означает сохранение строк обеих таблиц.

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

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

Обычный вопрос в случае применения внешних соединений - в каком элементе запроса, ON или where, задавать предикат?

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

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

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

Элемент where обрабатывается после элемента from, а именно после того, как выполнены все табличные операции (внешние соединения) и все внешние строки уже добавлены. Кроме того, условие where в отличие от элемента on является окончательным для исключаемых строк.

Пример: Первый запрос вернет всех клиентов, кто сделал или не сделал заказы (для не сделавших значение orderid = NULL):

SELECT C.custid, C.companyname, O.orderid

FROM Sales.Customers AS C

LEFT OUTER JOIN Sales.Orders AS O

ON C.custid = O.custid;

Этот запрос вернет только клиентов, кто не сделал заказы (для не сделавших, значение orderid = NULL):

SELECT C.custid, C.companyname, O.orderid

FROM Sales.Customers AS C

LEFT OUTER JOIN Sales.Orders AS O

ON C.custid = O.custid

WHERE O.orderid IS NULL;

GO

Пример:

Имеется таблица с последовательными датами (таблица Nums1, поле d):

Выбрать даты, в которых выполнялись заказы:

SELECT Nums1.d AS orderdate

FROM Nums1

LEFT OUTER JOIN Sales.Orders AS O

ON Nums1.d = O.orderdate

WHERE O.orderid IS not NULL

ORDER BY orderdate;

Выбрать даты, в которых не выполнялись заказы:

SELECT Nums1.d AS orderdate

FROM Nums1

LEFT OUTER JOIN Sales.Orders AS O

ON Nums1.d = O.orderdate

WHERE O.orderid IS NULL

ORDER BY orderdate;

Замечание: Если предикат в элементе where ссылается на атрибут из несохраняемой таблицы, участвующей во внешнем соединении, применяя выражение вида <атрибут> <операция> <значение>, как правило, это логическая ошибка.

Поскольку атрибуты несохраняемой таблицы соединения во внешних строках равны NULL, выражение вида NULL <операция> <значение> дает в результате UNKnown (если для поиска значений null не применяется явно оператор IS null). Элемент where отбрасывает значения unknown. Подобный предикат в элементе where приводит к исключению всех внешних строк из результирующего набора, сводя на нет внешнее соединение.

Другими словами, логически соединение становится внутренним.

В запросе

SELECT C.custid, C.companyname, O.orderid, O.orderdate

FROM Sales.Customers AS C

LEFT OUTER JOIN Sales.Orders AS O

ON C.custid = O.custid

WHERE O.orderdate >= '20070101';

выполняется левое внешнее соединение таблиц Customers (Клиенты) и Orders (Заказы). Перед применением фильтра из элемента where операция соединения возвращает внутренние строки, включающие клиентов, сделавших заказы, и внешние строки с атрибутами заказа, равными null, включающие клиентов, не сделавших ни одного заказа.

Предикат O.orderdate >= '20070101' в элементе where равен unknown для всех внешних строк, поскольку они содержат значение null в атрибуте о.orderdate.

Все внешние строки отбрасываются элементом where и применение внешнего соединения бесполезно.­

Результат без применения WHERE O.orderdate >= '20070101';

Результат с применением WHERE O.orderdate >= '20070101';