[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!

shorter urls: <http://shorl.com>

More information about the thelist mailing list