Best way to work around NOT IN NULL issue?

Question

There are plenty of explanations of why NOT IN NULL doesn't work, but I didn't see any solutions. (recent question here: Why does NOT IN with a set containing NULL always return FALSE/NULL?)

I have 6 optional arguments to a stored procedure that are used to filter a certain value of a query. The previous developer did something like this:

IF @var1 IS NULL AND ..... 

    select ...

else

    select ...
    where value in (@var1, ...)

I didn't particularly like this. (The query is quite massive), so I decided to opt for something like this:
(We had the results in a temp table anyway)

IF @var IS NOT NULL OR ...

    delete from #temp where value not in (@var1,...)

But I realized that this won't work.

The only thing I can think to do is to create another temp table that will hold only the not-null values in @var1, etc. (Either using if statements or deleting where is NULL) and then doing a join delete on this table.

Is there a better way?

@MikaelEriksson I think it's NOT IN that hes worried about. With NOT IN you get no results from your query — JNK, May 03 '12 at 18:08
Is it an option to give these variables default values? If you give them a value you know will never be present, say an empty string, or -123456 or something, it should be fine to not change any other code. I hate using magic numbers, though. — JNK, May 03 '12 at 18:11

score 7 · Accepted Answer · answered May 03 '12 at 18:15

Don't know about Sybase but in SQL Server you can do like this.

select *
from yourtable
where value not in (select N
                    from (values (@var1),
                                 (@var2),
                                 (@var3),
                                 (@var4),
                                 (@var5)) T(N)
                    where N is not null)

If you can't use values to create a derived table you can use union all instead.

where value not in (select N
                    from (select @var1 union all
                          select @var2 union all
                          select @var3 union all
                          select @var4 union all
                          select @var5) T(N)
                    where N is not null)

Is there any performance repercussion to doing so many sub-queries? Or will the server be smart enough to realize it doesn't need to be evaluated for each row of yourtable — user606723, May 03 '12 at 18:26
In SQL Server it gets treated as a derived table, so it only gets calculated once. The only time you get a row-by-row execution is if the outer table value is used in the inner table, normally. — JNK, May 03 '12 at 18:27

gbn · Answer 2 · 2012-05-04T07:35:58.353

3

This should work the same.

select ...
from
    yourtable T1
    JOIN
    (
      SELECT value FROM yourtable§
      EXCEPT SELECT @var1
      EXCEPT SELECT @var2
      EXCEPT SELECT @var3
      EXCEPT SELECT @var4
      EXCEPT SELECT @var5
      EXCEPT SELECT @var6
    ) T2 ON T1.value = T2.value 
where ...

edited May 04 '12 at 07:35

answered May 04 '12 at 06:56

gbn

69,809
8
163
243

score 0 · Answer 3 · edited Nov 29 '12 at 14:47

What you've already come up with by yourself:

CREATE TABLE #values (value varchar(8) NOT NULL)

IF @val1 IS NOT NULL INSERT #VALUES (value) SELECT @val1
IF @val2 IS NOT NULL INSERT #VALUES (value) SELECT @val2
IF @val3 IS NOT NULL INSERT #VALUES (value) SELECT @val3
IF @val4 IS NOT NULL INSERT #VALUES (value) SELECT @val4
IF @val5 IS NOT NULL INSERT #VALUES (value) SELECT @val5
IF @val6 IS NOT NULL INSERT #VALUES (value) SELECT @val6    

SELECT *
FROM table tab
WHERE value IN (SELECT value FROM #values)

Or

SELECT *
FROM table tab
WHERE value IN (ISNULL(@val1,'')
               ,ISNULL(@val2,'')
               ,ISNULL(@val3,'')
               ,ISNULL(@val4,'')
               ,ISNULL(@val5,'')
               ,ISNULL(@val6,''))

Adjust the ISNULL(@val,'whatever_really_not_exists').

To understand NULLs better I recommend reading the Sybase documentation about

set ansinull [ on | off ]

or a nice article by Robert Vollman about NULLs.

Best way to work around NOT IN NULL issue?

3 Answers3