CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
oshirowanen
oshirowanen

Reputation: 15965

SQL IF NULL From SELECT statement

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

Upvotes: 4

Views: 410

Answers (4)

chresse
chresse

Reputation: 5815

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

Upvotes: 3

mohan111
mohan111

Reputation: 8865

If you put NULLS OFF 

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

then you will get PRINT 1

Upvotes: 3

Jesuraja
Jesuraja

Reputation: 3844

You cannot check NULL using =. You need to use IS NULL like the following

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

Upvotes: 1

TechDo
TechDo

Reputation: 18659

You cant check NULL with =. For that IS has to be used. Ex:

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

Upvotes: 1

Related Questions