Subqueries and Query Expressions - The Set Membership Test (IN)
(Page 4 of 12 )
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:
Figure 9-4. Subquery set membership test (IN) syntax diagram
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.
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_ID
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.
Figure 9-5. Existence test (EXISTS) syntax diagram
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 represents 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.
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.
Figure 9-6. Quantified comparison tests (ANY and ALL) syntax diagrams
 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 ANY Test * >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
/ 19 
