SQL Series...32

Subqueries introduced by the keyword IN

One form of a nested query compares a single value with the set of values returned by a SELECT. It uses the IN predicate with the following syntax:

SELECT column_list

FROM table

WHERE expression IN (subquery) ;

The expression in the WHERE clause evaluates to a value. If that value is IN the list returned by the subquery, then the WHERE clause returns a True value, and the specified columns from the table row being processed are added to the result table. The subquery may reference the same table referenced by the outer query, or it may reference a different table.

I use Zetec’s database to demonstrate this type of query. Assume that there is a shortage of computer monitors in the computer industry. When you run out of monitors, you can no longer deliver products that include them. You want to know which products are affected. Enter the following query:

SELECT Model

FROM COMP_USED

WHERE CompID IN

(SELECT CompID

FROM COMPONENT

WHERE CompType = ‘Monitor’) ;

SQL processes the innermost query first, so it processes the COMPONENT table, returning the value of CompID for every row where CompType is ‘Monitor’. The result is a list of the ID numbers of all monitors. The outer query then compares the value of CompID in every row in the COMP_USED table against the list. If the comparison is successful, the value of the Model column for that row is added to the outer SELECT’s result table. The result is a list of all product models that include a monitor. The following example shows what happens when you run the query:

Model

-----

CX3000

CX3010

CX3020

MB3030

MX3020

MX3030

You now know which products will soon be out of stock. It’s time to go to the sales force and tell them to slow down on promoting these products.

When you use this form of nested query, the subquery must specify a single column, and that column’s data type must match the data type of the argu-ment preceding the IN keyword.