Lists (in and not in)

The in keyword allows you to select values that match any one of a list of values. The expression can be a constant or a column name, and the values list can be a set of constants or a subquery.

For example, to list the names and states of all authors who live in California, Indiana, or Maryland, you can use:

select au_lname, state 
from authors 
where state = "CA" or state = "IN" or state = "MD"

Or, to get the same results with less typing, use in. Separate items following the in keyword by commas and enclose them in parentheses. Use single or double quotes around char, varchar, unichar, unitext, univarchar, and datetime values. For example:

select au_lname, state 
from authors 
where state in ("CA", "IN", "MD")

This is what results from either query:

 au_lname         state 
 -----------      ----- 
 White            CA 
 Green            CA 
 Carson           CA 
 O’Leary          CA 
 Straight         CA 
 Bennet           CA 
 Dull             CA 
 Gringlesby       CA 
 Locksley         CA 
 Yokomoto         CA 
 DeFrance         IN 
 Stringer         CA 
 MacFeather       CA 
 Karsen           CA 
 Panteley         MD 
 Hunter           CA 
 McBadden         CA 
 
(17 rows affected) 

Perhaps the most important use for the in keyword is in nested queries, also called subqueries. For a full discussion of subqueries, see Chapter 5, “Subqueries: Using Queries Within Other Queries.” The following example gives an idea of what you can do with nested queries and the in keyword.

Suppose you want to know the names of the authors who receive less than 50 percent of the total royalties on the books they coauthor. The authors table gives author names and the titleauthor table gives royalty information. By putting the two tables together using in, but without listing the two tables in the same from clause, you can extract the information you need. The following query:

not in finds the authors that do not match the items in the list. The following query finds the names of authors who do not make less than 50 percent of the royalties on at least one book.

select au_lname, au_fname 
from authors 
where au_id not in 
  (select au_id 
   from titleauthor 
   where royaltyper <50) 
au_lname          au_fname 
---------------   ------------ 
White             Johnson 
Carson            Cheryl 
Straight          Dick 
Smith             Meander 
Bennet            Abraham 
Dull              Ann 
Locksley          Chastity 
Greene            Morningstar 
Blotchet-Halls    Reginald 
del Castillo      Innes 
DeFrance          Michel 
Stringer          Dirk 
Karsen            Livia 
Panteley          Sylvia 
Hunter            Sheryl 
McBadden          Heather 
Ringer            Albert 
Smith             Gabriella
 
(18 rows affected)