|
<< Click to Display Table of Contents >> SOME / ANY / ALL predicates |
  
|
|
<< Click to Display Table of Contents >> SOME / ANY / ALL predicates |
|
Compares a column value to a column value in multiple rows in a subquery.
column_reference comparison_predicate SOME | ANY | ALL (subquery)
Use the quantified comparison predicates SOME, ANY, and ALL to filter a table by comparing a column value with multiple comparison values. The quantified comparison predicates are used with comparison predicates to compare a column value to the multiple values in a column of a subquery.
The ANY predicate evaluates TRUE when the accompanying comparison predicate evaluates TRUE for any value from the subquery. The SOME predicate operates functionally the same as ANY. For example, using the statement below, for any row to be retrieved from the HOLDINGS table, the value in the PUR_PRICE column need only be greater than any one value returned in the subquery’s PRICE column.
SELECT *
FROM "Holdings.dbf" H
WHERE (H."Pur_Price" > ANY
(SELECT O."Price"
FROM "Old_Sales.dbf"))
The ALL predicate evaluates TRUE when the accompanying comparison predicate evaluates TRUE for all values from the subquery. For example, using the statement below, for any row to be retrieved from the HOLDINGS table, the value in the PUR_PRICE column needs to be greater than every value returned in the subquery’s PRICE column.
SELECT *
FROM "Holdings.dbf" H
WHERE (H." Pur_Price" > ALL
(SELECT O."Price"
FROM "Old_Sales.dbf"))
Note: The subquery providing the comparison values for the quantified comparison predicates may retrieve multiple rows, but can only have one column.
