17.10 - Equivalences Using IN/NOT IN, NOT, and ANY/ALL/SOME - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Functions, Expressions, and Predicates

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

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

This usage … Is equivalent to …
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))
NOT (x op (:a, :b, :c)) x NOT op (:a, :b, :c)

In the equivalences, 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)
NOT (x IN (:a, :b, :c)) x NOT IN (:a, :b, :c)
NOT (x NOT IN (:a, :b, :c)) x IN (:a, :b, :c)

Syntax 2: expression IN and NOT IN subquery

This syntax for IN and NOT IN is correct in either of the following two forms: