Equivalences Using IN/NOT IN, NOT, and ANY/ALL/SOME - Teradata VantageCloud Lake

Lake - Working with SQL
Deployment
VantageCloud
Edition
Lake
Product
Teradata VantageCloud Lake
Release Number
Published
February 2025
ft:locale
en-US
ft:lastEdition
2025-11-21
dita:mapPath
jbe1714339405530.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
jbe1714339405530

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

Expression Equivalent Expression
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.

Examples:

Expression Equivalent Expression
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)