• Hugo Kornelis (10/28/2010)


    Good question, but a small (yet significan) mistake in the explanation.

    NULL does not represent the value 'UNKNOWN'.

    Hugo, perhaps you could ask M$ to update the books online to match your opinion?

    <Quote from BOL>

    When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against any other value. This occurs if either an expression is compared to the literal NULL, or if two expressions are compared and one of them evaluates to NULL. For example, the following comparison always yields UNKNOWN when ANSI_NULLS is ON:

    Copyytd_sales > NULL

    The following comparison also yields UNKNOWN any time the variable contains the value NULL:

    ytd_sales > @MyVariable

    <Quote from BOL>

    Youre comment is correct with one small but significant correction.

    When ANSI_NULLS is OFF NULL is not value UNKNOWN.