IN (T-SQL)

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 Type

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