[thelist] Null values in where clauses in SQL Server
Filip Salomonsson
filip at netdesign.se
Fri Mar 15 09:15:01 CST 2002
Ben Gustafson wrote:
>
> I made an interesting discovery (at least it was a discovery for me; it may
> be a "duh" for others) about using null in Select statements in SQL Server.
> What I found was that if I have a Select statement that has a Where clause
> excluding data from a column that allows nulls, rows where that column has a
> null value are not included in the results.
> After examining the rows brought back by the queries, it becomes apparent
> that the first query was excluding not only the rows where the record_type
> is 2, but also where the record_type is null.
That seems correct. However, if you want the rows where
record_type is null included in you results, you may want
to try the IsNull function:
select reg_id, record_type
from registrant
where IsNull(record_type, 0) != 2
order by reg_id
The syntax for IsNull is (from the T-SQL docs):
ISNULL ( check_expression , replacement_value )
[...]
The value of check_expression is returned if it is not
NULL; otherwise, replacement_value is returned.
Good luck!
/filip
--
shorter urls: <http://shorl.com>
More information about the thelist
mailing list