Odd SQL behavior, I'm wondering why this works the way it does.

Posted by Matthew Vines on Stack Overflow See other posts from Stack Overflow or by Matthew Vines
Published on 2010-03-25T20:32:11Z Indexed on 2010/03/25 20:43 UTC
Read the original article Hit count: 223

Filed under:
|
|

Consider the following Transact sql.

DECLARE @table TABLE(val VARCHAR(255) NULL)

INSERT INTO @table (val) VALUES('a')
INSERT INTO @table (val) VALUES('b')
INSERT INTO @table (val) VALUES('c')
INSERT INTO @table (val) VALUES('d')
INSERT INTO @table (val) VALUES(NULL)

select val 
from @table
where val not in ('a') 

I would expect this to return

b, c, d, NULL

but instead it returns

b, c, d

Why is this the case? Is NULL not evaluated? Is NULL somehow in the set 'a'?

© Stack Overflow or respective owner

Related posts about sql

Related posts about tsql