SQL recognizes the quantifiers ANY (or SOME) and ALL. A quantifier allows one or more expressions to be compared with one or more values such as shown by the following generic example.
|IF you specify this quantifier …||THEN the search condition is satisfied if expression LIKE pattern_string … is true for …|
|ALL||every string in the list.|
|ANY||any string in the list.|
The ALL quantifier is the logical statement FOR .
The ANY quantifier is the logical statement FOR Ǝ.
The following table restates this.
|THIS expression …||IS equivalent to this expression …|
|x LIKE ALL ('A%','%B','%C%')||x LIKE 'A%'
AND x LIKE '%B'
AND x LIKE '%C%'
|x LIKE ANY ('A%','%B','%C%')||x LIKE 'A%'
OR x LIKE '%B'
OR x LIKE '%C%'
The following statement selects from the employee table the row of any employee whose job title includes the characters “Pres” or begins with the characters “Man”:
SELECT * FROM Employee WHERE JobTitle LIKE ANY ('%Pres%', 'Man%');
The result of this statement is:
|10021||Smith T||700||Manager||45, 000.00|
|10008||Phan A||300||Vice Pres||55, 000.00|
|10007||Aguilar J||600||Manager||45, 000.00|
|10018||Russell S||300||President||65, 000.00|
|10012||Watson L||500||Vice Pres||56, 000.00|
For the following forms, if you specify the ALL or ANY/SOME quantifier, then the subquery may return none, one, or several rows.
If, however, a quantifier is not used, then the subquery must return either no value or a single value as described in the following table.
|This expression …||Is TRUE when expression matches …|
|expression LIKE (subquery)||the single value returned by subquery.|
|expression LIKE ANY (subquery)||at least one value of the set of values returned by subquery; is false if subquery returns no values.|
|expression LIKE ALL (subquery)||each individual value in the set of values returned by subquery, and is true if subquery returns no values.|