ANY Predicate

This is a work in progress, changes may be applied in the future.

Description

In Teradata enables quantification in a comparison operation or IN/NOT IN predicate. The comparison of expression and at least one value in the set of values returned by subquery is true. Please review the following Teradata documentation for more information.

Teradata syntax

{ expression quantifier ( literal [ {, | OR} ... ] ) |
  { expression | ( expression [,...] ) } quantifier ( subquery )
}

Where quantifier:

{ comparison_operator [ NOT ] IN } { ALL |ANY | SOME }

Snowflake syntax

In subquery form, IN is equivalent to = ANY and NOT IN is equivalent to <> ALL. Review the following Snowflake documentation for more infromation.

To compare individual values:

<value> [ NOT ] IN ( <value_1> [ , <value_2> ...  ] )

To compare row constructors (parenthesized lists of values):

( <value_A> [, <value_B> ... ] ) [ NOT ] IN (  ( <value_1> [ , <value_2> ... ] )  [ , ( <value_3> [ , <value_4> ... ] )  ...  ]  )

To compare a value to the values returned by a subquery:

<value> [ NOT ] IN ( <subquery> )

Sample Source Patterns

Sample data

Teradata

Query

CREATE TABLE Employee (
    EmpNo INT,
    Name VARCHAR(100),
    DeptNo INT
);

INSERT INTO Employee (EmpNo, Name, DeptNo)
VALUES (1, 'Alice', 100);

INSERT INTO Employee (EmpNo, Name, DeptNo)
VALUES (2, 'Bob', 300);

INSERT INTO Employee (EmpNo, Name, DeptNo)
VALUES (3, 'Charlie', 500);

INSERT INTO Employee (EmpNo, Name, DeptNo)
VALUES (4, 'David', 200);

INSERT INTO Employee (EmpNo, Name, DeptNo)
VALUES (5, 'Eve', 100);

Snowflake

Query

CREATE OR REPLACE TABLE Employee (
    EmpNo INT,
    Name VARCHAR(100),
    DeptNo INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "01/14/2025",  "domain": "test" }}'
;

INSERT INTO Employee (EmpNo, Name, DeptNo)
VALUES (1, 'Alice', 100);

INSERT INTO Employee (EmpNo, Name, DeptNo)
VALUES (2, 'Bob', 300);

INSERT INTO Employee (EmpNo, Name, DeptNo)
VALUES (3, 'Charlie', 500);

INSERT INTO Employee (EmpNo, Name, DeptNo)
VALUES (4, 'David', 200);

INSERT INTO Employee (EmpNo, Name, DeptNo)
VALUES (5, 'Eve', 100);

Equal ANY pedicate in WHERE clause

Teradata

Input

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

Output

DeptNo
100
500
100
300

Snowflake

Input

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

Output

DeptNo
100
500
100
300

Other comparison operators in WHERE clause

When there are other comparison operators, there equivalent translation is to add a subquery with the required logic.

Teradata

Input

SELECT Name, DeptNo
FROM Employee
WHERE DeptNo < ANY(100,300,500) ;

Output

Name	DeptNo
Eve	100
Alice	100
David	200
Bob	300

Snowflake

Input

SELECT Name, DeptNo
FROM Employee
WHERE DeptNo < ANY 
(SELECT DeptNo
FROM Employee
WHERE DeptNo > 100
OR DeptNo > 300
OR DeptNo > 500);

Output

NAME	DEPTNO
Alice	100
Eve	100
Bob	300
David	200

IN ANY in WHERE clause

Teradata

Input

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

Output

DeptNo
100
500
100
300

Snowflake

Input

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

Output

DeptNo
100
500
100
300

NOT IN ALL in WHERE clause

Teradata

Input

SELECT Name, DeptNo
FROM Employee
WHERE DeptNo NOT IN ALL(100, 200);

Output

Name	DeptNo
Charlie	500
Bob	300

Snowflake

Input

SELECT Name, DeptNo
FROM Employee
WHERE DeptNo NOT IN (100, 200);

Output

Name	DeptNo
Charlie	500
Bob	300

Known Issues

NOT IN ANY in WHERE clause

Teradata

Input

SELECT Name, DeptNo
FROM Employee
WHERE DeptNo NOT IN ANY(100, 200);

Output

Name	DeptNo
Eve	100
Charlie	500
Alice	100
David	200
Bob	300

Snowflake

Input

SELECT Name, DeptNo
FROM Employee
WHERE DeptNo IN (100, 200)
   OR DeptNo NOT IN (100, 200);

Output

Name	DeptNo
Eve	100
Charlie	500
Alice	100
David	200
Bob	300

Related EWIs

No related EWIs.