15.00 - ANY / ALL / SOME Quantifiers - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

ANY / ALL / SOME Quantifiers

Purpose  

Enables quantification in a comparison operation or IN/NOT IN predicate.

Syntax  

where:

 

Syntax element …

Specifies …

expression

an expression that specifies a value.

comparison_operator

a comparison operator that compares the expression or list of expressions and the literals in the list (Literals syntax) or the subquery (Subquery syntax) to produce a TRUE, FALSE or UNKNOWN result.

For more information on comparison operators, see Chapter 10: “Comparison Operators and Functions.”

[NOT] IN

a predicate that tests the existence of the expression or list of expressions in the list of literals (Literals syntax) or the subquery (Subquery syntax) to produce a TRUE, FALSE, or UNKNOWN result.

For more information on IN/NOT IN, see “IN/NOT IN” on page 943.

literal

a literal value.

subquery

a subquery that selects the same number of expressions as are specified in the expression or list of expressions.

The subquery cannot specify a SELECT AND CONSUME statement.

ANSI Compliance

ANY, SOME, and ALL are ANSI SQL:2011 compliant quantifiers.

ANY/ALL/SOME Quantifiers and Literal Syntax

When a list of literals is used with quantifiers and comparison operations or IN/NOT IN predicates, the results are determined as follows.

 

IF the predicate is …

AND specifies …

THEN the result is true when …

a comparison operation

 

ALL

the comparison of expression and every literal in the list produces true results.

ANY

the comparison of expression and any literal in the list is true.

SOME

IN

 

 

ALL

expression is equal to every literal in the list.

ANY

expression is equal to any literal in the list.

SOME

NOT IN

 

 

ALL

expression is not equal to any literal in the list.

ANY

expression is not equal to every literal in the list.

SOME

For comparison operations, implicit conversion rules are the same as for the comparison operators.

If expression evaluates to NULL, the result is considered to be unknown.

ANY/ALL/SOME Quantifiers and Subquery Syntax

When subqueries are used with quantifiers and comparison operations or IN/NOT IN predicates, the results are determined as follows.

 

IF this quantifier is specified …

AND the predicate is …

THEN the result is …

WHEN …

ALL

 

 

 

a comparison operation

TRUE

 

the comparison of expression and every value in the set of values returned by subquery produces true results.

IN

TRUE

expression is equal to every value in the set of values returned by subquery.

NOT IN

TRUE

expression is not equal to any value in the set of values returned by subquery.

ALL

 

a comparison operation

TRUE

 

subquery returns no values.

 

 

 

IN

NOT IN

ANY

SOME

 

 

 

 

a comparison operation

TRUE

the comparison of expression and at least one value in the set of values returned by subquery is true.

IN

TRUE

expression is equal to at least one value in the set of values returned by subquery.

NOT IN

TRUE

expression is not equal to at least one value in the set of values returned by subquery.

a comparison operation

FALSE

 

 

subquery returns no values.

 

 

 

IN

NOT IN

Equivalences Using ANY/ALL/SOME and Comparison Operators

The following table provides equivalences for the ANY/ALL/SOME quantifiers, where op is a comparison operator:

 

This …

Is equivalent to …

x op ALL (:a, :b, :c)
(x op :a) AND (x op :b) AND (x op :c)
x op ANY (:a, :b, :c)
(x op :a) OR (x op :b) OR (x op :c) 
x op SOME (:a, :b, :c)

Here are some examples:

 

This expression …

Is equivalent to …

x < ALL (:a, :b, :c)
(x < :a) AND (x < :b) AND (x < :c)
x > ANY (:a, :b, :c)
(x > :a) OR (x > :b) OR (x > :c) 
x > SOME (:a, :b, :c)

Equivalences Using ANY/ALL/SOME and IN/NOT IN

The following table provides equivalences for the ANY/ALL/SOME quantifiers, where op is IN or NOT IN:

 

This …

Is equivalent to …1

NOT (x op ALL (:a, :b, :c))
x NOT op ANY (:a, :b, :c)
x NOT op SOME (:a, :b, :c)
NOT (x op ANY (:a, :b, :c))
x NOT op ALL (:a, :b, :c)
NOT (x op SOME (:a, :b, :c))

1
If op is NOT IN, then NOT op is IN, not NOT NOT IN.

Here are some examples:

 

This expression …

Is equivalent to …

NOT (x IN ANY (:a, :b, :c))
x NOT IN ALL (:a, :b, :c)
NOT (x IN ALL (:a, :b, :c))
x NOT IN ANY (:a, :b, :c)
NOT (x NOT IN ANY (:a, :b, :c))
x IN ALL (:a, :b, :c)
NOT (x NOT IN ALL (:a, :b, :c))
x IN ANY (:a, :b, :c)

Example  

The following statement uses a comparison operator with the ANY quantifier to select the employee number, name, and department number of anyone in departments 100, 300, and 500:

 

This Expression …

Is Equivalent to this expression…

SELECT EmpNo, Name, DeptNo
FROM Employee
WHERE DeptNo = ANY (100,300,500) ;
SELECT EmpNo, Name, DeptNo 
FROM Employee
WHERE (DeptNo = 100)
OR (DeptNo = 300)
OR (DeptNo = 500) ;

and

SELECT EmpNo, Name, DeptNo 
FROM Employee
WHERE DeptNo IN (100,300,500) ;

Example  

Here is an example that uses a subquery in a comparison operation that specifies the ALL quantifier:

   SELECT EmpNo, Name, JobTitle, Salary, YrsExp 
   FROM Employee 
   WHERE (Salary, YrsExp) >= ALL 
    (SELECT Salary, YrsExp FROM Employee) ;

Example  

This example shows the behavior of ANY/ALL/SOME.

Consider the following table definition and contents:

   CREATE TABLE t (x INTEGER);
   INSERT t (1);
   INSERT t (2);
   INSERT t (3);
   INSERT t (4);
   INSERT t (5);
 

IF you use this query …

THEN the result is …

SELECT * FROM t WHERE x IN ANY (1,2)
1, 2
SELECT * FROM t WHERE x = SOME (1,2)
1, 2
SELECT * FROM t WHERE x NOT IN ALL (1,2)
3, 4, 5
SELECT * FROM t WHERE NOT (x IN ANY (1,2))
3, 4, 5
SELECT * FROM t WHERE NOT (x = SOME (1,2))
3, 4, 5
SELECT * FROM t WHERE x NOT IN SOME (1, 2)
1, 2, 3, 4, 5
SELECT * FROM t WHERE x NOT = ANY (1, 2)
1, 2, 3, 4, 5
SELECT * FROM t WHERE x IN ALL (1,2)

no rows

SELECT * FROM t WHERE NOT (x NOT IN SOME (1,2))

no rows

SELECT * FROM t WHERE x = ALL (1,2)

no rows

SELECT * FROM t WHERE NOT (x NOT = ANY (1,2))

no rows