Any sql all: ANY и ALL — Учебник SQL — Schoolsw3.com

Содержание

SOME | ANY (Transact-SQL) — SQL Server

Статья

04/04/2023

Чтение занимает 3 мин

Применимо к:база данныхSQL Server Azure SQL Управляемый экземпляр SQL Azure

Сравнивает скалярное значение с набором значений, состоящим из одного столбца. Ключевые слова SOME и ANY эквивалентны.

Соглашения о синтаксисе Transact-SQL

Синтаксис

scalar_expression { = | <> | != | > | >= | !> | < | <= | !< }   
     { SOME | ANY } ( subquery )

Примечание

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

scalar_expression
Любое допустимое выражение expression.

{ = | <> | != | > | >= | !> | < | <= | !< }
Любой допустимый оператор сравнения.

SOME | ANY

Указывает на необходимость сравнения.

subquery

Вложенный запрос, содержащий результирующий набор, состоящий из одного столбца. Тип данных возвращаемого столбца должен совпадать с типом данных аргумента scalar_expression.

Типы результата

Boolean

Значение результата

При использовании ключевого слова SOME или ANY значение TRUE возвращается, если указанное сравнение имеет значение TRUE для любой пары (scalar_expression, x), где x является одним из значений набора из одного столбца; в остальных случаях возвращается значение FALSE.

Для ключевого слова SOME необходим аргумент scalar_expression, чтобы провести непосредственное сравнение по крайней мере одного значения, возвращенного вложенным запросом. Инструкции, которым необходим аргумент scalar_expression для сравнения каждого значения, возвращенного вложенным запросом, перечислены в разделе ALL (Transact-SQL). Например, если вложенный запрос возвращает значения 2 и 3, то при значении scalar_expression = SOME (subquery) для выражения scalar_express, равного 2, будет возвращаться TRUE. Если вложенный запрос возвращает значения 2 и 3, то при scalar_expression = ALL (subquery) будет возвращаться FALSE, так как некоторые значения вложенного запроса (значение 3) могут не отвечать критериям этого выражения.

Примеры

A. Выполнение простого примера

Следующие инструкции создают простую таблицу и добавляют значения 1, 2, 3 и 4 в столбец ID.

CREATE TABLE T1  
(ID INT) ;  
GO  
INSERT T1 VALUES (1) ;  
INSERT T1 VALUES (2) ;  
INSERT T1 VALUES (3) ;  
INSERT T1 VALUES (4) ;

Следующий запрос возвращает значение TRUE, так как 3 меньше некоторых из значений в таблице.

IF 3 < SOME (SELECT ID FROM T1)  
PRINT 'TRUE'   
ELSE  
PRINT 'FALSE' ;

Следующий запрос возвращает значение FALSE, так как 3 не меньше каждого из значений в таблице.

IF 3 < ALL (SELECT ID FROM T1)  
PRINT 'TRUE'   
ELSE  
PRINT 'FALSE' ;

Б. Выполнение практического примера

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

-- Uses AdventureWorks
CREATE PROCEDURE ManyDaysToComplete @OrderID INT, @NumberOfDays INT  
AS  
IF   
@NumberOfDays < SOME  
   (  
    SELECT DaysToManufacture  
    FROM Sales.SalesOrderDetail  
    JOIN Production.Product   
    ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID   
    WHERE SalesOrderID = @OrderID  
   )  
PRINT 'At least one item for this order can''t be manufactured in specified number of days.'
ELSE   
PRINT 'All items for this order can be manufactured in the specified number of days or less.' ;

Для проверки этой процедуры выполните ее, используя SalesOrderID``49080, имеющий один компонент, требующий на выполнение 2 дня, и два компонента, требующих немедленного выполнения. Первая инструкция отвечает этим критериям. Второй запрос — нет.

EXECUTE ManyDaysToComplete 49080, 2 ;

Результирующий набор:

All items for this order can be manufactured in the specified number of days or less.

EXECUTE ManyDaysToComplete 49080, 1 ;

Результирующий набор:

At least one item for this order can't be manufactured in specified number of days.

См. также раздел

ALL (Transact-SQL)
CASE (Transact-SQL)
Встроенные функции (Transact-SQL)
Операторы (Transact-SQL)
SELECT (Transact-SQL)
Предложение WHERE (Transact-SQL)
IN (Transact-SQL)
IS [NOT] DISTINCT FROM (Transact-SQL)

Отличие работы операторов ANY, SOME и ALL в языке T-SQL | Info-Comp.ru

Продолжаем изучать язык T-SQL и сегодня статья будет посвящена таким операторам как: ANY, SOME и ALL, мы узнаем, что это за операторы, как они работают и чем они отличаются друг от друга.

И начать хотелось бы с того, что все эти операторы ANY, SOME и ALL используются в T-SQL для сравнения скалярного значения с результирующим набором подзапроса, состоящим из одного столбца. Во всех случаях тип данных столбца, возвращаемого подзапросом должен совпадать с типом данных столбца (выражения), с которым происходит сравнение.

