Завершаем разговор о UNION-соединениях

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

Чаще всего в моей в практике находит применение UNION ALL, но и другие виды вертикальных объединений находят свое применение.

При нескольких операциях вертикально объединения, не гарантируется, что они будут выполняться последовательно сверху-вниз. Создадим еще одну таблицу и рассмотрим это на примере:

CREATE TABLE NextTable(

N1 int,

N2 varchar(10)

)

GO

 

INSERT NextTable(N1,N2)VALUES

(1,'Text 1'),

(4,'Text 4'),

(6,'Text 6')

 

Например, если мы напишем просто:

SELECT T1 x,T2 y

FROM TopTable

 

EXCEPT

 

SELECT B1,B2

FROM BottomTable

 

INTERSECT

 

SELECT N1,N2

FROM NextTable

 

То мы получим:

x	y
1	Text 1
2	Text 2
3	Text 3
4	Text 4
5	Text 5

 

Т.е. получается сначала выполнился INTERSECT, а после EXCEPT. Хотя логически будто должно было быть наоборот, т.е. идти сверху-вниз.

Я редко использую эти операции объединений, а тем более в таком виде, поэтому, чтобы не думать не гадать, в какой очередности он выполняет объединения, можно просто при помощи скобок явно указать последовательность объединений, давайте скажем, что сначала нужно сделать EXCEPT, а потом INTERSECT:

(

SELECT T1 x,T2 y

FROM TopTable

 

EXCEPT

 

SELECT B1,B2

FROM BottomTable

)

 

INTERSECT

 

SELECT N1,N2

FROM NextTable

 

x	y
1	Text 1
4	Text 4

Вот теперь я получил то, что и хотел.

Я не знаю работает ли такой синтаксис в других СУБД, но если что используйте подзапрос:

SELECT x,y

FROM

(

SELECT T1 x,T2 y

FROM TopTable

 

EXCEPT

 

SELECT B1,B2

FROM BottomTable

) q

 

INTERSECT

 

SELECT N1,N2

FROM NextTable

 

При использовании ORDER BY сортировка применяется к окончательному набору:

SELECT T1 x,T2 y

FROM TopTable

 

UNION ALL

 

SELECT B1,B2

FROM BottomTable 

 

UNION ALL

 

SELECT B1,B2

FROM BottomTable 

 

ORDER BY x DESC

 

Для задания сортировки здесь удобней использовать псевдоним колонки, заданный в первом запросе.

Самое главное про UNION-объединения я вроде написал, если что поиграйте с UNION-объединениями самостоятельно.

Примечание. В СУБД Oracle тоже есть такие же виды соединения, разница только в операции EXCEPT, там она называется MINUS.

 

Использование подзапросов

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

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

SELECT q1.x1,q1.y1,q2.x2,q2.y2

FROM

(

SELECT T1 x1,T2 y1

FROM TopTable

 

EXCEPT

 

SELECT B1,B2

FROM BottomTable

) q1

JOIN

(

SELECT T1 x2,T2 y2

FROM TopTable

 

EXCEPT

 

SELECT N1,N2

FROM NextTable

) q2

ON q1.x1=q2.x2

 

Если не понятно, сразу, то разбирайте такие запросы по частям. Т.е. сначала посмотрите, что возвращает первый подзапрос «q1», потом, что возвращает второй подзапрос «q2», а затем выполните операцию JOIN над результатами подзапросов «q1» и «q2».

Конструкция WITH

Это достаточно полезная конструкция особенно в случае работы с большими подзапросами.

Сравним:

SELECT q1.x1,q1.y1,q2.x2,q2.y2

FROM

(

SELECT T1 x1,T2 y1

FROM TopTable

 

EXCEPT

 

SELECT B1,B2

FROM BottomTable

) q1

JOIN

(

SELECT T1 x2,T2 y2

FROM TopTable

 

EXCEPT

 

SELECT N1,N2

 FROM NextTable

) q2

