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?

Answer 1

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)

Answer 2

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 ...

Answer 3

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.