SQL Series...33

Subqueries introduced by the keyword NOT IN

Just as you can introduce a subquery with the IN keyword, you can do the opposite and introduce it with the NOT IN keyword. In fact, now is a great time for Zetec management to make such a query. By using the query in the preceding section, Zetec management found out what products not to sell. That is valuable information, but it doesn’t pay the rent. What Zetec manage-ment really wants to know is what products to sell. Management wants to emphasize the sale of products that don’t contain monitors. A nested query featuring a subquery introduced by the NOT IN keyword provides the requested information:

SELECT Model

FROM COMP_USED

WHERE Model NOT IN

(SELECT Model

FROM COMP_USED

WHERE CompID IN

(SELECT CompID

FROM COMPONENT

WHERE CompType = ‘Monitor’)) ;

This query produces the following result:

Model

-----

PX3040

PB3050

PX3040

PB3050

A couple things are worth noting here:

This query has two levels of nesting. The two subqueries are identical to the previous query statement. The only difference is that a new enclosing statement has been wrapped around them. The enclosing statement takes the list of products that contain monitors and applies a SELECT introduced by the NOT IN keyword to that list. The result is another list that contains all product models except those that have monitors.

The result table does contain duplicates. The duplication occurs because a product containing several components that are not monitors has a row in the COMP_USED table for each component. The query cre-ates an entry in the result table for each of those rows.

In the example, the number of rows does not create a problem because the result table is short. In the real world, however, such a result table may have hundreds or thousands of rows. To avoid confusion, you need to eliminate the duplicates. You can do so easily by adding the DISTINCT keyword to the query. Only rows that are distinct (different) from all previously retrieved rows are added to the result table:

SELECT DISTINCT Model

FROM COMP_USED

WHERE Model NOT IN

(SELECT Model

FROM COMP_USED

WHERE CompID IN

(SELECT CompID

FROM COMPONENT

WHERE CompType = ‘Monitor’)) ;

As expected, the result is as follows:

Model

-----

PX3040

PB3050