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

chresse · Accepted Answer · 2014-07-17 10:03:28Z

3

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

edited Jul 17, 2014 at 10:03

answered Jul 17, 2014 at 9:32

chresse

5,8873 gold badges35 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mohan111 · Accepted Answer · 2014-07-17 09:37:58Z

3

If you put NULLS OFF

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

then you will get PRINT 1

answered Jul 17, 2014 at 9:37

mohan111

8,8854 gold badges35 silver badges61 bronze badges

Comments

TechDo · Accepted Answer · 2014-07-17 09:32:46Z

1

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

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

answered Jul 17, 2014 at 9:32

TechDo

18.7k3 gold badges55 silver badges64 bronze badges

Comments

Jesuraja · Accepted Answer · 2014-07-17 09:36:53Z

1

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

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

answered Jul 17, 2014 at 9:36

Jesuraja

3,8524 gold badges26 silver badges49 bronze badges

Collectives™ on Stack Overflow

SQL IF NULL From SELECT statement

4 Answers 4

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Related