Subqueries and Query Expressions - Outer References
(Page 3 of 12 )
Within the body of a subquery, it’s often necessary to refer to the value of a column in the current row of the main query. Consider once again the query from the previous sections:
Figure 9-3. Subquery comparison test syntax diagram
List the offices where the sales target for the office exceeds the sum of the individual salespeople’s quotas.
SELECT CITY
FROM OFFICES
WHERE TARGET > (SELECT SUM(QUOTA)
FROM SALESREPS
WHERE REP_OFFICE = OFFICE)
The role of the subquery in this SELECT statement is to calculate the total quota
for those salespeople who work in a particular office—specifically, the office currently being tested by the WHERE clause of the main query. The subquery does this by scanning the SALESREPS table. But notice that the OFFICE column in the WHERE clause of the subquery doesn’t refer to a column of the SALESREPS table; it refers to a column of the OFFICES table, which is a part of the main query. As SQL moves through each row of the OFFICES table, it uses the OFFICE value from the current row when it carries out the subquery.
The OFFICE column in this subquery is an example of an outer reference, which is a column name that does not refer to any of the tables named in the FROM clause of the subquery in which the column name appears. Instead, the column name refers to a column of a table specified in the FROM clause of the main query. As the previous example shows, when the DBMS examines the search condition in the subquery, the value of the column in an outer reference is taken from the row currently being tested by the main query.
Subquery Search Conditions A subquery usually appears as part of a search condition in the WHERE or HAVING clause. Chapter 6 described the simple search conditions that can be used in these clauses. In addition, most SQL products offer these subquery search conditions:
- Subquery comparison test. Compares the value of an expression to a single value produced by a subquery. This test resembles the simple comparison test.
- Subquery set membership test. Checks whether the value of an expression matches one of the set of values produced by a subquery. This test resembles the simple set membership test.
- Existence test. Tests whether a subquery produces any rows of query results.
- Quantified comparison test. Compares the value of an expression to each of the set of values produced by a subquery.
The Subquery Comparison Test (=, <>, <, <=, >, >=) The subquery comparison test is a modified form of the simple comparison test, as shown in Figure 9-3. It compares the value of an expression to the value produced by a subquery and returns a TRUE result if the comparison is true. You use this test to compare a value from the row being tested to a single value produced by a subquery, as in this example:
List the salespeople whose quotas are equal to or higher than the target of the Atlanta sales office.
SELECT NAME
FROM SALESREPS
WHERE QUOTA >= (SELECT TARGET
FROM OFFICES
WHERE CITY = 'Atlanta')
NAME
Bill Adams
Sue Smith
Larry Fitch
The subquery in the example retrieves the sales target of the Atlanta office. The value is then used to select the salespeople whose quotas are higher than the retrieved target.
The subquery comparison test offers the same six comparison operators (=, <>, <, <=, >, >=) available with the simple comparison test. The subquery specified in this test must produce a single value of the appropriate data type—that is, it must produce a single row of query results containing exactly one column. If the subquery produces multiple rows or multiple columns, the comparison does not make sense, and SQL reports an error condition. If the subquery produces no rows or produces a NULL value, the comparison test returns NULL (unknown).
Here are some additional examples of subquery comparison tests:
List all customers served by Bill Adams.
SELECT COMPANY
FROM CUSTOMERS
WHERE CUST_REP = (SELECT EMPL_NUM
FROM SALESREPS
WHERE NAME = 'Bill Adams')
COMPANY
Acme Mfg.
Three-Way Lines
List all products from manufacturer ACI where the quantity on hand is above the quantity on hand of product ACI-41004.
SELECT DESCRIPTION, QTY_ON_HAND
FROM PRODUCTS
WHERE MFR_ID = 'ACI'
AND QTY_ON_HAND > (SELECT QTY_ON_HAND
FROM PRODUCTS
WHERE MFR_ID = 'ACI'
AND PRODUCT_ID = '41004')
DESCRIPTION QTY_ON_HAND
| Size 3 Widget | 207 |
| Size 1 Widget | 277 |
| Size 2 Widget | 167 |
The subquery comparison test specified by the SQL1 standard and supported by all of the leading DBMS products allows a subquery only on the right side of the comparison operator. This comparison:
A < (subquery)
is allowed, but this comparison:
(subquery) > A
is not permitted. This doesn’t limit the power of the comparison test, because the operator in any unequal comparison can always be turned around so that the subquery is put on the right side of the inequality. However, it does mean that you must sometimes turn around the logic of an English-language request to get a form of the request that corresponds to a legal SQL statement.
The SQL2 standard eliminated this restriction and allows the subquery to appear on either side of the comparison operator. In fact, the SQL2 standard goes considerably further and allows a comparison test to be applied to an entire row of values instead of a single value. This and other more advanced query expression features of the SQL2 standard are described in the latter sections of this chapter. However, they are not uniformly supported by the current versions of the major SQL products. For portability, it’s best to write subqueries that conform to the SQL1 restrictions, as described previously.
 This article is excerpted from MySQL The Complete Reference by Vikram Vaswani (McGraw-Hill, 2003; ISBN 0072224770). Check it out at your favorite bookstore today. Buy this book now. |
Next: The Set Membership Test (IN) >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
/ 19 
