IN
Determines if a given value matches any value in a subquery or a list.
Syntax
test_expression [ NOT ] IN
(
subquery
| expression [ ,...n ]
)
Arguments
test_expression
Is any valid Microsoft® SQL Server™ expression.
subquery
Is a subquery that has a result set of one column. This column must have the same data type as test_expression.
expression [,...n]
Is a list of expressions to test for a match. All expressions must be of the same type as test_expression.
Result Types
Boolean
Result Value
If the value of test_expression is equal to any value returned by subquery or is equal to any expression from the comma-separated list, the result value is TRUE. Otherwise, the result value is FALSE.
Using NOT IN negates the returned value.
Examples
A. Compare OR and IN
This example selects a list of the names and states of all authors who live in California, Indiana, or Maryland.
USE pubs
SELECT au_lname, state
FROM authors
WHERE state = 'CA' OR state = 'IN' OR state = 'MD'
However, you get the same results using IN:
USE pubs
SELECT au_lname, state
FROM authors
WHERE state IN ('CA', 'IN', 'MD')
Here is the result set 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)
B. Use IN with a subquery
This example 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.
USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE au_id IN
(SELECT au_id
FROM titleauthor
WHERE royaltyper < 50)
Here is the result set:
au_lname au_fname
---------------------------------------- --------------------
Green Marjorie
O'Leary Michael
Gringlesby Burt
Yokomoto Akiko
MacFeather Stearns
Ringer Anne
(6 row(s) affected)
C. Use NOT IN with a subquery
NOT IN finds the authors who do not match the items in the values list. This example 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)
Here is the result set:
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)