Oracle® TimesTen InMemory Database SQL Reference Release 11.2.1 Part Number E1307003 


View PDF 
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.
RowValueConstructor {CompOp {ANY SOME}  IN} ValueList
The syntax for RowValueConstructor:
RowValueConstructorElement  (RowValueConstuctorList) 
The syntax for RowValueConstructorList:
RowValueConstructorElement[{, RowValueConstructorElement} ... ]
The syntax for RowValueConstructorElement:
Expression  NULL
The syntax for CompOp:
{=  <>  >  >=  <  <= }
The syntax for more than one element in the ValueList:
({Constant  ?  :DynamicParameter} [,...] )
The syntax for one element in the ValueList not enclosed in parentheses:
Constant  ?  :DynamicParameter
The syntax for an empty ValueList:
( )
The syntax for the ValueList for a list of expressions:
(({Constant  ?  :DynamicParameter} [,...]))
Parameters
Component  Description 

Expression 
Specifies a value to be obtained. The values in ValueList must be compatible with the expression. For information on the syntax of expressions, see "Expression specification". 
=  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 expression's or list of expression's value. The ValueList cannot contain a column reference or a subquery. The ValueList can be nested if the left operand of the ValueList is a list.
Elements of the

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 ValueList
, then the following is true:
X IN (a,b,...,z)
is equivalent to X = a OR X = b OR...OR X = z
.
Character strings are compared according to the ASCII collating sequence for ASCII data.
NULL cannot be specified in ValueList
.
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 '1123P01'
, '1733AD01'
, :SerialNumber
or :SerialInd
, where :SerialNumber
and :SerialInd
are dynamic parameters whose values are supplied at runtime.
SELECT PartNumber FROM Purchasing.Parts WHERE SerialNumber IN ('1123P01', '1733AD01',:SerialNumber, :SerialInd);
To query an empty select list for IN condition:
SELECT * FROM t1 WHERE x1 IN ();
Illustrates the use of a list of expressions with in:
SELECT * FROM t1 WHERE (x1,y1) IN ((1,2), (3,4));
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.