Exists operator:
Cities table:
CITY_ID | NAME | STATE |
1 | New York | New York |
2 | Los Angeles | California |
3 | Chicago | Illinois |
4 | San Antonio | Texas |
5 | San Diego | California |
States table:
STATE_ID | NAME |
1 | Arizona |
2 | California |
3 | Texas |
4 | Michigan |
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_ID | NAME | STATE |
2 | Los Angeles | California |
4 | San Antonio | Texas |
5 | San Diego | California |
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_ID | NAME | STATE |
1 | New York | New York |
3 | Chicago | Illinois |