SELECT statement

Using SOME(ANY) and ALL keywords with comparison predicates

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 predicates, which result in Boolean value.

Example 5.8.3

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

Console

Execute

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

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

    Scheme

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 four PC models:

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:

Console

Execute

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

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

    Scheme

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 5.8.4

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:

Console

Execute

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;

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;

    Scheme

No scheme

As a result we get:

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

Example 5.8.5

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:

Console

Execute

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

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

    Scheme

Here is the result set: