IN

Transact-SQL Reference

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)

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)

See Also

CASE

Expressions

Functions

Operators

SELECT

WHERE