Using the 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. Find the PC makers whose models are not presently sold (i.e. they are not available in the PC table):

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:

maker

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. DISTINCT.

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

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

model price

1298 1050.0

1750 1200.0

1752 1150.0

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, 30.

Again about subqueries

It should be noted that a query returns generally a collection of values, so a run-time error may occur during the query execution if one uses the subquery in the WHERE clause without EXISTS, IN, ALL, and ANY operators, which result in Boolean value.

Example. Find the models and the prices of PC priced above laptops at minimal price:

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

This query is quite correct, i.e. the scalar value of the price is compared with the subquery which returns a single value. As a result we get three PC models:

model price

1121 850.0

1233 950.0

1233 980.0

However, if in answer to question regarding the models and the prices of PCs that cost the same as a laptop one writes the following query:

SELECT DISTINCT model, price
FROM PC
WHERE price =
(SELECT price
FROM Laptop);

the following error message will be obtained while executing the above query:

This error is due to comparison of the scalar value to the subquery, which returns either more that single value or none.

In its turn, subqueries may also include nested queries.

On the other hand, it is natural that subquery returning a number of rows and consisting of multiple columns may as well be used in the FROM clause. This restricts a column/row set when joining tables.

Example. Find the maker, the type, and the processor's speed of the laptops with speed above 600 MGz.
For example, this query may be formulated as follows:

SELECT prod.maker, lap.*
FROM (SELECT 'Laptop' AS type, model, speed
FROM Laptop
WHERE speed > 600) AS lap INNER JOIN
(SELECT maker, model
FROM Product) AS prod ON lap.model = prod.model;
As a result we get:

maker type model speed

B Laptop 1750 750

A Laptop 1752 750

And finally, queries may be present in the SELECT clause. Sometimes, this allows a query to be formulated in a shorthand form.

Example. Find the difference between the average prices of PCs and laptops, i.e. by how mach is the laptop price higher than that of PC in average.
Generally speaking, a single SELECT clause is sufficient in this case:

SELECT (SELECT AVG(price)
FROM Laptop) -
(SELECT AVG(price)
FROM PC) AS dif_price;

Here is the result set:

dif_price

365.81818181818187

Suggested exercises:

18, 25, 26, 27, 28, 37, 39, 46, 56, 57.

Previous | Index | Next

Home SELECT exercises (rating stages) DML exercises Developers