8.5. Using Subqueries with Operators

In this section, we look at examples that demonstrate the use of subqueries with comparison operators. These examples are based on the Room table, which has the following data:

    BLDG  ROOM  CAPACITY OHEAD
    ----- ----- -------- -----
    13    101   85       Y
    36    123   35       N
    58    114   60       NULL
    79    179   35       Y
    79    174   22       Y
    58    112   40       NULL
    36    122   25       N
    36    121   25       N
    36    120   25       N
    58    110   NULL     Y

    (10 row(s) affected)

In previous chapters, you have seen SELECTs with conditions like the following:

    SELECT *
    FROM   Room
    WHERE  capacity = 25

In this example, 25 is a constant and = is a comparison operator. The constant can be replaced by a subquery, and the operator can be any of the comparison operators (=, <>, <, >, <=, or >=). For example, we could devise a query to tell us which classrooms have a below-average capacity by computing the average in a subquery and using a comparison operator, like this:

    SELECT *
    FROM   Room
    WHERE  capacity <
      (SELECT AVG(capacity)
       FROM   Room)

This query produces the following six rows of output, showing six rooms with below-average capacity:

    BLDG   ROOM   CAPACITY OHEAD
    ------ ------ -------- -----
    36     120    25       N
    36     121    25       N
    36     122    25       N
    36     123    35       N
    79     174    22       Y
    79     179    35       Y

    Warning: Null value is eliminated by an aggregate or other SET operation.

    (6 row(s) affected)

The only problem with using subqueries in this fashion is that the subquery must return only one row. If an aggregate function is applied to a table in the subquery in this fashion, you will always get only one roweven if there is a WHERE clause that excludes all rows, the subquery returns one row with a null value. For example, if we were to change the preceding query to the following and force multiple rows in the subquery,

    SELECT *
    FROM   Room
    WHERE  capacity <
      (SELECT AVG(capacity)
       FROM   Room
       WHERE  bldg = 99)

we would get:

    BLDG   ROOM   CAPACITY OHEAD
    ------ ------ -------- -----

    (0 row(s) affected)

We get no rows selected because there is no bldg = 99. If we were to change the query to the following:

     SELECT *
     FROM   Room
     WHERE  bldg =
        (SELECT  bldg
         FROM    Room
         WHERE   capacity > 10)

we would get the following error message:

    BLDG   ROOM   CAPACITY OHEAD
    ------ ------ -------- -----
    Msg 512, Level 16, State 1, Line 1
    Subquery returned more than 1 value. This is not permitted when the subquery follows
    =, !=, <, <=, >, >= or when the subquery is used as an expression.

When using comparison operators, only single values are acceptable from the subquery. Again, to ensure that we get only one row in the subquery and hence a workable query, we can use an aggregate with no GROUP BY or HAVING (to be discussed in Chapter 9).

As with all queries, the caveat to audit the result is always applicable.