• Add OR nm IS NULL

    Null never matches any value, so you cannot have null returned from a in or not in.

    Your IN is expanded to this:

    NOT (nm='a')

    Now, the logical expression NULL = 'literal value' returns UNKNOWN (not true, not false). NOT UNKNOWN is UNKNOWN. A where clause predicate will only return the row if the predicate returns TRUE. Hence, since both NULL = 'literal value' and NOT (NULL = 'literal value') both return UNKNOWN, not true, the row with null will never be returned from an IN or NOT IN.

    The only logical expression that can return TRUE in the presence of NULL is the IS NULL/IS NOT NULL expression. Hence you will need to add that to your where clause if you want to see null values.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass