Subqueries

A subquery is a query whose result supplies a criterion value for another query. Subqueries take the place of normal WHERE expressions. Because the result generated by the subquery takes the place of an expression, the subquery can return only a single value (as opposed to a conventional query, which returns multiple values in the form of rows and columns).

The only syntactical difference between a subquery and any other type of expression placed in a WHERE clause is that the subquery must be enclosed in parentheses. For example, say that you want to create a query that shows your most expensive items. You define an expensive item as an item whose price is above the average price of all items in tblItem. Because the value of a larger-than-average order can be determined (by performing an aggregate average on the UnitPrice field in tblItem), you can use this value as a subquery criterion value in the larger query, as follows:

 SELECT Name, UnitCost  FROM tblItem WHERE (UnitCost >     (SELECT AVG(UnitCost) FROM tblItem)) 

In this case, the query and the subquery happen to be querying the same table, but that doesn't have to be the case. Subqueries can query any table in the database so long as they return a single value.

The preceding SQL statement returns the following result set.

Name	UnitCost
Rubber Chicken	2.03
Disappearing Penny Magic Trick	2.04
Loaded Dice	1.46
Whoopee Cushion	2.03