Using SOME(ANY) and ALL keywords with comparison predicates

<expression> <comparison operator> SOME|ANY (<subquery>)

SOME and ANY are synonyms, i.e. any of them may be used. The subquery results is a single value column. If any value V returned by the subquery evaluates the operation “<expression value> <comparison operator> V” to TRUE, the ANY predicate is also equal to TRUE.

<expression> <comparsion operator> ALL (<subquery>)

is similar to that with ANY, except that all values returned by the subquery must meet the predicate “<expression> <comparison operator> V “.

Example 5.8.1

SELECT DISTINCT maker
FROM Product
WHERE type = 'pc' AND
NOT model = ANY (SELECT model
FROM PC
)

It turns out that maker E has not supplied their models from sale:

Let us consider that example in details. The predicate

model = ANY (SELECT model
FROM PC
);

returns TRUE if the model specified by the model column in the main query will be in the PC-table model list (returned by the subquery). Because of the predicate using the NOT negation, TRUE will be obtained unless the model is in the list. That predicate is checked for each row in the main query that return all PC models (type = ‘PC’ predicate) in the Product table. The result set consists of single column - maker’s name. The DISTINCT keyword is used to eliminate any maker duplicates that may occur when a maker produces more than one model absent from the PC table.

Example 5.8.2

SELECT DISTINCT model, price
FROM Laptop
WHERE price > ALL (SELECT price
FROM PC
)

Here are the formal rules for evaluating the predicate with ANY|SOME and ALL parameters:

• If ALL or SOME parameter are given and all the comparison results of expression value with each value returned by the subquery are equal to TRUE, truth value is TRUE.
• If the result set of the subquery does not have any rows with the ALL parameter specified, the result is TRUE. However, if the SOME parameter is specified, the result is equal to FALSE.
• If the ALL parameter has been specified and comparison of the expression value with at least one value obtained from the subquery gives FALSE, the truth value is equal to FALSE.
• If the SOME parameter is specified and comparison of the expression value with at least one value obtained from the subquery gives TRUE, the truth value is equal to TRUE.
• If the SOME parameter is specified and each comparison of the expression value with the values obtained from the subquery gives FALSE, the truth value is also equal to FALSE.
• Otherwise, the result evaluates to UNKNOWN.

Suggested exercises: 17, 24, 51, 67, 68, 74 , 79, 127