Microsoft® SQL Server® 2012 Bible

124

Part II: Building Databases and Working with Data

Effectively, the IN search condition is the equivalent of multiple EQUALS comparisons ORed

together:

USE AdventureWorks;

SELECT Name

FROM Person.StateProvince

WHERE StateProvinceCode = 'NC'

OR StateProvinceCode = 'WV';

Result:

Name

-----------

North Carolina

West Virginia

The IN operator may also search for a value in a list of columns. The following example

searches for the text 'Ken in either the FirstName and LastName columns:

USE AdventureWorks;

SELECT FirstName, LastName

FROM Person.Person

WHERE 'Ken' IN (FirstName, LastName)

Result:

FirstName LastName

-----------------------

Ken Kwok

Ken Meyer

Ken Myer

Ken Myer

Ken Sanchez

Ken Sanchez

You can combine the IN operator with NOT to exclude certain rows. For example,

WHERE NOT IN ('NC', 'WV') would return all rows except those in North Carolina and

West Virginia:

USE AdventureWorks;

SELECT StateProvinceCode

FROM Person.StateProvince

WHERE StateProvinceCode NOT IN ('NC', 'WV');

Abbreviated Result:

StateProviceCode

-----------

AB

AK

c06.indd 124c06.indd 124 7/30/2012 4:16:03 PM7/30/2012 4:16:03 PM

http://www.it-ebooks.info