How to Use Nested SQL Queries that Return a Single Value
Introducing a subquery with one of the six comparison operators (=, <>, <,<=, >, >=) is often useful. In such a case, the SQL expression preceding the operator evaluates to a single value, and the subquery following the operator must also evaluate to a single value. An exception is the case of the quantified comparison operator, which is a comparison operator followed by a quantifier (ANY, SOME, or ALL).
To illustrate a case in which a subquery returns a single value, look at another piece of Zetec Corporation’s database. It contains a CUSTOMER table that holds information about the companies that buy Zetec products.
The CONTACT table holds personal data about individuals at each of Zetec’s customer organizations.
Say that you want to look at the contact information for Olympic Sales, but you don’t remember that company’s CustID. Use a nested query like this one to recover the information you want:
SELECT * FROM CONTACT WHERE CustID = (SELECT CustID FROM CUSTOMER WHERE Company = ‘Olympic Sales’) ;
The result looks something like this:
CustID ContFName ContLName ContPhone ContInfo ------ --------- --------- --------- -------- 118 Jerry Attwater 505-876-3456 Will play major role in additive manufacturing.
You can now call Jerry at Olympic and tell him about this month’s special sale on 3D printers.
When you use a subquery in an “=” comparison, the subquery’s SELECT list must specify a single column (CustID in the example). When the subquery is executed, it must return a single row in order to have a single value for the comparison.
In this example, assume that the CUSTOMER table has only one row with a Company value of ‘Olympic Sales’. The CREATE TABLE statement for CUSTOMER specifies a UNIQUE constraint for Company, and this statement guarantees that the subquery in the preceding example returns a single value (or no value).
Subqueries like the one in this example, however, are commonly used on columns that are not specified to be UNIQUE. In such cases, you must rely on prior knowledge of the database contents for believing that the column has no duplicates.
If more than one customer has a value of ‘Olympic Sales’ in the Company column (perhaps in different states), the subquery raises an error.
If no customer with such a company name exists, the subquery is treated as if it was null, and the comparison becomes unknown. In this case, the WHERE clause returns no row (because it returns only rows with the condition True and filters rows with the condition False or unknown). This would probably happen, for example, if someone misspelled the Companyas ‘Olumpic Sales’.
Although the equal operator (=) is the most common, you can use any of the other five comparison operators in a similar structure. For every row in the table specified in the enclosing statement’s FROM clause, the single value returned by the subquery is compared with the expression in the enclosing statement’s WHERE clause. If the comparison gives a True value, a row is added to the result table.
You can guarantee that a subquery will return a single value if you include an aggregate function in it. Aggregate functions always return a single value. Of course, this way of returning a single value is helpful only if you want the result of an aggregate function.
Suppose you are a Zetec salesperson and you need to earn a big commission check to pay for some unexpected bills. You decide to concentrate on selling Zetec’s most expensive product. You can find out what that product is with a nested query:
SELECT Model, ProdName, ListPrice FROM PRODUCT WHERE ListPrice = (SELECT MAX(ListPrice) FROM PRODUCT) ;
In the preceding nested query, both the subquery and the enclosing statement operate on the same table. The subquery returns a single value: the maximum list price in the PRODUCT table. The outer query retrieves all rows from the PRODUCT table that have that list price.
The next example shows a comparison subquery that uses a comparison operator other than =:
SELECT Model, ProdName, ListPrice FROM PRODUCT WHERE ListPrice < (SELECT AVG(ListPrice) FROM PRODUCT) ;
The subquery returns a single value: the average list price in the PRODUCT table. The outer query retrieves all rows from the PRODUCT table that have a lower list price than the average list price.
In the original SQL standard, a comparison could have only one subquery, and it had to be on the right side of the comparison. SQL:1999 allowed either or both operands of the comparison to be subqueries, and later versions of SQL retain that expansion of capability.