ANY / IN Predicate (Value List)

The ANY / IN quantified predicate compares an expression or list of expressions with a list of specified values. The ANY predicate evaluates to TRUE if one or more of the values in the ValueList relate to the expression or list of expressions as indicated by the comparison operator. Similarly, the IN predicate evaluates to TRUE if the expression or list of expressions is equal to one of the values in the list.

SQL syntax

RowValueConstructor {CompOp {ANY | SOME} | IN} ValueList

The syntax for RowValueConstructor: 

RowValueConstructorElement | (RowValueConstructorList) |

The syntax for RowValueConstructorList: 

RowValueConstructorElement[{, RowValueConstructorElement} ... ]

The syntax for RowValueConstructorElement: 

Expression | NULL

The syntax for CompOp: 

{= | <> | > | >= | < | <= }

The syntax for one element in the ValueList (no parentheses necessary): 

ConstantExpression

The syntax for more than one element in the ValueList: 

({ConstantExpression} [,...] )

The syntax for an empty ValueList: 

( )

Parameters

Component Description

Expression

Specifies a value to be obtained. The values in ValueList must be compatible with the expression. See "Expression Specification" for information on syntax.

ConstantExpression

Specifies a constant value or an expression that evaluates to a constant value (such as a number, character string, or date). This includes support for bound values (? or :DynamicParameter), NULL, and calls to functions that return constant values.

=

Is equal to.

<>

Is not equal to.

>

Is greater than.

>=

Is greater than or equal to.

<

Is less than.

<=

Is less than or equal to.

{ANY|

SOME}

The predicate is TRUE if one or more of the values in the ValueList relate to the expression or list of expressions as indicated by the comparison operator. SOME is a synonym for ANY.

ValueList

A list of values that are compared against the RowValueConstructor values.

The ValueList can be an empty list (sometimes generated by SQL generation tools) or consists of ConstantExpression entries.

The ValueList cannot include column references, sequences, subqueries, ROWID values, or ROWNUM values.

The ValueList can be nested if RowValueConstructor is a list. For example: (x, y) not in ((1+1, 2), (abs(-1), 5+1))

Description

  • If X is the value of Expression, and (a,b, ..., z) represents the elements in ValueList, and OP is a comparison operator, then the following is true:

    • X OP ANY (a,b,...,z) is equivalent to X OP a OR X OP b OR...OR X OP z.

  • If X is the value of Expression and (a,b,..., z) are the elements in a Value­List, then the following is true:

    • X IN (a,b,...,z) is equivalent to X = a OR X = b OR...OR X = z.

  • All character data types are compared in accordance with the current value of the NLS_SORT session parameter.

  • See "Numeric Data Types" for information about how TimesTen compares values of different but compatible types.

  • When evaluating an empty ValueList, the result of Expression IN is false.

Examples

Select all item numbers containing orders of 100, 200, or 300 items.

SELECT DISTINCT OrderItems.ItemNumber 
FROM OrderItems
WHERE OrderItems.Quantity = ANY (100, 200, 300)

Get part numbers of parts whose weight is 12, 16, or 17.

SELECT Parts.PartNumber FROM Parts 
WHERE Parts.Weight IN (12, 16, 17);

Get part number of parts whose serial number is '1123-P-01', '1733-AD-01', :SerialNumber or :SerialInd, where :SerialNumber and :SerialInd are dynamic parameters whose values are supplied at runtime. 

SELECT PartNumber FROM Purchasing.Parts 
WHERE SerialNumber
IN ('1123-P-01', '1733-AD-01',:SerialNumber, :SerialInd);

The following example queries an empty select list for IN condition. 

SELECT * FROM t1 WHERE x1 IN ();

The following example uses a list of expressions with IN. 

SELECT * FROM t1 WHERE (x1,y1) IN ((1,2), (3,4));

The next three examples, using ttIsql, show the use of constant expressions. Assume a table T with a single column named X of type NUMBER, with the following data: 

Command> SELECT * FROM t;
< 1 >
< 2 >
< 3 >
< 4 >
< 5 >
5 rows found.

This first example uses constant expressions in a query:

Command> SELECT x FROM t WHERE x in (abs(1-2), TO_NUMBER('1')+2, 3);
< 1 >
< 3 >
2 rows found.

This second example also uses dynamic parameters:

Command> SELECT x FROM t WHERE x = ANY (1+?, 1+3, ?);

Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.

Enter Parameter 1 '_QMARK_1' (NUMBER) > 1
Enter Parameter 2 '_QMARK_2' (NUMBER) > 5
< 2 >
< 4 >
< 5 >
3 rows found.

This third example also uses NULL: 

Command> SELECT x FROM t WHERE x IN (null, 1, 3+1, 2);
< 1 >
< 2 >
< 4 >
3 rows found.

For the next example, consider a table T_DATE with a single column named MYDATE of type DATE, with the following data: 

Command> SELECT * FROM t_date;
< 2013-08-13 00:00:00 >
< 2013-08-14 00:00:00 >
< 2013-08-15 00:00:00 >
3 rows found.

The example uses constant expressions for dates:

Command> SELECT mydate FROM t_date 
         WHERE mydate IN (DATE '2013-08-12'+1, 
                          DATE '2013-08-12'+2);
< 2013-08-13 00:00:00 >
< 2013-08-14 00:00:00 >
2 rows found.

For the next example, consider a table MYCHARS with a single column named COL1 of type VARCHAR2(32), with the following data: 

Command> SELECT * FROM mychars;
< abc >
< def >
< ghi >
3 rows found.

The example uses a function call that returns a constant expression:

Command> SELECT col1 FROM mychars WHERE col1 IN (ltrim('abcdef', 'abc'));
< def >
1 row found.

The following example illustrates the use of a list of expressions for the IN predicate. The query returns the DEPARTMENT_NAME for departments with DEPARTMENT_ID = 240 and LOCATION_ID = 1700.

Note:

The expression on the right side of the IN predicate must be enclosed in double parentheses (( )). 

Command> SELECT department_name FROM departments 
         WHERE (department_id, location_id) IN ((240,1700));
< Government Sales >
1 row found.