The IN keyword allows you to select values that match any one of a list of values. For example, without IN, if you want a list of the names and states of all authors who live in California, Indiana, or Maryland, use this query:
SELECT au_lname, state FROM authors WHERE state = 'CA' OR state = 'IN' OR state = 'MD'
However, you get the same results with less typing if you use IN:
SELECT au_lname, state
FROM authors
WHERE state IN ('CA', 'IN', 'MD')
You get the same 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 row(s) affected)
The items following the IN keyword must be separated by commas and be enclosed in parentheses.
Perhaps the most important use for the IN keyword is in nested queries, also referred to as subqueries. (For a full discussion of subqueries, see Using Subqueries) The following example gives some idea of what you can do with nested queries and the IN keyword.
Suppose that you want to know the names of 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 together using IN, you can extract the information you need without listing the two tables in the same FROM clause.
The following query finds all au_ids in the titleauthor table for authors who make less than 50 percent of the royalty on any one book, and then selects from the authors table all 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 row(s) affected)
NOT IN finds the authors who do not match the items in the values 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 |
Dean |
Smith |
Meander |
Bennet |
Abraham |
Dull |
Ann |
Locksley |
Charlene |
Greene |
Morningstar |
Blotchet-Halls |
Reginald |
del Castillo |
Innes |
DeFrance |
Michel |
Stringer |
Dirk |
Karsen |
Livia |
Panteley |
Sylvia |
Hunter |
Sheryl |
McBadden |
Heather |
Ringer |
Albert |
(17 row(s) affected)
When you select values using the IN keyword, be sure to put single quotation marks around all char, varchar, text, datetime, and smalldatetime data.