[thelist] Null values in where clauses in SQL Server

[Ben Gustafson]

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
--
[ Picked text/plain from multipart/alternative ]

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.

For example, I ran the query below in Query Analyzer on the database for our
lead-generation site:

	select reg_id, record_type
	from registrant
	where record_type != 2
	order by reg_id

The query results are:

	(430 row(s) affected)

With this query:

	select reg_id, record_type
	from registrant
	where record_type is null or record_type != 2
	order by reg_id

The query results are:

	(1061 row(s) affected)

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. I may fashion this into a tip
after getting input from thelist.

--Ben

________________
Ben Gustafson
Webmaster
Lionbridge Technologies, Inc.
www.lionbridge.com