Операторы ANY и SOME эквивалентны, т.е. результат их работы будет одинаковый. Какой использовать можете решать на основе контекста запроса или участка кода, например, ANY переводится как «Любой», а SOME как «Некоторые» (для сведения ALL это «Все»).

Эти операторы используются с любым допустимым оператором сравнения: = | <> | != | > | >= | !> | < | <= | !< .

ANY, SOME и ALL возвращают результат с типом Boolean.

Полезные материалы по теме:
Видеокурсы по T-SQL
Справочник T-SQL;
Основы программирования на T-SQL;
Самоучитель по языку Transact-SQL.

Содержание

Операторы ANY и SOME в языке T-SQL
Оператор ALL в языке T-SQL
Исходные данные
Примеры работы операторов ANY, SOME и ALL
Пример 1
Пример 2
Пример 3

Операторы ANY и SOME в языке T-SQL

Работа этих операторов заключается в том, чтобы сравнить, по крайней мере, одно значение результирующего набора подзапроса со значением выражения и если результатом сравнения для любой пары значений является TRUE, то результат работы ANY и SOME также TRUE. Другими словами, если в качестве скалярного значения у нас будет выступать 1, а результирующий набор подзапроса возвращает 1, 2 и 3 результат условия 1 = ANY (1,2,3) будет TRUE, так как значение 1 равняется одному из значений результирующего набора подзапроса.

Примечание! В MS SQL Server использовать перечисление значений вместо подзапроса не допускается (т.е. будет ошибка, если написать ANY = (1,2,3)).

В случае если подзапрос не возвращает строк, то результат работы ANY и SOME будет FALSE.

Оператор ALL в языке T-SQL

Данный оператор сравнивает каждое значение столбца, возвращаемого вложенным запросом со значением столбца выражения и если заданное сравнение, возвращает TRUE для всех пар, то результат работы оператора ALL также TRUE, в противном случае FALSE. Другими словами, если в качестве скалярного значения у нас будет выступать 5, а результирующий набор подзапроса возвращает 1, 2 и 3 результат условия 5 > ALL (1,2,3) будет TRUE, так как значение 5 больше всех значений результирующего набора подзапроса. А если, например условие будет 2 > ALL (1,2,3), то результатом будет FALSE, так как значение 2 не больше всех возвращаемых подзапросом значений.

Если подзапрос не возвращает строк, то результат работы оператора ALL равен TRUE.

Без примеров понять работу операторов ANY, SOME и ALL, и уж тем более сравнить их, наверное, сложно, поэтому давайте сразу перейдём к примерам и для начала разберем исходные данные.

Исходные данные

В качестве СУБД у меня будет выступать MS SQL Server 2012 Express. Для примера будет база данных Test, а в ней таблица TestTable, которую мы сейчас создадим и наполним тестовыми данными. Эти данные представляют собой список товаров с указанием их цены и категории.

   
   --Создание таблицы
   CREATE TABLE dbo.TestTable(
                ID INT IDENTITY(1,1) NOT NULL,
                ProductName VARCHAR(50) NOT NULL,
                Price MONEY NULL,
                CategoryID INT NOT NULL,
    CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED (ID ASC)
   )
   GO
   --Вставляем тестовые данные
   INSERT INTO dbo. TestTable (ProductName, Price, CategoryID)
         VALUES('Компьютер', 500, 1)
   GO
   INSERT INTO dbo.TestTable (ProductName, Price, CategoryID)
         VALUES('Монитор', 400, 2)
   GO
   INSERT INTO dbo.TestTable (ProductName, Price, CategoryID)
         VALUES('Телефон', 200, 1)
   GO
   INSERT INTO dbo.TestTable (ProductName, Price, CategoryID)
         VALUES('Планшет', 300, 1)
   GO
   INSERT INTO dbo.TestTable (ProductName, Price, CategoryID)
         VALUES('Принтер', 250, 2)
   GO

Примеры работы операторов ANY, SOME и ALL

Пример 1

Допустим, нам необходимо получить все товары из категории 1, цена которых превышает цену товаров из категории 2. При этом мы напишем три SQL запроса SELECT, первый просто выводит цены товаров из категории 2, второй запрос с условием ANY, а третий с ALL и посмотрим на результаты их работы.

   
   --Цена товаров из второй категории
   SELECT Price 
   FROM dbo. TestTable 
   WHERE CategoryID = 2

   --ANY
   SELECT * FROM dbo.TestTable
   WHERE CategoryID = 1 
     AND Price > ANY (SELECT Price 
                         FROM dbo.TestTable 
                         WHERE CategoryID = 2)

   --ALL
   SELECT * FROM dbo.TestTable
   WHERE CategoryID = 1 
     AND Price > ALL (SELECT Price 
                         FROM dbo.TestTable 
                         WHERE CategoryID = 2)

