TSQL: NULL in the "IN" clause

Question

I have a need to filter records where:

NotificationRead = 0 || NULL --> IF GetRead = 0
NotificationRead = 1 --> IF GetRead = 1
NotificationRead = 0 || 1 || NULL --> IF GetRead = NULL

Here is the query I am using for this:

DECLARE @GetRead BIT
DECLARE @Query VARCHAR(20)

SET @GetRead = NULL

IF @GetRead = 0 SET @Query = '0,NULL'
ELSE IF @GetRead = 1 SET @Query = '1'
ELSE SET @Query = '0,1,NULL'

SELECT * FROM vwNotifications  WHERE NotificationRead IN (@Query)

The query above basically fails when I supply NULL in the IN clause. I do know the reason why thanks to this question.

But if I take an approach as suggested in that question's answer (using NotificationRead IN (@Query) OR NotificationRead IS NULL), I get all records where NotificationRead = NULL when I don't need them for example, when @GetRead = 1

Can you please point me to the right direction here?

You can't use `NULL` in `IN` clase because `NULL`s cannot be compared with other values/variables in the context of `SQL Server`. For exmaple, the following statement will return nothing: `SELECT * FROM [dbo].[TEST] WHERE [TestColumn] IN (NULL)` — gotqn, Apr 25 '14 at 11:33

Mark Byers · Accepted Answer · 2011-10-27T22:42:03.477

6

No, the problem is that you are providing the values as strings.

1 IN (1, 2) -- true.
1 IN ('1, 2') -- false.

Try this instead:

SELECT *
FROM vwNotifications 
WHERE (@GetRead = 0 AND (NotificationRead = 0 OR NotificationRead IS NULL))
OR    (@GetRead = 1 AND NotificationRead = 1)
OR    (@GetRead IS NULL AND (NotificationRead IN (0, 1) OR 
                             NotificationRead IS NULL))

edited Oct 27 '11 at 22:42

answered Oct 27 '11 at 22:36

Mark Byers

811,555
193
1,581
1,452

score 3 · Answer 2 · answered Oct 27 '11 at 22:42

Others may have a better way, but I would move your select into each of the 'if' statements, and modify accordingly.

IF @GetRead = 0
    SELECT * FROM vwNotifications WHERE NotificationRead IS NULL or NotificationRead = 0
ELSE IF @GetRead = 1 
    SELECT * FROM vwNotifications WHERE NotificationRead = 1
ELSE
    SELECT * FROM vwNotifications WHERE NotificationRead IS NULL or NotificationRead in 0, 1

Note: Please don't actually use SELECT * in production code. Name your columns.

score 0 · Answer 3 · answered Jan 07 '21 at 12:40

0

You can add SET ANSI NULLS OS top of query. When added NULL can usable IN (NULL) clause.

SET ANSI NULLS OFF
SELECT * ......
....
WHERE UserId IN (1, 2, NULL)

answered Jan 07 '21 at 12:40

Mahmut Bedir

487
1
5
23

score 0 · Answer 4 · answered Oct 27 '11 at 22:42

A couple of options:

DECLARE @T TABLE (ID INT IDENTITY(1,1), Notify INT)
DECLARE @GetRead BIT
SET @GetRead = 0

INSERT INTO @T
        ( Notify )
VALUES  ( 0  -- Notify - int
          )

INSERT INTO @T
        ( Notify )
VALUES  ( 1  -- Notify - int
          )

INSERT INTO @T
        ( Notify )
VALUES  ( NULL  -- Notify - int
          )

SELECT *
FROM @t t
WHERE @GetRead IS NULL 
    OR (@GetRead = 0 AND (t.Notify =0 OR t.Notify IS NULL))
    OR (@getRead = 1 AND t.Notify = @GetRead)

SELECT *
FROM @t t
WHERE @GetRead IS NULL 
    OR ISNULL(t.notify, 0) = @GetRead

TSQL: NULL in the "IN" clause

4 Answers4