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

## Teradata Vantage™ - SQL Functions, Expressions, and Predicates

Product
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
dita:ditavalPath
dita:id
kby1472250656485

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: