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

Example 5.8.3

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 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:

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

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

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:

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

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:

Suggested exercises: 10, 18, 24, 25, 26, 28, 43, 56, 57, 60, 61, 62, 63, 72, 75, 80, 87, 88, 92, 96, 99, 110, 111, 112, 113, 118, 127, 129