SQL Server ANY

Learning Objective

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

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

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.