IN, ALL, ANY Oerator in Teradata

IN operator to have multiple values in the ‘WHERE’ clause. This is an alternative use of having multiple OR. Here is the syntax.

SELECT {*|<col1>}, <col2>, <col3>…

FROM <TABLE name>

WHERE <COLUMN name> [NOT] IN (<list OF VALUES>)

For example, to select employees from a list of department numbers, use the following SQL.

SELECT first_name, last_name

FROM employee

WHERE dept_no IN (100,250,368)

Remember that a result set would be returned even if there is only on matching dept_no found. As mentioned before if you have a NULL operator in the list then there would be no rows returned. To find out rows not matching the list of values, prefix the NOT operator before IN as follows;

SELECT first_name, last_name

FROM employee

WHERE dept_no NOT IN (210,652,555,871)

If you want to use another alternative to this, you can go for ANY, but this has to be used with a comparison operator as follows.

SELECT {*|<col1>}, <col2>, <col3>…

FROM <TABLE name>

WHERE <COLUMN name> = ANY (<list OF VALUES>)

To negate you have to swap the word with ALL and prefix with the NOT operator before the equal sign,

 i.e. NOT = ALL <list of values>