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.
1. 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.
2. The Set Membership Test (in)
The subquery set membership test (IN) is a modified form of the simple set membership test, as shown in Figure 9-4. It compares a single data value to a column of data values produced by a subquery and returns a TRUE result if the data value matches one of the values in the column. You use this test when you need to compare a value from the row being tested to a set of values produced by a subquery. Here is a simple example:
List the salespeople who work in offices that are over target.
SELECT NAME
FROM SALESREPS
WHERE REP_OFFICE IN (SELECT OFFICE
FROM OFFICES
WHERE SALES > TARGET)
NAME
——–
Mary Jones
Sam Clark
Bill Adams
Sue Smith
Larry Fitch
The subquery produces a set of office numbers where the sales are above target. (In the sample database, there are three such offices, numbered 11, 13, and 21.) The main query then checks each row of the SALESREPS table to determine whether that particular salesperson works in an office with one of these numbers. Here are some other examples of subqueries that test set membership:
List the salespeople who do not work in offices managed by Larry Fitch (employee 108).
SELECT NAME
FROM SALESREPS
WHERE REP_OFFICE NOT IN (SELECT OFFICE
FROM OFFICES
WHERE MGR = 108)
NAME
——
Bill Adams
Mary Jones
Sam Clark
Bob Smith
Dan Roberts
Paul Cruz
List all of the customers who have placed orders for ACI Widgets (manufacturer ACI, product numbers starting with 4100) between January and June 1990.
SELECT COMPANY
FROM CUSTOMERS
WHERE CUST_NUM IN(SELECT DISTINCT CUST
FROM ORDERS
WHERE MFR = ‘ACI’
AND PRODUCT LIKE ‘4100%’
AND ORDER_DATE BETWEEN ’01-JAN-90′
AND ’30-JUN-90′)
COMPANY
—————
Acme Mfg.
Ace International
Holm & Landis
JCP Inc.
In each of these examples, the subquery produces a column of data values, and the WHERE clause of the main query checks to see whether a value from a row of the main query matches one of the values in the column. The subquery form of the IN test thus
works exactly like the simple IN test, except that the set of values is produced by a subquery instead of being explicitly listed in the statement.
3. The Existence Test (exists)
The existence test (EXISTS) checks whether a subquery produces any rows of query results, as shown in Figure 9-5. There is no simple comparison test that resembles the existence test; it is used only with subqueries.
Here is an example of a request that can be expressed naturally using an existence test:
List the products for which an order of $25,000 or more has been received.
The request could easily be rephrased as:
List the products for which there exists at least one order in the ORDERS table (a) that is for the product in question and (b) that has an amount of at least $25,000.
The SELECT statement used to retrieve the requested list of products closely resembles the rephrased request:
SELECT DISTINCT DESCRIPTION
FROM PRODUCTS
WHERE EXISTS (SELECT ORDER_NUM
FROM ORDERS
WHERE PRODUCT = PRODUCT_
AND MFR = MFR_ID
AND AMOUNT >= 25000.00)
DESCRIPTION
————-
500-lb Brace
Left Hinge
Right Hinge
Widget Remover
Conceptually, SQL processes this query by going through the PRODUCTS table and performing the subquery for each product. The subquery produces a column containing the order numbers of any orders for the “current” product that are over $25,000. If there are any such orders (that is, if the column is not empty), the EXISTS test is TRUE. If the subquery produces no rows, the EXISTS test is FALSE. The EXISTS test cannot produce a NULL value.
You can reverse the logic of the EXISTS test using the NOT EXISTS form. In this case, the test is TRUE if the subquery produces no rows, and FALSE otherwise.
Notice that the EXISTS search condition doesn’t really use the results of the subquery at all. It merely tests to see whether the subquery produces any results. For this reason, SQL relaxes the rule that “subqueries must return a single column of data” and allows you to use the SELECT * form in the subquery of an EXISTS test. The previous subquery could thus have been written:
List the products for which an order of $25,000 or more has been received.
SELECT DESCRIPTION
FROM PRODUCTS
WHERE EXISTS (SELECT *
FROM ORDERS
WHERE PRODUCT = PRODUCT_ID
AND MFR = MFR_ID
AND AMOUNT >= 25000.00)
In practice, the subquery in an EXISTS test is always written using the SELECT * notation.
Here are some additional examples of queries that use EXISTS:
List any customers assigned to Sue Smith who have not placed an order for over $3000.
SELECT COMPANY
FROM CUSTOMERS
WHERE CUST_REP = (SELECT EMPL_NUM
FROM SALESREPS
WHERE NAME = ‘Sue Smith’)
AND NOT EXISTS (SELECT *
FROM ORDERS
WHERE CUST = CUST_NUM
AND AMOUNT > 3000.00)
COMPANY
—————–
Carter & Sons
Fred Lewis Corp.
List the offices where there is a salesperson whose quota re-presents more than 55 percent of the office’s target.
SELECT CITY
FROM OFFICES
WHERE EXISTS (SELECT *
FROM SALESREPS
WHERE REP_OFFICE = OFFICE
AND QUOTA > (.55 * TARGET))
CITY
——–
Denver
Atlanta
Note that in each of these examples, the subquery includes an outer reference to a column of the table in the main query. In practice, the subquery in an EXISTS test will always contain an outer reference that links the subquery to the row currently being tested by the main query.
4. Quantified Tests (any and all) *
The subquery version of the IN test checks whether a data value is equal to some value in a column of subquery results. SQL provides two quantified tests, ANY and ALL, that extend this notion to other comparison operators, such as greater than (>) and less than (<). Both of these tests compare a data value to the column of data values produced by a subquery, as shown in Figure 9-6.
5. The any Test *
The ANY test is used in conjunction with one of the six SQL comparison operators (=, <>, <, <=, >, >=) to compare a single test value to a column of data values produced by a subquery. To perform the test, SQL uses the specified comparison operator to compare the test value to each data value in the column, one at a time. If any of the individual comparisons yield a TRUE result, the ANY test returns a TRUE result. Here is an example of a request that can be handled with the ANY test:
List the salespeople who have taken an order that represents more than 10 percent of their quota.
SELECT NAME
FROM SALESREPS
WHERE (.1 * QUOTA) < ANY (SELECT AMOUNT
FROM ORDERS
WHERE REP = EMPL_NUM)
NAME
———-
Sam Clark
Larry Fitch
Nancy Angelli
Conceptually, the main query tests each row of the SALESREPS table, one by one. The subquery finds all of the orders taken by the current salesperson and returns a column containing the order amounts for those orders. The WHERE clause of the main query then computes 10 percent of the current salesperson’s quota and uses it as a test value, comparing it to every order amount produced by the subquery. If any order amount exceeds the calculated test value, the ANY test returns TRUE, and the salesperson is included in the query results. If not, the salesperson is not included in the query results. The keyword SOME is an alternative for ANY specified by the ANSI/ISO SQL standard. Either keyword can generally be used, but some DBMS brands do not support SOME.
The ANY test can sometimes be difficult to understand because it involves an entire set of comparisons, not just one. It helps if you read the test in a slightly different way than it appears in the statement. If this ANY test appears:
WHERE X < ANY (SELECT Y …)
instead of reading the test like this:
“where X is less than any select Y…”
try reading it like this:
“where, for some Y, X is less than Y”
When you use this trick, the preceding query becomes:
Select the salespeople where, for some order taken by the salesperson, 10 percent of the salesperson’s quota is less than the order amount.
If the subquery in an ANY test produces no rows of query results, or if the query results include NULL values, the operation of the ANY test may vary from one DBMS to another. The ANSI/ISO SQL standard specifies these detailed rules describing the results of the ANY test when the test value is compared to the column of subquery results:
- If the subquery produces an empty column of query results, the ANY test returns FALSE—there is no value produced by the subquery for which the comparison test holds.
- If the comparison test is TRUE for at least one of the data values in the column, then the ANY search condition returns TRUE—there is indeed some value produced by the subquery for which the comparison test holds.
- If the comparison test is FALSE for every data value in the column, then the ANY search condition returns FALSE. In this case, you can conclusively state that there is no value produced by the subquery for which the comparison test holds.
- If the comparison test is not TRUE for any data value in the column, but it is NULL (unknown) for one or more of the data values, then the ANY search condition returns NULL. In this situation, you cannot conclusively state whether there is a value produced by the subquery for which the comparison test holds; there may or may not be, depending on the “actual” (but currently unknown) values for the NULL
The ANY comparison operator can be very tricky to use in practice, especially in conjunction with the inequality (<>) comparison operator. Here is an example that shows the problem:
List the names and ages of all the people in the sales force who do not manage an office.
It’s tempting to express this query as shown in this example:
SELECT NAME, AGE
FROM SALESREPS
WHERE EMPL_NUM <> ANY (SELECT MGR
FROM OFFICES)
The subquery:
SELECT MGR
FROM OFFICES
obviously produces the employee numbers of the managers, and therefore the query seems to be saying:
Find each salesperson who is not the manager of any office.
But that’s not what the query says! What it does say is this:
Find each salesperson who, for some office, is not the manager of that office.
Of course for any given salesperson, it’s possible to find some office where that salesperson is not the manager. The query results would include all the salespeople and therefore fail to answer the question that was posed! The correct query is:
SELECT NAME, AGE
FROM SALESREPS
WHERE NOT (EMPL_NUM = ANY (SELECT MGR
FROM OFFICES))
NAME AGE
————————
Mary Jones 31
Sue Smith 48
Dan Roberts 45
Tom Snyder 41
Paul Cruz 29
Nancy Angelli 49
You can always turn a query with an ANY test into a query with an EXISTS test by moving the comparison inside the search condition of the subquery. This is usually a very good idea because it eliminates errors like the one just described. Here is an alternative form of the query, using the EXISTS test:
SELECT NAME, AGE
FROM SALESREPS
WHERE NOT EXISTS (SELECT *
FROM OFFICES
WHERE EMPL NUM = MGR)
NAME AGE
————– —-
Mary Jones 31
Sue Smith 48
Dan Roberts 45
Tom Snyder 41
Paul Cruz 29
Nancy Angelli 49
6. The all Test *
Like the ANY test, the ALL test is used in conjunction with one of the six SQL comparison operators (=, <>, <, <=, >, >=) to compare a single test value to a column of data values produced by a subquery. To perform the test, SQL uses the specified comparison operator to compare the test value to each data value in the column, one at a time. If all of the individual comparisons yield a TRUE result, the ALL test returns a TRUE result. Here is an example of a request that can be handled with the ALL test:
List the offices and their targets where all of the salespeople have sales that exceed 50 percent of the office’s target.
SELECT CITY, TARGET
FROM OFFICES
WHERE (.50 * TARGET) < ALL (SELECT SALES
FROM SALESREPS
WHERE REP_OFFICE = OFFICE)
CITY TARGET
———— ————
Denver $300,000.00
New York $575,000.00
Atlanta $350,000.00
Conceptually, the main query tests each row of the OFFICES table, one by one.
The subquery finds all of the salespeople who work in the current office and returns a column containing the sales for each salesperson. The WHERE clause of the main query then computes 50 percent of the office’s target and uses it as a test value, comparing it to every sales value produced by the subquery. If all of the sales values exceed the calculated test value, the ALL test returns TRUE, and the office is included in the query results. If not, the office is not included in the query results.
Like the ANY test, the ALL test can be difficult to understand because it involves an entire set of comparisons, not just one. Again, it helps if you read the test in a slightly different way than it appears in the statement. If this ALL test appears:
WHERE X < ALL (SELECT Y …)
instead of reading it like this:
“where X is less than all select Y…”
try reading the test like this:
“where, for all Y, X is less than Y”
When you use this trick, the preceding query becomes:
Select the offices where, for all salespeople who work in the office, 50 percent of the office’s target is less than the salesperson’s sales.
If the subquery in an ALL test produces no rows of query results, or if the query results include NULL values, the operation of the ALL test may vary from one DBMS to another. The ANSI/ISO SQL standard specifies these detailed rules describing the results of the ALL test when the test value is compared to the column of subquery results:
- If the subquery produces an empty column of query results, the ALL test returns TRUE. The comparison test does hold for every value produced by the subquery; there just aren’t any values.
- If the comparison test is TRUE for every data value in the column, then the ALL search condition returns TRUE. Again, the comparison test holds true for every value produced by the subquery.
- If the comparison test is FALSE for any data value in the column, then the ALL search condition returns FALSE. In this case, you can conclusively state that the comparison test does not hold true for every data value produced by the query.
- If the comparison test is not FALSE for any data value in the column, but it is NULL for one or more of the data values, then the ALL search condition returns NULL. In this situation, you cannot conclusively state whether there is a value produced by the subquery for which the comparison test does not hold true; there may or may not be, depending on the “actual” (but currently unknown) values for the NULL
The subtle errors that can occur when the ANY test is combined with the inequality (<>) comparison operator also occur with the ALL test. As with the ANY test, the ALL test can always be converted into an equivalent EXISTS test by moving the comparison inside the subquery.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.