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 |