SQL IF NULL From SELECT statement

Question

Why does this give me 1 which is what I was expecting:

IF (SELECT 123) = 123
    PRINT 1
ELSE
    PRINT 2

But this gives me 2 which I was not expecting:

IF (SELECT NULL) = NULL
    PRINT 1
ELSE
    PRINT 2

Answer 1

NULL values are checked by IS NULL

you have to use:

IF (SELECT NULL) IS NULL
    PRINT 1
ELSE
    PRINT 2

from the manual:

To search for column values that are NULL, you cannot use an expr = NULL test. The following statement returns no rows, because expr = NULL is never true for any expression

Answer 2

If you put NULLS OFF

SET ANSI_NULLS OFF
    IF (SELECT NULL) =  NULL
        PRINT 1
    ELSE
        PRINT 2

then you will get PRINT 1