Как видим, запрос с ANY вернул 2 строки, это означает что стоимость этих товаров больше стоимости хотя бы одного товара из второй категории (500 больше всех, а 300 больше 250).

Запрос с условием ALL вернул одну строку, это означает что стоимость данного товара больше стоимости всех товаров из второй категории (500 больше 400 и 500 больше 250).

Заметка! XACT_ABORT в T-SQL – что это такое и как использовать.

Пример 2

Теперь давайте посмотрим, как будут вести себя эти операторы, если мы укажем несуществующую категорию (например, 5) в подзапросе, т. е. подзапрос, вообще не вернет строк.

   
   --ANY
   SELECT * FROM dbo.TestTable
   WHERE CategoryID = 1 
     AND Price > ANY (SELECT Price 
                         FROM dbo.TestTable 
                         WHERE CategoryID = 5)

   --ALL
   SELECT * FROM dbo.TestTable
   WHERE CategoryID = 1 
     AND Price > ALL (SELECT Price 
                         FROM dbo.TestTable 
                         WHERE CategoryID = 5)

В первом случае с ANY запрос не вернул строк, а во втором с ALL вернулись все строки. Как я и говорил с ANY, если подзапрос не возвращает строк, то результат работы оператора будет FALSE т.е. мы пытались найти хоть одно сравнение значений с TRUE, но не нашли поэтому ничего и не вывелось.

С оператором ALL вывелись все строки, так как для сравнения со всеми значениями результата подзапроса нужны сами эти значения, а если их нет, то условие не применяется и отбор строк не происходит (результат работы оператора ALL в этом случае TRUE).

Пример 3

А сейчас давайте узнаем, что будет, если подзапрос возвращает помимо нормальных значений значение NULL. Для этого сначала давайте добавим еще одну строку в таблицу TestTable со значением цены NULL.

   
   INSERT INTO dbo.TestTable (ProductName, Price, CategoryID)
         VALUES('Сканер', NULL, 2)

И запустим точно такой же запрос как в примере 1.

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

Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения специально для начинающих.

На этом предлагаю заканчивать надеюсь, материал был Вам полезен, пока!

Оператор SQL ANY, иллюстрированный практическими примерами

Резюме : в этом руководстве вы узнаете об операторе SQL ANY и о том, как его использовать для сравнения значения с набором значений.

Знакомство с оператором SQL ANY

Оператор ANY — это логический оператор, который сравнивает значение с набором значений, возвращаемых подзапросом. Перед оператором ANY должен стоять оператор сравнения >, >=, <, <=, =, <>, а за ним следует подзапрос.

Ниже показан синтаксис оператора ANY :

   WHERE имя_столбца оператор сравнения ANY (подзапрос)
  
  Язык кода: SQL (язык структурированных запросов) (sql)

Если подзапрос не возвращает ни одной строки, условие оценивается как ложное. Предположим, что подзапрос не возвращает нулевые строки. Ниже показано значение оператора ANY при его использовании с каждым оператором сравнения:

Условие	Значение
x = ЛЮБОЙ (…)	Значения в столбце c должны соответствовать одному или нескольким значениям в наборе, чтобы их оценка была истинной.
x != ЛЮБОЙ (…)	Значения в столбце c не должны совпадать с одним или несколькими значениями в наборе, чтобы их оценка была истинной.
x > ANY (…)	Значения в столбце c должны быть больше наименьшего значения в наборе, чтобы они были истинными.
x < ЛЮБОЙ (…)	Значения в столбце c должны быть меньше самого большого значения в наборе, чтобы оно было истинным.
x >= ANY (…)	Значения в столбце c должны быть больше или равны наименьшему значению в наборе, чтобы быть верными.
x <= ANY (…)	Значения в столбце c должны быть меньше или равны наибольшему значению в наборе, чтобы оно было истинным.

SQL ЛЮБЫЕ примеры

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

SQL ANY с оператором равенства пример

Следующий оператор использует функцию AVG() и предложение GROUP BY для поиска средняя зарплата каждого отдела:

   ВЫБЕРИТЕ
    ОКРУГЛ(СРЕДНЕЕ(зарплата), 2)
ОТ
    сотрудники
ГРУППА ПО
    id_отдела
СОРТИРОВАТЬ ПО
    AVG(зарплата) DESC;
  
  Язык кода: SQL (язык структурированных запросов) (sql)

Чтобы найти всех сотрудников, зарплата которых равна средней зарплате в их отделе, используйте следующий запрос:

SELECT имя, фамилия, зарплата ОТ сотрудники ГДЕ зарплата = ЛЮБАЯ ( ВЫБИРАТЬ СРЕДНЯЯ (зарплата) ОТ сотрудники ГРУППА ПО id_отдела) СОРТИРОВАТЬ ПО имя, фамилия, зарплата;