T-SQL WHERE NOT IN not catching NULL values

Question

This is my code:

DECLARE @TABLE TABLE
(
    RecordID BIGINT,
    RecordValue NVARCHAR(10)
)

INSERT INTO @TABLE(RecordID,RecordValue)
VALUES  (1,'100')
       ,(2,NULL)
       ,(3,'200')
       ,(4,NULL)
       ,(5,'200')

SELECT RecordID,RecordValue
FROM @TABLE
WHERE RecordValue NOT IN ('200')

OUTPUT:

1 100

Desire OUTPUT:

1 100

2 NULL

4 NULL

How can I get the 'NULL' values too?

Actually, my the sql statement is generated by a lot of procedures and functions, I am limited in doing operations like using ISNULL function. The final statement is in the following format:

WHERE RecordValue NOT IN ('CSV...') 
WHERE RecordValue IN ('CSV...') 
or combinatnation from both 
RecordValue NOT IN ('CSV...') AND RecordValue IN ('CSV...')

Is there a way to get NULL values too in this way?

psur · Accepted Answer · 2012-09-11 11:54:00Z

4

Try this:

SELECT RecordID,RecordValue 
FROM @TABLE 
WHERE RecordValue NOT IN ('200') OR RecordValue IS NULL

When value is NULL then comparison with it is always NULL so you have to take it into account.

answered Sep 11, 2012 at 11:54

psur

4,53930 silver badges37 bronze badges

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

1 Comment

gotqn Over a year ago

Thanks. I managed to change the procedures in order to generated this code.

Joao Leal · Accepted Answer · 2012-09-11 11:53:51Z

1

Like this:

SELECT RecordID,RecordValue
FROM @TABLE
WHERE ISNULL(RecordValue,'') NOT IN ('200')

answered Sep 11, 2012 at 11:53

Joao Leal

5,5521 gold badge15 silver badges23 bronze badges

Collectives™ on Stack Overflow

T-SQL WHERE NOT IN not catching NULL values

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related