ON q1.x1=q2.x2

 

То же самое написанное при помощи WITH:

WITH q1 AS(

SELECT T1 x1,T2 y1

FROM TopTable

 

EXCEPT

 

SELECT B1,B2

FROM BottomTable

),

q2 AS(

SELECT T1 x2,T2 y2

FROM TopTable

 

EXCEPT

 

SELECT N1,N2

FROM NextTable

)

 

-- основной запрос становится более прозрачным

SELECT q1.x1,q1.y1,q2.x2,q2.y2

FROM q1

JOIN q2 ON q1.x1=q2.x2

 

Как видим большие подзапросы вынесены и поименованы в блоке WITH, что дало возможность разгрузить текст основного запроса и сделать его понятным.

Вспомним так же пример из предыдущей части, где использовалось представление ViewEmployeesInfo:

CREATE VIEW ViewEmployeesInfo

AS

SELECT

emp.*, -- вернуть все поля таблицы Employees

dep.Name DepartmentName, -- к этим полям добавить поле Name из таблицы Departments

pos.Name PositionName -- и еще добавить поле Name из таблицы Positions

FROM Employees emp

LEFT JOIN Departments dep ON emp.DepartmentID=dep.ID

LEFT JOIN Positions pos ON emp.PositionID=pos.ID

 

И запрос, который использовал данное представление:

SELECT

DepartmentName,

COUNT(DISTINCT PositionID) PositionCount,

COUNT(*) EmplCount,

SUM(Salary) SalaryAmount,

AVG(Salary) SalaryAvg

FROM ViewEmployeesInfo emp

GROUP BY DepartmentID,DepartmentName

ORDER BY DepartmentName

 

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

WITH cteEmployeesInfo AS(

SELECT

emp.*, -- вернуть все поля таблицы Employees

dep.Name DepartmentName, -- к этим полям добавить поле Name из таблицы Departments

pos.Name PositionName -- и еще добавить поле Name из таблицы Positions

FROM Employees emp

LEFT JOIN Departments dep ON emp.DepartmentID=dep.ID

LEFT JOIN Positions pos ON emp.PositionID=pos.ID

)

SELECT

DepartmentName,

COUNT(DISTINCT PositionID) PositionCount,

COUNT(*) EmplCount,

SUM(Salary) SalaryAmount,

AVG(Salary) SalaryAvg

FROM cteEmployeesInfo emp

GROUP BY DepartmentID,DepartmentName

ORDER BY DepartmentName

 

Только в случае созданного представления мы можем использовать его из разных запросов, т.к. представление создается на уровне БД. Тогда как подзапрос оформленный в блоке WITH виден только в рамках этого запроса.

Использование WITH по-другому называет CTE-выражениями:
Общие табличные выражения (CTE — Common Table Expressions) позволяют существенно уменьшить объем кода, если многократно приходится обращаться к одним и тем же запросам. CTE играет роль представления, которое создается в рамках одного запроса и, не сохраняется как объект схемы.

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

 

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

WITH cteEmpl AS(

SELECT ID,CAST(Name AS nvarchar(300)) Name,1 EmpLevel

FROM Employees

WHERE ManagerID IS NULL -- все сотрудники у которых нет вышестоящего

 

UNION ALL

 

SELECT emp.ID,CAST(SPACE(cte.EmpLevel*5)+emp.Name AS nvarchar(300)),cte.EmpLevel+1

FROM Employees emp

JOIN cteEmpl cte ON emp.ManagerID=cte.ID

)

SELECT *

FROM cteEmpl

 

ID	Name	EmpLevel
1000	Иванов И.И.	1
1002	_____Сидоров С.С.	2
1003	_____Андреев А.А.	2
1005	_____Александров А.А.	2
1001	__________Петров П.П.	3
1004	__________Николаев Н.Н.	3

Для наглядности пробелы заменены знаками подчеркивания.

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