T-SQL Tutorial

T-SQL Exists and NOT Exists


Exists operator:

Cities table:

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

States table:

STATE_IDNAME
1Arizona
2California
3Texas
4Michigan

Exists Example:

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

Exists Result:

CITY_IDNAMESTATE
2Los AngelesCalifornia
4San AntonioTexas
5San DiegoCalifornia

NOT Exists Example:

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

NOT Exists Result:

CITY_IDNAMESTATE
1New YorkNew York
3ChicagoIllinois