Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
@MikaelEriksson I think it's NOT IN that hes worried about. With NOT IN you get no results from your query –  JNK May 3 '12 at 18:08
    
@JNK. hehe well.. that is a different matter. –  Mikael Eriksson May 3 '12 at 18:09
    
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 3 '12 at 18:11
    
I thought about that @JNK, but that just seems silly.. –  user606723 May 3 '12 at 18:14

3 Answers 3

up vote 5 down vote accepted

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)
share|improve this answer
    
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 3 '12 at 18:26
3  
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 3 '12 at 18:27

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 ...
share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.