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)
I'm afraid that the reason is simply that the rules were set in an adhoc fashion (like quite many other "features" of the ISO SQL standard) at a time when SQL aggregations and their connection with mathematics were less understood than they are now (*).
It's just one of the extremely many inconsistencies in the SQL language. They make the language harder to teach, harder to learn, harder to understand, harder to use, harder to whatever you want, but that's just the way things are. The rules cannot be changed "cold" and "just like that", for obvious reasons of backward compatibility (If the ISO committee publishes a final version of the standard, and vendors then set out to implement that standard, then those vendors will not appreciate it very much if in a subsequent version, the rules are changed such that existing (compliant) implementations of the former version of the standard "automatically fail to comply" the new version ...)
(*) It is now better understood that aggregations over an empty set behave more consistently if they systematically return the identity value (= what you call the 'neutral element') of the underlying binary operator at hand. That underlying binary operator for COUNT and SUM is addition, and its identity value is zero. For MIN and MAX, that identity value is the highest and lowest value of the type at hand, respectively, if the concerned types are finite. Cases like averaging, harmonic means, medians, etc. are extremely intricate and exotic in this respect, though.
Best Answer
Take a look at PSOUG's notes on NULL. As Fabricio Araujo hinted, NULL is not really a value like the number 4 or string 'bacon strips'. In fact, NULL is untyped in the SQL language, which is why you cannot validly use it in an equality comparison. You need the special
IS [NOT] NULL
syntax to check if a value is NULL or not.