How to Use IN and NOT IN Predicates in SQL Statements

By Allen G. Taylor

The IN and NOT IN SQL predicates deal with whether specified values (such as OR, WA, and ID) are contained within a particular set of values (such as the states of the United States).

You may, for example, have a table that lists suppliers of a commodity that your company purchases on a regular basis. You want to know the phone numbers of the suppliers located in the Pacific Northwest. You can find these numbers by using comparison predicates, such as those shown in the following example:

SELECT Company, Phone
 FROM SUPPLIER
 WHERE State = 'OR' OR State = 'WA' OR State = 'ID' ;

You can also use the IN predicate to perform the same task, as follows:

SELECT Company, Phone
 FROM SUPPLIER
 WHERE State IN ('OR', 'WA', 'ID') ;

This formulation is a bit more compact than the one using comparison predicates and logical OR. It also eliminates any possible confusion between the logical OR operator and the abbreviation for the state of Oregon.

The NOT IN version of this predicate works the same way. Say that you have locations in California, Arizona, and New Mexico, and to avoid paying sales tax, you want to consider using suppliers located anywhere except in those states. Use the following construction:

SELECT Company, Phone
 FROM SUPPLIER
 WHERE State NOT IN ('CA', 'AZ', 'NM') ;

Using the IN keyword this way saves you a little typing — though (frankly) that isn’t much of an advantage. You can do the same job by using comparison predicates.

You may have another good reason to use the IN predicate rather than comparison predicates, even if using IN doesn’t save much typing: Your DBMS probably implements the two methods differently, and one of the methods may be significantly faster than the other on your system.

You may want to run a performance comparison on the two ways of expressing inclusion in (or exclusion from) a group and then use the technique that produces the quicker result. A DBMS with a good optimizer will probably choose the more efficient method, regardless of which predicate you use.

The IN keyword is valuable in another area, too. If IN is part of a subquery, the keyword enables you to pull information from two tables to obtain results that you can’t derive from a single table. Here’s an example that shows how a subquery uses the IN keyword.

Suppose you want to display the names of all customers who’ve bought the F-35 product in the last 30 days. Customer names are in the CUSTOMER table, and sales transaction data is in the TRANSACT table. You can use the following query:

SELECT FirstName, LastName
 FROM CUSTOMER
 WHERE CustomerID IN
  (SELECT CustomerID
   FROM TRANSACT
   WHERE ProductID = 'F-35'
   AND TransDate >= (CurrentDate - 30)) ;

The inner SELECT of the TRANSACT table nests within the outer SELECT of the CUSTOMER table. The inner SELECT finds the CustomerID numbers of all customers who bought the F-35 product in the last 30 days. The outer SELECT displays the first and last names of all customers whose CustomerID is retrieved by the inner SELECT.