Операторы ALL/ANY/SOME

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

Синтаксис SQL 2003

SELECT …

WHERE выражение сравнение {ALL | ANY | SOME} (подзапрос)

Ключевые слова

WHERE выражение

Скалярное выражение (например, столбец) в случае ALL сравнивается со всеми значениями в подзапросе, а в случае ANY или SOME — с каждым выражением в подзапросе до тех пор, пока не будет найдено совпадение. В случае оператора ALL, чтобы возвращаемым значением было TRUE, все строки должны совпадать с выражением. В случае оператора ANY или SOME с выражением должна совпасть одна или несколько строк.

сравнение

Сравнивает выражение и подзапрос. Сравнение должно представлять собой стандартный оператор сравнения, такой, как=<>, !=, >, >=, < или <=.

Общие правила

Оператор ALL возвращает булево значение TRUE в одном из двух случаев: либо подзапрос возвращает пустой набор данных (то есть не возвращает записей), либо все записи в наборе выдерживают сравнение. Оператор ALL возвращает значение FALSE, если сравнение выдерживают не все записи в наборе. Операторы ANY и SOME возвращают булево значение TRUE, если хотя бы одна запись подзапроса выдерживает сравнение, и FALSE, если ни одна из записей не выдерживает сравнение (или когда подзапрос возвращает пустой набор данных). Если хотя бы одно возвращаемое подзапросом значение равно NULL, то результатом проверки будет NULL, а не TRUE.

Например, мы хотим увидеть всех авторов, не имеющих напечатанных произведений.

SELECT au_id FROM authors
WHERE au_id <> ALL(SELECT au_id FROM titleauthor)

Операторы ANY/SOME вы можете использовать для создания различного рода фильтров. Например, в следующем запросе из таблицы employees извлекаются записи обо всех служащих, работающих в Анкоридже и внесенных в таблицу employee_archive.

SELECT *
FROM employees
WHERE job_lvl=ANY(SELECT job_lvl FROM employee_archive WHERE city="Anchorage")

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

Не включайте в подзапрос специальные предложения, такие, как ORDER BY, GROUP BY, CUBE, ROLLUP, WITH и тому подобное. За дополнительной информацией обращайтесь к запросу «Подинструкция SUBQUERY».

Советы и хитрости программирования

Операторы ALL и ANY/SOME довольно сложно использовать. Большинство разработчиков считают, что гораздо легче использовать сходные функции Мн EXISTS.

Различия в реализациях на разных платформах

Все платформы поддерживают операторы ALL и ANY/SOME в том виде, как это описано выше, за исключением MySQL, в котором до версии 4.0 не было поддержки подзапросов. На платформах MySQL версий более ранних, чем 4.0, попробуйте вместо операторов ANY/SOME использовать операторы IN и EXISTS.

В Oracle поддерживается небольшая вариация, отличительной особенностью которой является то, что вы можете задать вместо подзапроса список значений. Например, можно найти всех служащих, у которых job_lvl равен 9 или 14.

SELECT *
FROM employee
WHERE job_lvl=ALL (9, 14)

В DB2 и SQL Server поддерживаются дополнительные операции сравнения: не более (!>) и не менее (!<).

Источник