SQL ANY, ALL

The SQL ANY and ALL Operators

The ANY and ALL operators enable comparisons between a single column value and a range of other values.

The SQL ANY Operator

The ANY operator:

• Yields a boolean outcome.
• Produces TRUE if any of the subquery values satisfy the condition.

In essence, ANY signifies that the condition holds true if the operation is satisfied by any value within the range.

ANY Syntax

SELECT column_name(s) FROM table_name WHERE column_name operator ANY   (SELECT column_name   FROM table_name   WHERE condition);

 

Please note that the operator must be one of the standard comparison operators: (=, <>, !=, >, >=, <, or <=).

The SQL ALL Operator

The ALL operator:

• Provides a boolean outcome.
• Yields TRUE if all subquery values fulfill the condition.
• Is applied in conjunction with SELECT, WHERE, and HAVING statements.

ALL signifies that the condition holds true only if the operation is satisfied by every value within the range.

ALL Syntax With SELECT

SELECT ALL column_name(s) FROM table_name WHERE condition;

ALL Syntax With WHERE or HAVING

SELECT column_name(s) FROM table_name WHERE column_name operator ALL   (SELECT column_name   FROM table_name   WHERE condition);

 

Please keep in mind that the operator must adhere to one of the standard comparison operators: (=, <>, !=, >, >=, <, or <=).

Demo Database

Here is an excerpt from the “Products” table within the Northwind sample database:

ProductID	ProductName	SupplierID	CategoryID	Unit	Price
1	Chais	1	1	10 boxes x 20 bags	18
2	Chang	1	1	24 – 12 oz bottles	19
3	Aniseed Syrup	1	2	12 – 550 ml bottles	10
4	Chef Anton’s Cajun Seasoning	2	2	48 – 6 oz jars	22
5	Chef Anton’s Gumbo Mix	2	2	36 boxes	21.35
6	Grandma’s Boysenberry Spread	3	2	12 – 8 oz jars	25
7	Uncle Bob’s Organic Dried Pears	3	7	12 – 1 lb pkgs.	30
8	Northwoods Cranberry Sauce	3	2	12 – 12 oz jars	40
9	Mishi Kobe Niku	4	6	18 – 500 g pkgs.	97

Here’s a segment from the “OrderDetails” table:

OrderDetailID	OrderID	ProductID	Quantity
1	10248	11	12
2	10248	42	10
3	10248	17	5
4	10249	14	9
5	10249	51	40
6	10250	41	10
7	10250	51	35
8	10250	65	15
9	10251	22	6
10	10251	57	15

SQL ANY Examples

This SQL statement lists the ProductName if it finds any records in the OrderDetails table with a Quantity equal to 10 (which will return TRUE because the Quantity column contains some instances of the value 10).

Example

SELECT ProductName FROM Products WHERE ProductID = ANY   (SELECT ProductID   FROM OrderDetails   WHERE Quantity = 10);

This SQL statement lists the ProductName if it finds any records in the OrderDetails table with a Quantity greater than 99 (which will return TRUE because the Quantity column contains some values larger than 99).

Example

SELECT ProductName FROM Products WHERE ProductID = ANY   (SELECT ProductID   FROM OrderDetails   WHERE Quantity > 99);

This SQL statement lists the ProductName if it finds any records in the OrderDetails table with a Quantity greater than 1000 (which will return FALSE because the Quantity column contains no values larger than 1000).

Example

SELECT ProductName FROM Products WHERE ProductID = ANY   (SELECT ProductID   FROM OrderDetails   WHERE Quantity > 1000);

SQL ALL Examples

This SQL statement retrieves the names of all products.

Example

SELECT ALL ProductName FROM Products WHERE TRUE;

This SQL statement lists the ProductName if all records in the OrderDetails table have a Quantity equal to 10. This will naturally return FALSE because the Quantity column contains various values, not just 10.

Example

SELECT ProductName FROM Products WHERE ProductID = ALL   (SELECT ProductID   FROM OrderDetails   WHERE Quantity = 10);