Отличие работы операторов 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, в которых используется последовательная методика обучения специально для начинающих.

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