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

Teradata Vantage™ - SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1145-162K
Language
English (United States)

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

This … 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))

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)