The query
SELECT name, city, state, zip, account
FROM customer
WHERE account =
(SELECT MAX(account)
FROM customer)
gives the table:
The query finds the customer with the largest account.
The second SELECT statement, enclosed in parentheses, is called a subquery. It is a completely self-contained SQL query. Its execution produces a value or a set of values as part of the main query. The main query is completed by these values in order to make a valid command of it.
First the above query finds a value, i.e. the largest account of any customer. This amount becomes part of the conditional expression 'account = (...)'. The main query selects the customer whose account satisfies this condition.
It is important that the subquery only produces one value. If it generates more values, the conditional expression 'account = (more than one value)' is no longer a valid expression. The subquery generally selects one column; and in most cases, it only returns one row of this column.
But it happens sometimes that a subquery finds values from more than one row.
The following example contains a condition with the operator IN: It finds all customers and their accounts which are equal to the accounts of the customers in New York. As a list after IN may contain more than one value, the inner, subordinate query can return more than one value (but from one and the same column).
SELECT cno, name, city, account
FROM customer
WHERE account IN
(SELECT DISTINCT account
FROM customer
WHERE city = 'New York')
The statement
SELECT city, FIXED (AVG(account),7,2)
FROM customer
GROUP BY city
HAVING AVG(account) >= ALL
(SELECT AVG(account)
FROM customer
GROUP BY city)
has as result:
the city or cities with the largest average account. 'ALL' means that the wanted city has an average account equal to or greater than any other average account found by the subquery.
The statement
SELECT name, city
FROM hotel
WHERE name = ANY
(SELECT city
FROM hotel)
has as result:
a list of hotels (and their cities) which have the same names as any cities in the base table. The subquery produces a list of city names which is then used for the comparison of the hotel names.
The statement
SELECT * FROM customer
WHERE EXISTS
(SELECT * FROM reservation
WHERE customer.cno = reservation.cno)
makes the condition:
Select reservations only if there is one or more reservations.
The customer number establishes a connection between the tables 'customer' and 'reservation'. Thus the example anticipates the description given in Section 'Columns from Two and More Tables'.
ALL or ANY are used whenever a subquery produces either more than one value or no value at all and when this is taken into account in a condition which usually requires just one value.
- 'WHERE value = ALL(result of the subquery)' is true if the condition 'WHERE value = result' is true for every result produced by the subquery. Operations other than '=' are possible. If one of the results is NULL, the result of the condition with ALL is unknown.
The example above where ALL is used produces the cities the average account of which is the largest average account of all cities.
- 'WHERE value = ANY(result of the subquery)' is true if the condition 'WHERE value = result' is true for any result produced by the subquery.
The example above where ANY is used produces names of hotels which are identical with any city names.
EXISTS is used when the subquery does not need to produce a value, but only is to find out whether there is a row that meets a specific condition.
Find the average accounts for all cities where the average account is greater than that of all customers:
SELECT city, FIXED (AVG(account),7,2)
FROM customer
GROUP BY city
HAVING AVG(account) >
(SELECT AVG(account)
FROM customer)
Show all customers who made a reservation for the 04/12/1998 as the day of arrival:
SELECT name
FROM customer
WHERE cno IN
(SELECT DISTINCT cno
FROM reservation
WHERE arrival = '04/12/1998')
Show all customers who made no reservation at all:
SELECT cno, name
FROM customer
WHERE cno = ALL
(SELECT cno
FROM reservation
WHERE arrival > '03/13/1998')
Subqueries can be nested: i.e., it is possible to subordinate queries to subqueries.
Find all hotels of the city where a customer has the largest account of all customers:
SELECT name, city
FROM hotel
WHERE city =
(SELECT city
FROM customer
WHERE account =
(SELECT MAX(account)
FROM customer))