IN operator TSQL Tutorial

IN operator:

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 );

IN 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 );

NOT IN Result:

CITY_IDNAMESTATE
1New YorkNew York
3ChicagoIllinois