Structured Query Language/Quantified Comparison

There are use cases in which an application wants to compare rows or columns not with a fixed value - e.g.: 'WHERE status = 5' - but with a result of a query which is evaluated at runtime. A first example of such dynamic queries are subqueries which return exactly one value: '... WHERE version = (SELECT MAX(version) ...)'. Additionally, sometimes there is the need to compare against a set, which contains multiple values: '... WHERE version (SELECT version FROM t1 WHERE status > 2 ...)'.

To do so, SQL offers some special comparison methods between the table to be queried and the result of the subquery: IN, ALL, ANY/SOME, and EXISTS. They belong to the group of so-called predicates.
 * The IN predicate retrieves rows that correlate to the resulting values of the subquery.
 * The ALL predicate (in combination with <, <=, =, >=, > or <>) retrieves rows which correlate to all values of the subquery (boolean AND operation).
 * The ANY predicate (in combination with <, <=, =, >=, > or <>) retrieves rows which correlate to any value of the subquery (boolean OR operation). The keyword SOME can be used as a synonym for ANY, so you can exchange one against the other.
 * The EXISTS predicate retrieves rows, if the subquery retrieves one or more rows.

IN
The IN predicate - as described in a previous chapter - accepts a set of values or rows.

The subquery selects a lot of values. Therefore it is not possible to use operators like '=' or '>'. They would merely compare single values​​. But the IN predicate handles the situation and compares person.id with every value of contact.person_id regardless of the number of contact.person_id values. This comparisons are mutually linked in the sense of boolean OR operations.

The IN predicate can be negated by adding the keyword NOT.

ALL
The ALL predicate compares every row in the sense of a boolean AND with every value of the subquery. It needs - in contrast to the IN predicate - an additional operator, which is one of: <, <=, =, >=, > or <>.

Common hint: If there is no NULL special marker in the subquery it is possible to replace the ALL predicate by equivalent (and more intuitive) operations:

MySQL hint: Because of query rewrite issues the ONLY_FULL_GROUP_BY mode shall be disabled, e.g. by the command: set sql_mode='ANSI'.

ANY/SOME
The key words ANY and SOME are synonyms, their meaning is the same. Within this Wikibook, we prefer the use of ANY.

The ANY predicate compares every row in the sense of a boolean OR with every value of the subquery. It needs - in contrast to the IN predicate - an additional operator, which is one of: <, <=, =, >=, > or <>.

Common hint: If there is no NULL special marker in the subquery it is possible to replace the ANY predicate by equivalent (and more intuitive) operations:

MySQL hint: Because of query rewrite issues the ONLY_FULL_GROUP_BY mode shall be disabled, e.g. by the command: set sql_mode='ANSI'.

EXISTS
The EXISTS predicate retrieves rows, if the subquery retrieves one or more rows. Meaningful examples typically use a correlated subquery.

The example retrieves all contacts for such persons, which have an ICQ-contact.

The EXISTS predicate can be negated by adding the keyword NOT.