T-SQL Tutorial

T-SQL IN and NOT IN


When you use the IN subquery, you are going to generate results that begin in a list of zero or more values. These results are returned by the subquery and used by the outer query.

IN is also used to check whether a specific value matches any other value within a list.
If the IN subquery is used and a list contains NULL, the result of IN and NOT IN will return as UNKNOWN.

By using IN in SQL Server database, you can save some valuable time and streamline the information within a query within a matter of seconds.

Cities table:

CITY_IDNAMESTATE
1New YorkNew York
2Los AngelesCalifornia
3ChicagoIllinois
4San AntonioTexas
5San DiegoCalifornia

States table:

STATE_IDNAME
1Arizona
2California
3Texas
4Michigan

IN Example:

Find the cities that have the state in the states table.
SELECT * FROM cities c
WHERE c.state IN (SELECT s.name FROM states s );

Result:

CITY_IDNAMESTATE
2Los AngelesCalifornia
4San AntonioTexas
5San DiegoCalifornia

NOT IN Example:

Find the cities that NOT have the state in the states table.
SELECT * FROM cities c
WHERE c.state NOT IN (SELECT s.name FROM states s );

Result:

CITY_IDNAMESTATE
1New YorkNew York
3ChicagoIllinois