SQL IF NULL From SELECT Statement

Question

Why does this give me 1 what I expected:

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

But this gives me 2, which I did not expect:

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

Answer 1

Values

NULL checked using IS NULL

you should 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 the expr = NULL test. The following statement does not return rows, because expr = NULL will never be true for any expression

Answer 2

If you set NULLS OFF

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

then you get PRINT 1

Answer 3

NULL =. IS. :

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

Answer 4

NULL =. IS NULL, .

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