It always returns FALSE. Is this feature documented in standards,
and what rationale is behind it?

The standard is that boolean with NULL returns neither TRUE nor FALSE, but
UNKNOWN.  This is by definition how NULLs are supposed to be handled in
relational databases.  SQL Server allows you to SET ANSI_NULL to OFF if you
don't want the standard behaviour.

Joe

Show quoteHide quote

Oops! I meant "comparison with NULL," not boolean.  Sorry...

x NOT IN  (...,NULL)

will always return FALSE or UNKNOWN, never TRUE. This makes some sense if
you consider that the NULL represents an unknown value. If the list contains
an unknown then we can't know for sure that x is not in the list (but we may
know that x IS in the list if X is equal to one of the non-NULL values).
This is the behaviour which is defined in the ANSI/ISO SQL standards.

Don't look for rationale in in SQL's handling of NULLs and three-value
logic. Ultimately, you will be disappointed. Consider the boolean
expression:

(x=x) AND (y=y)

where x is NULL and y is non-NULL. The result is UNKNOWN, not TRUE. This
defies rational explanation. If the value of x is unknown then the one thing
we DO know for sure about x is that it is equal to itself! On the other hand
if the value x is deemed "inapplicable" then the comparison (x=x) is surely
a no-op and the rest of the expression should be evaluated without it:

(y=y) = TRUE         ... (but not in SQL).

Sorry, but the correct answer to your question is "because the SQL Standard
says so". :-(

Yes.  And how else could it work?  IN() isdefined as

The IN() Predicate

If you are an old Pascal programmer you have seen a version of the IN
() predicate.  It is TRUE if a value appears in a list.  It is defined
as a chain of OR-ed predicates, that is:
   a IN (x, y, z)
becomes:
((a = x) OR (a = y) OR (a = z))

However we are in SQL and not Pascal.  If there is a NULL in the list,
things change.

a IN (x, y, NULL)
becomes  ((a = x) OR (a = y) OR (a = NULL))
becomes  ((a = x) OR (a = y) OR UNKNOWN)

There is nothing unusual yet.  The NOT IN () predicate is defined as
the negation of the IN() predicate.

   a NOT IN (x, y, NULL)
becomes   NOT (a IN (x, y, NULL))
becomes   NOT ((a = x) OR (a = y) OR (a = NULL))
becomes   (NOT(a = x) AND NOT(a = y) AND NOT(UNKNOWN)) --DeMorgan's
law
becomes   ((a <> x) AND (a <> y) AND UNKNOWN) -- 3VL rules
becomes   (UNKNOWN) -- 3VL rules

Suddenly this predicate has one and only one value, thanks to the NULL.

I smell a big, fat compiler bug
How do you rate this script
SET ROWCOUNT!
Squeezing out more performance!!
Number of transactions