SQL Server ALL

Learning Objective

The objective of this SQL Server tutorial is to teach you how to use the ALL condition with a logical operator for value comparison in a SQL statement.

What is ANY in SQL Server?

The ALL 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 subquery resultset and it must satisfy the logical operation condition against all values in the list for the comparison to be 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 ALL 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 subquery resultset. If a column value satisfies the logical operation condition against all the values in the subquery resultset it passes the test and is included in the resultset. Otherwise It stands rejected. The SELECT subquery with ALL can be executed on the same table or another related table in the database containing the column specified in the WHERE or HAVING condition. Since a column value must satisfy the logical operator condition against all the values in the subquery resultset an ALL condition is difficult to satisfy. The easiest way to understand and remember the ALL condition operation is as below.

1. > ALL means greater than the largest value in the value list.
2. < ALL means less than the smallest value in the value list.
3. = ALL condition is satisfied only when the subquery returns a single value since it is not possible for any column value to be equal to all values in a value list.

Syntax

The basic syntax of SQL Server ALL statement with WHERE and HAVING clause are given below.

SELECT column_list FROM tableX
WHERE | HAVING columnX logical_operator ALL (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 group 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 the specified column or resultset and return an unique value.

NOTE:

• The main SELECT table and the subquery SELECT table can be the same or different tables.
• The column on which GROUP BY operates and the column on which HAVING operates can be the same or different.

SQL Server ALL 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 ALL.

product_id	product_name	list_price
100	Lipton Green Tea	75
101	Tata Tetley	65
102	Aramusk Luxury Soap	67
103	Lux Beauty Bar	55
104	Maybelline Kohl	49
105	Lakme Lip Gloss	33
106	Bru Filter Cofee	109
107	Druk Marmalade	52
108	Nescafe Latte	89
109	Old Spice After Shave	125

order_id	product_id	order_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 ALL – subquery on same table example

We will start with the simplest example where the subquery following ALL 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 product_id value in the table satisfies the outer WHERE condition against all the values is the subquery resultset. The following query does the same.

SELECT product_id
,product_name
,list_price FROM products
WHERE product_id >= ALL (SELECT product_id FROM products WHERE list_price <= 90);

The query will generate the following result.

product_id	product_name	list_price
108	Nescafe Latte	89
109	Old Spice After Shave	125

Explanation: Here first the subquery (or inner query) runs and fetches the values 100, 101, 102, 103, 104, 105, 107, 108 which are the product ids for products whose list prices less than 90. Then the main query (or outer query) compares all the product id values in the table and it must be greater than the largest value 108 in the subquery resultset. The product id values 108 and 109 satisfy the condition and the product details for those constitute the final result resultset.

2)  SQL Server ALL – 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 product table (i.e. different 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 > ALL (SELECT product_id FROM orders WHERE order_quantity = 5);

The query will generate the following result.

product_id	product_name
106	Bru Filter Cofee
107	Druk Marmalade
108	Nescafe Latte
109	Old Spice After Shave

Explanation: Here first the subquery (or inner query) runs on the orders table and fetches the values 102, 105, 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 products table and it must be greater than the largest value 105 in the subquery resultset.  The product id values 106, 107, 108 and 109 satisfy the condition and the product details for those constitute the final result resultset.

3) SQL Server ALL – HAVING example

This example uses the orders table. Here the subquery first determines the average of order_quantity (i.e. the average order size). Then all the order_quantity (i.e. order size) values in the table are matched against the average value and the resultset displays those corresponding order_ids where the order_quantity is greater than the average value. The following query does the same.

SELECT order_id
FROM orders
GROUP BY order_id HAVING MIN (order_quantity) < ALL (SELECT AVG (order_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 order quantity which is 3. Then the main query (or outer query) compares the order quantity values of all the orders one by one to check if it is greater than 3 and 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.