Overview

The IN() predicate is very natural. It takes a value and sees whether that value is in a list of comparable values. Standard SQL allows value expressions in the list, or for you to use a query to construct the list. The syntax is:

 ::= [NOT] IN  ::=     () ::=    {   }...

The expression NOT IN has the same effect as NOT ( IN ). This pattern for the use of the keyword NOT is found in most of the other predicates.

The expression IN has, by definition, the same effect as = ANY . Most optimizers will recognize this and execute the same code for both expressions. This means that if the is empty, such as one you would get from a subquery that returns no rows, the results will be equivalent to ( = (NULL, ..., NULL)), which is always evaluated to UNKNOWN. Likewise, if the is an explicit list of NULLs, the results will be UNKNOWN. However, please remember that there is a difference between an empty table and a table with rows of all NULLs.

IN() predicates with a subquery can sometimes be converted into EXISTS predicates, but there are some problems and differences in the predicates. The conversion to an EXISTS predicate is often a good way...