Subqueries can be used to formulate conditions for the selection of rows which are not to be applied to all table rows, but only to a group of rows.
But first the example of a query which finds the customer who has the largest account of an entire table:
SELECT name, city, state, zip, account
FROM customer
WHERE account =
(SELECT MAX(account)
FROM customer)
result:
A subquery is called a correlated subquery when it refers to columns of outer tables. Non-correlated subqueries are evaluated only once. Correlated subqueries are evaluated for each row of the outer table; in nested subqueries, the evaluation starts with the innermost query and ends with the outermost query.
The following is an example of correlation. It shows the customers who have the largest accounts in their respective cities:
SELECT name, city, account
FROM customer this_customer
WHERE account =
(SELECT MAX(account)
FROM customer
WHERE city = this_customer.city)
ORDER BY city
result:
'this_customer' in the example is called a 'reference name'.
As the same table is accessed in the inner and outer query of the above example, a reference name must be specified. The task of the reference name is to associate or 'correlate' a row from the result of the main query with a value in the conditional statement.
The following is a detailed description of the example:
Another example of the usage of correlation variables performs a grouping according to 'roomtype'. Those hotels are searched where the prices are less than the average price of the respective type of room.
SELECT hno, roomtype, price
FROM room x
WHERE price <
(SELECT AVG (price)
FROM room
WHERE roomtype = x.roomtype)
