ALL / NOT IN Predicate (Value List)

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

SQL syntax

RowValueConstructor {CompOp ALL | NOT 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 the syntax of expressions.
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.
ALL	The predicate is TRUE if all the values in the ValueList relate to the expression or list of expressions as indicated by the comparison operator.
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 ALL (a,b,...,z) is equivalent to X OP a AND X OP b AND...AND 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 NOT IN (a,b,...,z) is equivalent to NOT (X IN (a,b,...,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.

• NOT IN or NOT EXISTS with ALL can be specified in an OR expression.

• IN and EXISTS with ALL can be specified in an OR expression.

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

• If RowValueConstructorList is specified only the operators = and <> are allowed.

Examples

To query an empty select list for a NOT IN condition:

SELECT * FROM t1 WHERE x1 NOT IN ();

For ValueList examples, see the Examples section in "ANY / IN Predicate (Value List)".