Операции группового сравнения ALL и ANY

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

Операторы ALL и ANY используются в тех случаях, когда необходимо проверить условие на соответствие, с каждым значением которое вернул подзапрос. Они, как и оператор EXISTS работают только с подзапросами.

Для примера в каждом отделе выберем сотрудника, у которого ЗП больше ЗП всех сотрудников работающих в этом же отделе. Для этой цели применим ALL:

SELECT ID,Name,DepartmentID,Salary

FROM Employees e1

WHERE e1.Salary>ALL(

                 SELECT e2.Salary

                 FROM Employees e2

                 WHERE e2.DepartmentID=e1.DepartmentID -- учесть только сотрудников этого же отдела

                   AND e2.ID<>e1.ID -- чтобы исключить сравнение со своей же ЗП

                   AND e2.Salary IS NOT NULL -- исключить NULL значения

              )

 

ID	Name	DepartmentID	Salary
1000	Иванов И.И.	1	5000
1002	Сидоров С.С.	2	2500
1003	Андреев А.А.	3	2000
1005	Александров А.А.	NULL	2000

Здесь происходит проверка на то, что e1.Salary больше значений e2.Salary, которые вернул подзапрос.

Как думаете, почему здесь вернулись даже те сотрудники, для которых подзапрос не вернул ни одной строки? А потому что логика такая – нет записей, не с чем проверять, а значит я и так больше всех.))) Вот такая хитрость здесь скрыта.

Для большего понимания, давайте посмотрим, как можно здесь оператор ALL заменить оператором NOT EXISTS:

SELECT ID,Name,DepartmentID,Salary

FROM Employees e1

WHERE NOT EXISTS(

               SELECT *

               FROM Employees e2

               WHERE e2.DepartmentID=e1.DepartmentID -- учесть только сотрудников этого же отдела

                 AND e2.Salary>e1.Salary -- выбираем только ЗП больше ЗП этого сотрудника

          )

 

Т.е. мы тут выразили то же самое только другими словами «Верни сотрудников для которых нет сотрудников из того же отдела с большей ЗП чем у него».

Здесь становится понятно почему ALL возвращает истинное значение в том случае если подзапрос не возвращает данных.

Так же обратите внимание, что для ALL важно исключить NULL-значения из подзапроса, иначе результат проверки на каждое значение может оказаться неопределенным. Сравнивайте в этом случае логика ALL логикой при использовании AND, т.е. выражение (Salary>1000 AND Salary>1500 AND Salary>NULL) вернет NULL.

А вот с ANY (он же SOME) будет по-другому:

SELECT ID,Name,DepartmentID,Salary

FROM Employees e1

WHERE e1.Salary>ANY(-- ANY = SOME

                 SELECT e2.Salary

                 FROM Employees e2

                 WHERE e2.DepartmentID=e1.DepartmentID -- учесть только сотрудников этого же отдела

                   AND e2.ID<>e1.ID -- чтобы исключить сравнение со своей же ЗП

              )

 

ID	Name	DepartmentID	Salary
1003	Андреев А.А.	3	2000

C оператором ANY важно, чтобы подзапрос вернул записи, с которыми можно сравнить на любое выполнение условия. Т.к. во всех отделах сидят только по одному сотруднику, кроме ИТ-отдела, то вернулся только Андреев А.А., чью ЗП удалось сравнить с ЗП других сотрудников этого же отдела. Т.е. мы вытащили здесь тех, чья ЗП больше любой ЗП сотрудника из этого же отдела.

Давайте для большего понимания, попробуем выразить здесь ANY при помощи EXISTS:

SELECT ID,Name,DepartmentID,Salary

FROM Employees e1

WHERE EXISTS(

         SELECT *

         FROM Employees e2

         WHERE e2.DepartmentID=e1.DepartmentID -- учесть только сотрудников этого же отдела

           AND e2.Salary<e1.Salary -- проверяем есть ли сотрудники с меньшей ЗП чем у данного сотрудника

      )

 

Смысл здесь стал «есть ли хоть какой-то сотрудник из этого отделу у которого ЗП ниже ЗП данного сотрудника».

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

Наличие NULL-значений в подзапросе здесь не так опасно, т.к. мы сравниваем на любое значение. Сравнивайте в этом случае логика ANY логикой при использовании OR, т.е. выражение (Salary>1000 OR Salary>1500 OR Salary>NULL) может вернуть истинное значение если выполнится хотя бы одно условие.

Если ANY используется для сравнения на равенство, то его можно представить при помощи IN:

SELECT *

FROM Departments

WHERE ID=ANY(SELECT DISTINCT DepartmentID FROM Employees)

 

Здесь мы возвращаем все отделы, в которых есть сотрудники. Соответственно это будет эквивалентно:

SELECT *

FROM Departments

WHERE ID IN(SELECT DISTINCT DepartmentID FROM Employees)

 

Как видите ALL и ANY можно выразить при помощи других операторов. Но в некоторых случаях их использование может сделать запрос более читабельным, поэтому для полноты картины их тоже стоит знать и применять в подходящих для этого случаях. Т.е. при написании запроса вы можете написать его так как вас попросили «выбери сотрудника у которого ЗП больше всех»:

SELECT *

FROM Employees e1

WHERE e1.Salary>ALL(SELECT e2.Salary FROM Employees e2 WHERE e2.ID<>e1.ID AND e2.Salary IS NOT NULL)

 

не заменяя смысл на аналогичный «выбери сотрудников для которых нет сотрудников с ЗП больше чем у него»:

SELECT *

FROM Employees e1

WHERE NOT EXISTS(SELECT * FROM Employees e2 WHERE e2.Salary>e1.Salary)

 

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