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:
Searches the titleauthor table for all au_ids of authors making less than 50 percent of the royalty on any one book.
Selects from the authors table all the author names with au_ids that match the results from the titleauthor query. The results show that several authors fall into the less than 50 percent category.
select au_lname, au_fname from authors where au_id in (select au_id from titleauthor where royaltyper <50)
au_lname au_fname -------------- ------------ Green Marjorie O’Leary Michael Gringlesby Burt Yokomoto Akiko MacFeather Stearns Ringer Anne (6 rows affected)
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)
