Dear group,

I would like to ask a brief question about NULL values and the IN operator.

The following SQL evaluates to (with ANSI_NULLS ON):

1 IN (1, 2, 3, NULL) --> Evaluates to true (makes perfect sense).
NULL IN (1, 2, 3) --> Evaluates to unknown (makes perfect sense).
4 IN (1, 2, 3) --> Evaluates to false (makes perfect sense).

But¡K

4 IN (1, 2, 3, NULL) --> Evaluates to unknown and not to false.

I read that most major databases do this exactly the same and I actually
was able to find something about this behavior in the PostGreSQL
documentation (I couldn¡¦t find anything about this in the BOL, so I hoped
that the PostGreSQL might apply)

The docs state that for the IN operator:

If there are no equal right-hand values and at least one right-hand row
yields null, the result of the IN construct will be null, not false. This
is in accordance with SQL's normal rules for Boolean combinations of null
values.

So, why does SQL compare to a NULL value (if present) when no matching
values can be found for the right hand of the IN construct?

Kind regards,

Please ignore this message, I posted it to the wrong group.

Regards,