SQL Series...34

Nested queries that return a single value

Introducing a subquery with one of the six comparison operators (=, <>, <, <=, >, >=) is often useful. In such a case, the expression preceding the opera-tor 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 com-parison 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. It also contains a CONTACT table that holds personal data about individuals at each of Zetec’s customer organizations. The tables are structured as shown in Tables

Table	CUSTOMER Table
Column	Type	Constraints
CustID	INTEGER	PRIMARY KEY
Company	CHAR (40)
CustAddress	CHAR (30)
CustCity	CHAR (20)
CustState	CHAR (2)
CustZip	CHAR (10)
CustPhone	CHAR (12)
ModLevel	INTEGER

Table	CONTACT Table
Column	Type	Constraints
CustID	INTEGER	FOREIGN KEY
ContFName	CHAR (10)
ContLName	CHAR (16)
ContPhone	CHAR (12)
ContInfo	CHAR (50)

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

coordinating

the

wireless

Web.

You can now call Jerry at Olympic and tell him about this month’s special sale on Web-enabled cell phones.

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, I assume that the CUSTOMER table has only one row with a Company value of ‘Olympic Sales’. If the CREATE TABLE statement for CUSTOMER specified a UNIQUE constraint for Company, such a statement guarantees that the subquery in the preceding example returns a single value (or no value). Subqueries like the one in the example, however, are commonly used on columns that are not specified to be UNIQUE. In such cases, you are relying on some other reasons 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 were 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 COMPANY as ‘Olumpic Sales’.

Although the equals 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 to 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 return-ing a single value is helpful only if you want the result of an aggregate function.

Say that 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) ;

This is an example of a nested query where 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 list price less 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 SQL:2003 retains that expansion of capability.