Structured Query Language/Quantified Comparision

From Wikibooks, open books for an open world
Jump to: navigation, search

← Like Predicate | Rollup Cube →

There are use cases in which an application wants to compare rows or columns not with a fix 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 results in 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 <comparision> (SELECT version FROM t1 WHERE status > 2 ...)'.

To do so, SQL offers some special comparision 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 retrievs rows which correlate to the resulting values of the subquery.
  • The ALL predicate (in combination with <, <=, =, >=, > or <>) retrievs rows which correlate to all values of the subquery (boolean AND operation).
  • The ANY predicate (in combination with <, <=, =, >=, > or <>) retrievs rows which correlate to any value of the subquery (boolean OR operation). The key word SOME can be used as a synonym for ANY, so you can exchange one against the other.
  • The EXISTS predicate retrievs rows, if the subquery retrieves one or more rows.

IN[edit]

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

SELECT * 
FROM   person 
WHERE  id IN 
  (SELECT person_id FROM contact); -- Subquery with potentially a lot of 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 mutally linked in the sense of boolean OR operations.

The IN predicate can be negated by adding the key word NOT.

...
WHERE  id NOT IN
...

ALL[edit]

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

SELECT *
FROM   person 
WHERE  weight > ALL  
  (SELECT weight FROM person WHERE lastname = 'de Winter');

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:

<op> ALL Substitution
< ALL < (SELECT MIN() ...)
<= ALL <= (SELECT MIN() ...)
= ALL '=' or 'IN', if subselect retrieves 1 value.
Else: A single value cannot be equal to different values at the same time. (x = a AND x = b) evaluates to 'false' in all cases.
>= ALL >= (SELECT MAX() ...)
> ALL > (SELECT MAX() ...)
<> ALL '<>' or 'NOT IN', if subselect retrieves 1 value.
Else: 'NOT IN'. (x <> a AND x <> b).

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[edit]

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. He needs - in opposite to the IN predicate - an additional operator, which is one of: <, <=, =, >=, > or <>.

SELECT *
FROM   person 
WHERE  weight > ANY  
  (SELECT weight FROM person WHERE lastname = 'de Winter');

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:

<op> ANY Substitution
< ANY < (SELECT MAX() ...)
<= ANY <= (SELECT MAX() ...)
= ANY '=' or 'IN', if subselect retrieves 1 value.
Else: 'IN'. (x = a OR x = b).
>= ANY >= (SELECT MIN() ...)
> ANY > (SELECT MIN() ...)
<> ANY '<>' or 'NOT IN', if subselect retrieves 1 value.
Else: A single value is always different from two or more different values under an OR conjunction. (x <> a OR x <> b) evaluates to 'true' in all cases.

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[edit]

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

SELECT *
FROM   contact c1
WHERE EXISTS
  (SELECT  *
   FROM    contact c2
   WHERE   c2.person_id = c1.person_id  -- correlation criterion between query and subquery
   AND     c2.contact_type = 'icq');

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

The EXISTS predicate can be negated by adding the key word NOT.

...
WHERE NOT EXISTS
...