#IN and #NOTIN treat missing values differently from the way = and <> (and their variants: <, <=, >=, and >) do. Consider two records containing the database field X: the first record has the value 42 inX; the second record is missing a value for X (i.e., in ProVal expressions, its value is #MV). The following table summarizes the behavior of = and <>:

X	X=42	X=X	X=#MV	X<>42	X<>X	X<>#MV
42	1	1	0	0	0	1
#MV	#MV	#MV	1	#MV	#MV	0

The explanation for the behavior is this: = and <> generally act like + and *, producing #MV in the result if either argument contains a missing value. However, if one of the arguments is #MV itself (as opposed to a database field containing #MV), the operator simply tests whether the other argument is missing. (And note that = and <> are commutative, so X=#MV is the same as #MV=X.)

#IN and #NOTIN, on the other hand, treat #MV exactly like any other number, regardless of whether it occurs in the left or right argument. The result of #IN and #NOTIN is always 1’s and 0’s, never #MV.

X	X #IN 42	X #IN X	X #IN #MV	X #NOTIN 42	X #NOTIN X	X #NOTIN #MV
42	1	1	0	0	0	1
#MV	0	1	1	1	0	0