SQL Server ANY Operator

In SQL Server there are many different types of Logical operators like ANY, AND, LIKE, EXISTS, NOT, IN, and BETWEEN. The logical operators are used to perform conditional checks in SQL Queries. These operators are very useful to filter and compare single or multiple data in SQL Queries. In this article let us discuss the 'ANY' operator available in SQL Server.

ANY Operator

The ANY operator in SQL Server compares a column value or literal value with a single-column set of values returned by subquery. The data type of the single column in the subquery should be the same as the data type of the scalar expression column value.

Syntax:

[scalar_expression] [comparison_operator] ANY (subquery)

• scalar_expression: This refers to any valid expression
  
• comparison_operator: This refers to any one of the standard comparison operators.
  
• ANY: Operator must be preceded by any one of the comparison operators like =, !=, >, >=, <, or <=
  
• subquery: Refers here to a 'Select' statement that returns a single column of values.

How Does ANY Operator Work?

The ANY operator returns TRUE if any of the subquery values return TRUE. It only checks for TRUE if any value in the subquery single column satisfies the condition. The ANY operator is useful in situations where we need to check for a specific value which exists within a set of values.

Examples: How to Use ANY Operator

Here are few examples of how we can use the ANY operator.

Sample Data tables used in the examples

Products Table

Orders Table

Example 1

In the below example ANY operator is used to check the Price of Product above 50.

Select * from Products where Price = ANY (Select Price from Products where Price > 50)

Output of the above query

Example 2

In the below example ANY operator is used to check for all Products Sold within the price range of 30 and 50

Select * from Products where Price =ANY (Select Price from Products where Price Between 30 and 50)

Output of the above query:

Example 3

In the below example ANY operator is used to check for Specific Products Ordered based on OrderDetailsID

Select * from Products where ProductID > ANY (Select ProductID from OrderDetails where OrderDetailID = 10 )

Output of the above query

Conclusion

The ANY logical operator in SQL Server is used in filtering data based on single column value in a subquery. This is one of the many logical operators which comes in handy to check for specific values returned by a subquery with a scalar expression. The ANY operator is generally used with comparison operators with the 'WHERE' clause in a 'Select statement'