The standard Not In predicate is supported with a single expression, a list of literal constants or a subquery. That is it may be used to test whether or not an expression equals another expression, is one of a list of values or is returned from a query. The Not In predicate generates SQL in one of several forms shown below.
expression NOT IN expression expression NOT IN (literal list) expression IN (literal TO literal) expression NOT IN (subquery) (expression list) NOT IN (subquery)
When dragging a Not In operator into a variable, the following tree element is created.
A column, expression, literal or SQL Element List can be moved into the (empty -Not In Test Value) branch of the tree. One or more literals or a single column, expression or Subquery element can be moved into the Expressions folder within the tree. To represent a literal TO literal expression, a SQL Text with Arguments element can be selected from the Other category with text set equal to <P1> TO <P2>, and numeric literals or parameters selected as arguments.
The use of the ALL predicate with the NOT IN predicate is optional. That is, NOT IN (…), NOT IN ALL (…) and <> ALL (…) are all equivalent. If desired, drop an ALL SQL element onto the Expressions folder and then use the Expressions folder under the ALL node.
There are no special properties for the Not In operator.