Data sub-queries using EXISTS or NOT EXISTS operators are used in combination with the SELECT statements, and are a good alternative to operators IN, ALL and ANY.

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:

Syntax

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