SQL data manipulation language

SELECT statement

< 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

Find the PC makers whose models are not presently sold (i.e. they are not available in the PC table):

Console

Execute

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

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

    Scheme

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
 );

Example 5.8.2

Find the models and prices for laptops with priced above any PC:

Console

Execute

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

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

    Scheme

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.
  

Content:

(5.8.1.) Again about subqueries