Transact-SQL Reference

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)