ANY / SOME
operator compares a scalar value with a single-column set of values. Use of ANY
and SOME operator is same. Both operators are interchangeable.
Syntax
of Logical Operator ANY / SOME :
scalar_expression { = | < > | ! = | > | > = | !
> | < | < = | ! < } {
ANY | SOME} ( subquery )
scalar_expression is valid expression in sql server.
{ = | <> | != | > |
>= | !> | < | <= | !< }
scalar_expression
is valid expression in sql server.
{
= | <> | != | > | >= | !> | < | <= | !< } is a
comparison operator.
Subquery is a query
that returns a result set of one column. The data type of the returned column
must be the same data type as the data type of scalar_expression. ORDER BY
clause, the COMPUTE clause, and the INTO keyword are not allowed in subquery).
Return type of ANY / SOME operator is boolean. It
returns TRUE when the comparison specified is TRUE for all pairs(scalar_expression, x)
where x is a value in the single-column set otherwise it returns FALSE.
Example of Logical Operator ANY / SOME :
Example 1 : Using any /
some operator with subquery in a where clause
SELECT P.ProductName, P.UnitPrice
FROM Products P
WHERE P.ProductName =ANY(SELECT
S.ProductName FROM Sales_by_Category S
WHERE P.ProductName = S.ProductName)
Output
ProductName UnitPrice
Boston Crab
Meat 18.40
Gnocchi di
nonna Alice 38.00
Gudbrandsdalsost 36.00
Outback
Lager 15.00
Schoggi
Schokolade 43.90
Thüringer
Rostbratwurst 123.79
Tourtière 7.45
Above query
shows use of Any operator to get products and unitprice which are sold. You can
also achieve same result using Existx and IN operator. To view this example,
please visit below links.
Exists
Operator
IN Operator
Example 2 : Using any / some operator with
subquery in a where clause
SELECT OrderID,
Subtotal
FROM Order_Subtotals
WHERE Subtotal > ANY
(SELECT SaleAmount
FROM Sales_Totals_by_Amount)
ORDER BY Subtotal ASC
Output
OrderID
Subtotal
10515 9921.30
10691 10164.80
10540 10191.70
10479 10495.60
10897 10835.24
10817 10952.84
10417 11188.40
Above query displays orders with a subtotal greater than ANY saleamountin Sales_Totals_by_Amount table i.e. SubTotal greater than any amount
present in the
set returned by the subquery. That means that records selected by the outer
query should be greater than any of the values.
You
can achieve same result using min aggregate function in the subquery. To view this
example, please visit below link.
Min Function