16.20 - ANY/ALL/SOME Quantifiers - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Functions, Expressions, and Predicates

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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:

EmpNo Name DeptNo JobTitle Salary
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.