CODEWITHSUNDEEP
t-sqlnullwhere-clause
gotqn
gotqn

Reputation: 43666

T-SQL WHERE NOT IN not catching NULL values

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?

Upvotes: 2

Views: 266

Answers (2)

psur
psur

Reputation: 4519

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.

Upvotes: 4

Joao Leal
Joao Leal

Reputation: 5542

Like this:

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

Upvotes: 1

Related Questions