A good advantage of the EXISTS operator is that:
- It is fast because it returns TRUE as soon as it encounters the match.
- Unlike IN operator, it can compare anything with NULL value and return; while the IN operator returns entire query as NULL, the EXISTS operator will return false instead.
A simple example with the EXISTS operator looks like this:
SELECT column1 FROM table1 WHERE EXISTS (SELECT * FROM table2);
Or a more complex example, which searches for all the shows present in one or more cities:
SELECT DISTINCT show FROM shows WHERE EXISTS (SELECT * FROM cities_shows WHERE cities_shows.show = shows.show);