The objective of this SQL Server tutorial is to teach you how to use the ANY condition with a logical operator for value comparison in a SQL statement.
What is ANY in SQL Server?
The ANY condition in SQL is used to compare a column value to a directly specified list of values or to a list of values returned by a subquery (usually the latter). It is used with the WHERE or HAVING clauses. When used all the values for the column specified with the WHERE or HAVING clause is evaluated against each and every value in the value list specified or returned by the subquery and if it satisfies the logical operation condition against any value in the list the comparison is successful. It is important to note that this kind of comparison is primarily done for numeric values since a string value can be either equal to or not equal to another string but cannot be <, >, <=, >=. So, for string comparison it has limited use.
Operation
The ANY clause is preceded by a logical operator and followed by a list of values or SELECT subquery. First the subquery executes and fetches the list of values and then the main or outer query executes and compares each column value against the values in the subquery resultset. If a column value satisfies the logical operation condition against any one of the values in the subquery resultset it passes the test and is included in the final resultset. Otherwise It stands rejected. The SELECT subquery with ANY can be executed on the same table or another related table in the database containing the column specified in the WHERE or HAVING condition.
Syntax
The basic syntax of SQL Server ANY statement with WHERE and HAVING clause are given below.
SELECT column_list
FROM tableX
[WHERE columnX logical_operator ANY (SELECT columnX from table(X/Y) WHERE conditions)];
In this syntax,
column_list – the list of columns to be included and displayed in the resultset.
table – table from which data is to be fetched and included in the resultset.
WHERE conditions – Optional. This is used to specify some conditions while selecting data. In case you are not using WHERE clause all the rows available will be selected.
columnX/Y – the table column against which the WHERE or HAVING condition operates.
logical_operator – The =, >, <, >=, <=, != or <> operators used to compare a numeric or string value.
ANY – clause which evaluates a value against a list of values to determine if it matches any value in the list.
GROUP BY – clause used to aggregate records from the table or an intermediary resultset (like the filtered resultset generated by WHERE clause if used before) where the value for a particular column is the same for all the records.
HAVING condition– clause used to specify an aggregate function whose result can be tested on the groups generated by GROUP BY.
aggregate_function – SUM, COUNT, MIN, MAX, or AVG mathematical functions which operate on all values in a column or resultset and return a unique value.
NOTE:
The main SELECT table and the subquery SELECT table can be same or different table.
The column on which GROUP BY operates and the column on which HAVING operates can be same or different.
SQL Server ANY Examples
Columns are also referred to as fields or attributes and the terms are used interchangeably.
A row of information in a table is called a tuple.
Let us consider 2 tables, one called products containing product details and another called orders containing details of orders placed for the different products. We will use these sample tables as reference for understanding the different usage scenarios of ANY.
product_id
product_name
product_category
list_price
100
Lipton Green Tea
201
75
101
Tata Tetley
201
65
102
Aramusk Luxury Soap
202
67
103
Lux Beauty Bar
202
55
104
Maybelline Kohl
203
49
105
Lakme Lip Gloss
204
33
106
Bru Filter Cofee
205
109
107
Druk Marmalade
206
52
108
Nescafe Latte
205
89
109
Old Spice After Shave
207
125
Table Name: products
order_id
product_id
product_quantity
301
102
5
301
109
2
301
106
2
302
103
4
302
107
1
303
101
3
303
109
1
303
108
3
304
105
5
304
104
5
Table Name: orders
1) SQL Server ANY – subquery on same table example
We will start with the simplest example where the subquery following ANY is run on the products table to retrieve product_id’s as per a WHERE condition on list_price. Then all the product_id values in the products table (I.e. the same table) are matched against the subquery resultset (which consists of a subset of product_id’s). Finally, the query displays the product details of those products where the value of product_id in the table satisfies the outer WHERE condition against any one of the values is the subquery resultset. The following query does the same.
SELECT product_id
,product_name
,list_price
FROM products WHERE product_id > ANY (SELECT product_id FROM products WHERE list_price>=90);
The query will generate the following result.
product_id
product_name
list_price
107
Druk Marmalade
52
108
Nescafe Latte
89
109
Old Spice After Shave
125
Explanation: Here first the subquery (or inner query) runs and fetches the values 106 and 109 which are the product ids for products whose list prices are greater than 90 (id 109 for list price 125 and id 106 for list price 109). Then the main query (or outer query) compares all the product id values in the table one by one against 106 and 109 (as per the ANY condition) and if the value is greater than either it passes the test and the product details for that product id is displayed in the result.
2) SQL Server ANY – subquery on different table example
In this example we will run the subquery on the orders table to retrieve product_id’s as per a WHERE condition on product_quantity. Then the product_id values in the products table (i.e. another table) are matched against the subquery resultset (which consists of a subset of product_id’s) and displays the product details of those products where the outer WHERE condition is satisfied. The following query does the same.
SELECT product_id
,product_name
FROM products
WHERE product_id = ANY (SELECT product_id FROM orders WHERE product_quantity=5);
The query will generate the following result.
product_id
product_name
102
Aramusk Luxury Soap
104
Maybelline Kohl
105
Lakme Lip Gloss
Explanation: Here first the subquery (or inner query) runs and fetches the values 102, 105 and 104 which correspond to order quantity 5 for a product in the order table. Then the main query (or outer query) compares all the product id values in the main products table one by one against 102, 105 and 104 (as per the ANY condition) and if the value is equal to either 102, 105 or 104 then the test is passed and the product details for those products are displayed in the result.
3) SQL Server ANY – HAVING example
This example uses the orders table. Here the subquery first determines the average of product_quantity from all the orders (i.e. the average order size). Then it compares the product_quantity (i.e. order size) values in the table against the average one by one and those values which are more than the average value are retained and their corresponding order_id’s are displayed as result. The following query does the same.
SELECT order_id
FROM orders
GROUP BY order_id
HAVING min(product_quantity) < ANY (SELECT avg(product_quantity) FROM orders);
The query will generate the following result.
order_id
301
302
303
Explanation: Here first the subquery (or inner query) calculates the average of product quantity which comes to 3. Then the main query (or outer query) compares the product quantity values of all the orders one by one to check if the number of products ordered for an order id is less than the average value of 3. It comes across 2 values (1 and 2) in 4 rows corresponding to order id’s 301, 302 and 303 and displays the same in the resultset.