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.
It turns out that maker E has not supplied their models from sale:
Maker
E
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
Find the models and prices for laptops with priced above any 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.