Subqueries and Query Expressions - Subqueries in the HAVING Clause *

Although subqueries are most often found in the WHERE clause, they can also be used in the HAVING clause of a query. When a subquery appears in the HAVING clause, it works as part of the row group selection performed by the HAVING clause. Consider this query with a subquery:

List the salespeople whose average order size for products manufactured by ACI is higher than overall average order size.

  SELECT NAME, AVG(AMOUNT)
       FROM SALESREPS, ORDERS
  WHERE EMPL_NUM = REP
     AND MFR = 'ACI'
  GROUP BY NAME
HAVING AVG(AMOUNT) > (SELECT AVG(AMOUNT)
                                                     FROM ORDERS)

NAME              AVG (AMOUNT)

----------       ----------------

Sue Smith        $15,000.00
Tom Snyder     $22,500.00

Figure 9-7 shows conceptually how this query works. The subquery calculates the overall average order size. It is a simple subquery and contains no outer references, so SQL can calculate the average once and then use it repeatedly in the HAVING clause. The main query goes through the ORDERS table, finding all orders for ACI products, and groups them by salesperson. The HAVING clause then checks each row group to see whether the average order size in that group is bigger than the average for all orders, calculated earlier. If so, the row group is retained; if not, the row group is discarded. Finally, the SELECT clause produces one summary row for each group, showing the name of the salesperson and the average order size for each.

You can also use a correlated subquery in the HAVING clause. Because the subquery is evaluated once for each row group, however, all outer references in the correlated subquery must be single-valued for each row group. Effectively, this means that the outer reference must either be a reference to a grouping column of the outer query or be contained within a column function. In the latter case, the value of the column function for the row group being tested is calculated as part of the subquery processing.

If the previous request is changed slightly, the subquery in the HAVING clause becomes a correlated subquery:

List the salespeople whose average order size for products manufactured by ACI is at least as big as that salesperson’s overall average order size.

SELECT NAME,  AVG(AMOUNT)
     FROM SALESREPS, ORDERS

 WHERE EMPL_NUM = REP
      AND MFR = 'ACI'
  GROUP BY NAME, EMPL_NUM
HAVING AVG(AMOUNT) >= (SELECT AVG(AMOUNT)
                                                       FROM ORDERS
                                                   WHERE REP = EMPL_NUM)

                  NAME            AVG(AMOUNT)

                  --------         ---------------

In this new example, the subquery must produce the overall average order size for the salesperson whose row group is currently being tested by the HAVING clause. The subquery selects orders for that particular salesperson, using the outer reference EMPL_NUM. The outer reference is legal because EMPL_NUM has the same value in all rows of a group produced by the main query.

Figure 9-7.  Subquery operation in the HAVING clause

This chapter has described subqueries, which allow you to use the results of one query to help define another query. Before moving on to the advanced query facilities of the SQL2 specification, let’s summarize subqueries:

• A subquery is a “query within a query.” Subqueries appear within one of the subquery search conditions in the WHERE or HAVING clause.

• When a subquery appears in the WHERE clause, the results of the subquery are used to select the individual rows that contribute data to the query results.

• When a subquery appears in the HAVING clause, the results of the subquery are used to select the row groups that contribute data to the query results.

• Subqueries can be nested within other subqueries.

• The subquery form of the comparison test uses one of the simple comparison operators to compare a test value to the single value returned by a subquery.

• The subquery form of the set membership test (IN) matches a test value to the set of values returned by a subquery.

• The existence test (EXISTS) checks whether a subquery returns any values.

• The quantified tests (ANY and ALL) use one of the simple comparison operators to compare a test value to all of the values returned by a subquery, checking to see whether the comparison holds for some or all of the values.

• A subquery may include an outer reference to a table in any of the queries that contain it, linking the subquery to the current row of that query.

Figure 9-8 shows the final version of the rules for SQL query processing, extended to include subqueries. It provides a complete definition of the query results produced by a SELECT statement.

Figure 9-8.  SQL query processing rules (final version)

The SQL queries described thus far in Chapters 6–9 are the mainstream capabilities provided by most SQL implementations. The combination of features they represent— column selection in the SELECT clause, row selection criteria in the WHERE clause, multitable joins in the FROM clause, summary queries in the GROUP BY and HAVING clauses, and subqueries for more complex requests—give the user a powerful set of data retrieval and data analysis capabilities. However, database experts have pointed out many limitations of these mainstream query capabilities, including these:

• No decision making within queries. Suppose you wanted to generate a two-column report from the sample database showing the name of each sales office and either its annual sales target or its year-to-date sales, whichever is larger. With standard SQL query features, this is hard to do. Or suppose you had a database that kept track of sales by quarter (four columns of data for each office) and wanted to write a program that displayed offices and their sales for

