Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » Operator RSS Feeds

ANY / SOME Logical Operator

Posted By: Sarita Patel     Category: SQL Server     Views: 5293

This article explains use of ANY / SOME logical operator with different examples.

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_expressionx)  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

  
Share: 

 
 
 

Didn't find what you were looking for? Find more on ANY / SOME Logical Operator Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of ANY / SOME Logical Operator is from United States.
 
View All Articles

 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!