The IN keyword allows you to select rows 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, you would need this query:
SELECT ProductID, ProductName
FROM Northwind.dbo.Products
WHERE CategoryID = 1 OR CategoryID = 4 OR CategoryID = 5
However, you can get the same results with less typing if you use IN:
SELECT ProductID, ProductName
FROM Northwind.dbo.Products
WHERE CategoryID IN (1, 4, 5)
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 more information about subqueries, see Subquery Fundamentals.
This 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:
USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE au_id IN
(SELECT au_id
FROM titleauthor
WHERE royaltyper < 50)
The results show that several authors fall into the less than 50 percent category.
This query finds the names of authors who do not make less than 50 percent of the royalties on at least one book:
USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE au_id NOT IN
(SELECT au_id
FROM titleauthor
WHERE royaltyper < 50)
NOT IN finds the authors who do not match the items in the values list.
| WHERE | Data Types |