Биохимия спиртового брожения: Основу технологии получения пива составляет спиртовое брожение, - при котором сахар превращается...
Индивидуальные очистные сооружения: К классу индивидуальных очистных сооружений относят сооружения, пропускная способность которых...
Топ:
Комплексной системы оценки состояния охраны труда на производственном объекте (КСОТ-П): Цели и задачи Комплексной системы оценки состояния охраны труда и определению факторов рисков по охране труда...
Основы обеспечения единства измерений: Обеспечение единства измерений - деятельность метрологических служб, направленная на достижение...
Устройство и оснащение процедурного кабинета: Решающая роль в обеспечении правильного лечения пациентов отводится процедурной медсестре...
Интересное:
Национальное богатство страны и его составляющие: для оценки элементов национального богатства используются...
Берегоукрепление оползневых склонов: На прибрежных склонах основной причиной развития оползневых процессов является подмыв водами рек естественных склонов...
Распространение рака на другие отдаленные от желудка органы: Характерных симптомов рака желудка не существует. Выраженные симптомы появляются, когда опухоль...
Дисциплины:
2020-05-08 | 214 |
5.00
из
|
Заказать работу |
|
|
Данные операторы, очень хитрые и их использовать нужно очень аккуратно. Вообще в своей практике я их применяю достаточно редко, предпочитая использовать в условиях операторы 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 изначально задумывался как язык для обычных пользователей, чтобы они могли выражать свои мысли разными способами.
|
|
Индивидуальные очистные сооружения: К классу индивидуальных очистных сооружений относят сооружения, пропускная способность которых...
Таксономические единицы (категории) растений: Каждая система классификации состоит из определённых соподчиненных друг другу...
Особенности сооружения опор в сложных условиях: Сооружение ВЛ в районах с суровыми климатическими и тяжелыми геологическими условиями...
Типы оградительных сооружений в морском порту: По расположению оградительных сооружений в плане различают волноломы, обе оконечности...
© cyberpedia.su 2017-2024 - Не является автором материалов. Исключительное право сохранено за автором текста.
Если вы не хотите, чтобы данный материал был у нас на сайте, перейдите по ссылке: Нарушение авторских прав. Мы поможем в написании вашей работы!