Subqueries and Query Expressions - What Is a Subquery?

Figure 9-1 shows the form of a SQL subquery. The subquery is enclosed in parentheses, but otherwise it has the familiar form of a SELECT statement, with a FROM clause and optional WHERE, GROUP BY, and HAVING clauses. The form of these clauses in a subquery is identical to that in a SELECT statement, and they perform their normal functions when used within a subquery. There are, however, a few differences between a subquery and an actual SELECT statement:

• In the most common uses, a subquery must produce a single column of data as its query results. This means that a subquery almost always has a single select item in its SELECT clause.

• The ORDER BY clause cannot be specified in a subquery. The subquery results are used internally by the main query and are never visible to the user, so it makes little sense to sort them anyway.

Figure 9-1.  Basic subquery syntax diagram    

Column names appearing in a subquery may refer to columns of tables in the main query. These outer references are described in detail later in the “Outer References” section.

• In most implementations, a subquery cannot be the UNION of several different SELECT statements; only a single SELECT is allowed. (The SQL2 standard allows much more powerful query expressions and relaxes this restriction, as described later in the section “Advanced Queries in SQL2.”)

Subqueries are most frequently used in the WHERE clause of a SQL statement. When a subquery appears in the WHERE clause, it works as part of the row selection process. The very simplest subqueries appear within a search condition and produce a value that is used to test the search condition. Here is an example of a simple subquery:

List the salespeople whose quota is less than 10 percent of the companywide sales target.

SELECT NAME
FROM SALESREPS
WHERE QUOTA < (.1 * (SELECT SUM(TARGET) FROM OFFICES))

NAME

Bob Smith

In this case, the subquery calculates the sum of the sales targets for all of the offices to determine the companywide target, which is multiplied by 10 percent to determine the cutoff sales quota for the query. That value is then used in the search condition to check each row of the SALESREPS table and find the requested names. In this simple case, the subquery produces the same value for every row of the SALESREPS table; the QUOTA value for each salesperson is compared to the same companywide number. In fact, you could carry out this query by first performing the subquery, to calculate the cutoff quota amount ($275,000 in the sample database), and then carry out the main query using this number in a simple WHERE clause:

                WHERE REP_OFFICE = OFFICE)

CITY

Chicago

 Los Angeles

In this (more typical) case, the subquery cannot be calculated once for the entire query. The subquery produces a different value for each office, based on the quotas of the salespeople in that particular office. Figure 9-2 shows conceptually how SQL carries out the query. The main query draws its data from the OFFICES table, and the WHERE clause selects which offices will be included in the query results. SQL goes through the rows of the OFFICES table one by one, applying the test stated in the WHERE clause.

Figure 9-2.  Subquery operation in the WHERE clause

The WHERE clause compares the value of the TARGET column in the current row to the value produced by the subquery. To test the TARGET value, SQL carries out the subquery, finding the sum of the quotas for salespeople in the current office. The subquery produces a single number, and the WHERE clause compares the number to the TARGET value, selecting or rejecting the current office based on the comparison. As the figure shows, SQL carries out the subquery repeatedly, once for each row tested by the WHERE clause of the main query.

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: Outer References >>
 

More MS SQL Server Articles
More By McGraw-Hill/Osborne

Comments On: Subqueries and Query Expressions

>>> Be the FIRST to comment on this article!