SQL Series...37

Other correlated subqueries

As noted in a previous article, subqueries introduced by IN or by a comparison operator need not be correlated queries, but they can be.

Correlated subqueries introduced with IN

In the earlier section “Subqueries introduced by the keyword IN,” I discuss how a noncorrelated subquery can be used with the IN predicate. To show how a correlated subquery may use the IN predicate, ask the same question that came up with the EXISTS predicate: What are the names and phone numbers of the contacts at all of Zetec’s customers in California? You can answer this question with a correlated IN subquery:

SELECT *

FROM CONTACT

WHERE ‘CA’ IN

(SELECT CustState

FROM CUSTOMER

WHERE CONTACT.CustID = CUSTOMER.CustID) ;

The statement is evaluated for each record in the CONTACT table. If, for that record, the CustID numbers in CONTACT and CUSTOMER match, then the value of CUSTOMER.CustState is compared to ‘CA’. The result of the sub-query is a list that contains, at most, one element. If that one element is ‘CA’, the WHERE clause of the enclosing statement is satisfied, and a row is added to the query’s result table.

Subqueries introduced with comparison operators

A correlated subquery can also be introduced by one of the six comparison operators, as shown in the next example.

Zetec pays bonuses to its salespeople based on their total monthly sales volume. The higher the volume is, the higher the bonus percentage is. The bonus percentage list is kept in the BONUSRATE table:

MinAmount	MaxAmount	BonusPct
---------	---------	--------
0.00	24999.99	0.
25000.00	49999.99	0.001
50000.00	99999.99	0.002
100000.00	249999.99	0.003
250000.00	499999.99	0.004
500000.00	749999.99	0.005
750000.00	999999.99	0.006

If a person’s monthly sales are between $100,000.00 and $249,999.99, the bonus is 0.3 percent of sales.

Sales are recorded in a transaction master table named TRANSMASTER

TRANSMASTER
-----------
Column	Type	Constraints
------	----	-----------
TransID	INTEGER	PRIMARY KEY
CustID	INTEGER	FOREIGN KEY
EmpID	INTEGER	FOREIGN KEY
TransDate	DATE
NetAmount	NUMERIC
Freight	NUMERIC
Tax	NUMERIC
InvoiceTotal	NUMERIC

Sales bonuses are based on the sum of the NetAmount field for all of a person’s transactions in the month. You can find any person’s bonus rate with a correlated subquery that uses comparison operators:

SELECT BonusPct

FROM BONUSRATE

WHERE MinAmount <=

(SELECT SUM (NetAmount)

FROM TRANSMASTER

WHERE EmpID = 133)

AND MaxAmount >=

(SELECT SUM (NetAmount)

FROM TRANSMASTER

WHERE EmpID = 133) ;

This query is interesting in that it contains two subqueries, making use of the logical connective AND. The subqueries use the SUM aggregate operator, which returns a single value: the total monthly sales of employee number

133. That value is then compared against the MinAmount and the MaxAmount columns in the BONUSRATE table, producing the bonus rate for that employee.

If you had not known the EmpID but had known the person’s name, you could arrive at the same answer with a more complex query:

SELECT BonusPct

FROM BONUSRATE

WHERE MinAmount <=

(SELECT SUM (NetAmount)

FROM TRANSMASTER

WHERE EmpID =

(SELECT EmpID

FROM EMPLOYEE

WHERE EmplName = ‘Coffin’))

AND MaxAmount >=

(SELECT SUM (NetAmount)

FROM TRANSMASTER

WHERE EmpID =

(SELECT EmpID

FROM EMPLOYEE

WHERE EmplName = ‘Coffin’));

This example uses subqueries nested within subqueries, which in turn are nested within an enclosing query, to arrive at the bonus rate for the employee named Coffin. This structure works only if you know for sure that the com-pany has one, and only one, employee whose last name is Coffin. If you know that more than one employee is named Coffin, you can add terms to the WHERE clause of the innermost subquery until you’re sure that only one row of the EMPLOYEE table is selected.