• a specific (user-supplied) quarter. Again, this program is more difficult to write using standard SQL queries. You must include four separate SQL queries (one for each quarter), and the program logic must select which query to run, based on user input. This simple case isn’t too difficult, but in a more general case, the program could become much more complex.
• Limited use of subqueries. The simplest example of this limitation is the SQL1 restriction that a subquery can appear only on the right side of a comparison test in a WHERE clause. The database request “list the offices where the sum of the salesperson’s quotas is greater than the office target” is most directly expressed as this query:

SELECT OFFICE
 FROM OFFICES
WHERE (SELECT SUM(QUOTA)
                  FROM SALESREPS
             WHERE REP_OFFICE = OFFICE) > TARGET

But this isn’t a legal SQL1 statement. Instead, you must turn the inequality around:

• SELECT OFFICE
     FROM OFFICES
  WHERE TARGET > (SELECT SUM(QUOTA)
                                       FROM SALESREPS
                                  WHERE REP_OFFICE = OFFICE)

In this simple example, it isn’t hard to turn the logic around, but the restriction is a nuisance at best, and it does prevent you from comparing the results of two subqueries, for example.

• Limited-row expressions. Suppose you wanted to list the suppliers, item numbers, and prices for a set of products that are substitutes for one another. Conceptually, these are a set of products whose identification (a manufacturer-ID/product-ID pair) matches one of a set of values, and it would be natural to write the query using a set membership test as:

 SELECT MFR_ID, PRODUCT_ID, PRICE
      FROM PRODUCTS
 WHERE (MFR_ID, PRODUCT_ID) IN (('ACI',41003),('BIC',41089), …)

The SQL1 standard doesn’t permit this kind of set membership test. Instead, you must construct the query as a long set of individual comparisons, connected by ANDs and ORs.

•  Limited-table expressions. SQL allows you to define a view like this one for large orders:

SELECT *
    FROM PRODUCTS
WHERE AMOUNT > 10000

and then use the view as if it were a real table in the FROM clause of a query to find out which products, in which quantities, were ordered in these large orders:

SELECT MFR, PRODUCT, SUM(QTY)
   FROM BIGORDERS
GROUP BY MFR,  PRODUCT

But the SQL1 standard doesn’t allow a subquery in this position in the WHERE clause. Yet clearly, the DBMS should be able to determine the meaning of this query, since it must basically do the same processing to interpret the BIGORDERS view definition.

As these examples show, the SQL1 standard and mainstream DBMS products that implement to this level of the standard are relatively restrictive in their permitted use of expressions involving individual data items, sets of data items, rows, and tables. The SQL2 standard includes a number of advanced query capabilities that are focused on removing these restrictions and making the SQL language more general. The spirit of these SQL2 capabilities tends to be that a user should be able to write a query expression that makes sense and have the query expression be a legal SQL query. Because these SQL2 capabilities constitute a major expansion of the language over the SQL1 standard, most of them are required only at a full level of the standard.

The simplest extended query capabilities in SQL2 are those that provide more data manipulation and calculation power involving individual data values (called scalars in the SQL2 standard). Within the SQL language, individual data values tend to have three sources:

• The value of an individual column within an individual row of a table

• A literal value, such as 125.7 or ABC

• A user-supplied data value, entered into a program

In this SQL query:

 SELECT NAME, EMPL_NUM, HIRE_DATE, (QUOTA * .9)
    FROM SALESREPS
 WHERE (REP_OFFICE = 13) OR TITLE = 'VP SALES'

the column names NAME, EMPL_NUM, HIRE_DATE, and QUOTA generate individual data values for each row of query results, as do the column names REP_OFFICE and TITLE in the WHERE clause. The numbers .9 and 13 and the character string “VP SALES” similarly generate individual data values. If this SQL statement appeared within an embedded SQL program (described in Chapter 17), the program variable office_num might contain an individual data value, and the query might appear as:

  SELECT NAME, EMPL_NUM, HIRE_DATE, (QUOTA * .9)
           FROM SALESREPS
  WHERE (REP_OFFICE = :office_num) OR TITLE = 'VP SALES'

As this query and many previous examples have shown, individual data values can be combined in simple expressions, like the calculated value QUOTA*.9. To these basic SQL1 expressions, SQL2 adds the CAST operator for explicit data type conversion, the CASE operator for decision making, the NULLIF operation for conditionally creating a NULL value, and the COALESCE operator for conditionally creating non-NULL values.

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 CAST Expression (SQL2) >>
 

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

Comments On: Subqueries and Query Expressions

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