Quantified Predicate

Compares the value of an expression to some, any, or all values of a single column returned by a subquery.

Syntax

<expression> {= | <> | > | < | >= | <=}
  [SOME | ANY | ALL] (<subquery>)

Notes: String comparisons in Access or a default installation of SQL Server are case-insensitive. The data type of the expression must be compatible with the data type of the value returned by the subquery.

When you use ALL, the predicate is true if the comparison is True for all the values returned by the subquery. If the expression or any of the values returned by the subquery is Null, the result is undefined. When you use SOME or ANY, the predicate is True if the comparison is true for any of the values returned by the subquery. If the expression is a Null value, the result is undefined. If the subquery returns no values, the predicate is False.

Examples

To find the products whose price is greater than all the products in the Support category, enter the following (qxmplProductPrice>AllSupport):

SELECT tblProducts.ProductID, tblProducts.ProductName, tblProducts.UnitPrice
 FROM tblProducts
 WHERE tblProducts.UnitPrice >All
  (SELECT tblProducts.UnitPrice
  FROM tblProducts
  WHERE tblProducts.CategoryDescription = 'Support');

To find the products whose price is greater than any of the products in the Support category, enter the following (qxmplProductPrice>AnySupport):

SELECT tblProducts.ProductID, tblProducts.ProductName, tblProducts.UnitPrice
 FROM tblProducts
 WHERE tblProducts.UnitPrice >Any
  (SELECT tblProducts.UnitPrice
  FROM tblProducts
  WHERE tblProducts.CategoryDescription = 'Support');