Подзапросы с конструкцией APPLY

В MS SQL для последнего примера:

SELECT

ID,

Name,

-- подзапрос 1 - получаем ID сотрудника

(SELECT TOP 1 ID FROM Employees emp WHERE emp.DepartmentID=dep.ID ORDER BY ID DESC) LastEmpID,

-- подзапрос 2 - получаем имя сотрудника

(SELECT TOP 1 Name FROM Employees emp WHERE emp.DepartmentID=dep.ID ORDER BY ID DESC) LastEmpName

FROM Departments dep

 

можно применить конструкцию APPLY, которая имеет 2 формы – CROSS APPLY и OUTER APPLY.

Конструкция APPLY позволяет избавиться от множества подзапросов, как в данном примере, когда требуется получить и ID и Name последнего принятого сотрудника для каждого отдела:

SELECT

ID,

Name,

empInfo.LastEmpID,

empInfo.LastEmpName

FROM Departments dep

CROSS APPLY

(

SELECT TOP 1 ID LastEmpID,Name LastEmpName

FROM Employees emp

WHERE emp.DepartmentID=dep.ID

ORDER BY emp.ID DESC

) empInfo

 

ID	Name	LastEmpID	LastEmpName
1	Администрация	1000	Иванов И.И.
2	Бухгалтерия	1002	Сидоров С.С.
3	ИТ	1004	Николаев Н.Н.

Здесь подзапрос блока CROSS APPLY выполнится для каждого значения строки из таблицы Departments. Если подзапрос строки не вернет, то данный отдел исключается из результирующего списка.

Если требуется, чтобы были возвращены все строки таблицы Departments, то используйте следующую форму этого оператора OUTER APPLY:

SELECT

ID,

Name,

empInfo.LastEmpID,

empInfo.LastEmpName

FROM Departments dep

OUTER APPLY

(

SELECT TOP 1 ID LastEmpID,Name LastEmpName

FROM Employees emp

WHERE emp.DepartmentID=dep.ID

ORDER BY emp.ID DESC

) empInfo

 

ID	Name	LastEmpID	LastEmpName
1	Администрация	1000	Иванов И.И.
2	Бухгалтерия	1002	Сидоров С.С.
3	ИТ	1004	Николаев Н.Н.
4	Маркетинг и реклама	NULL	NULL
5	Логистика	NULL	NULL

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

SELECT dep.ID,dep.Name,pos.PositionID,pos.PositionName

FROM Departments dep

CROSS APPLY

(

SELECT ID PositionID,Name PositionName

FROM Positions

) pos

 

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

Для примера получим отделы, в которых числится более двух сотрудников:

SELECT *

FROM Departments dep

WHERE (SELECT COUNT(*) FROM Employees emp WHERE emp.DepartmentID=dep.ID)>2

 

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

Конструкции EXISTS и NOT EXISTS

Позволяют проверить есть ли соответствующие условию записи в подзапросе:

-- отделы в которых есть хотя бы один сотрудник

SELECT *

FROM Departments dep

WHERE EXISTS(SELECT * FROM Employees emp WHERE emp.DepartmentID=dep.ID)

 

-- отделы в которых нет ни одного сотрудника

SELECT *

FROM Departments dep

WHERE NOT EXISTS(SELECT * FROM Employees emp WHERE emp.DepartmentID=dep.ID)

 

Здесь все просто – EXISTS возвращает True, если подзапрос возвращает хотя бы одну строку, и False, если подзапрос не возвращает строк. NOT EXISTS – инверсия результата.

Конструкция IN и NOT IN с подзапросом

До этого мы рассматривали IN с перечислением значений. Так же можно использовать его с подзапросом, который возвращает перечень этих значений:

-- отделы где есть сотрудники

SELECT *

FROM Departments

WHERE ID IN(SELECT DISTINCT DepartmentID FROM Employees WHERE DepartmentID IS NOT NULL)

 

-- отделы где нет сотрудников

SELECT *

FROM Departments

WHERE ID NOT IN(SELECT DISTINCT DepartmentID FROM Employees WHERE DepartmentID IS NOT NULL)

 

Обратите внимание, что я исключил NULL значение используя условие (DepartmentID IS NOT NULL) в подзапросе. NULL значения в данном случае так же опасны – смотрите об этом в описании конструкции IN во второй части.