SQL Server - selecting a row doesn't return any NULL values. Why?

Question

Hi. I've got the following SQL table:

CREATE TABLE [dbo].[Test](
    [TestID] [int] NOT NULL,
    [TestNum] [int] NULL,
    [TestReason] [varchar](50) NULL
)

So TestNum an INT which allows NULL values, and I've inserted a whole lot of data into the table, of which some of the rows contain a NULL value for TestNum

If I then run the following query

select *
from Test
where TestNum != 123

The query aboe doesn't return any rows that have a NULL value. I would expect it to return ALL rows EXCEPT those that have the value 123.

Why is this?

I am running this query on a MS-SQL 2000 DB, imported into MS SQL 2005. Does this have any effect? Or is this behaviour standard for all versions of MS SQL Server?

Answer 1

Try

SELECT * FROM Test WHERE TestNum != 123 OR TestNum IS NULL

NULL values are treated differently from other values. It is not possible to compare NULL and 123; they are not equivalent.

Answer 2

NULL represents the value "unknown". For this reason, NULL = NULL is false. If you want to see NULLs, you have to also say "OR TestNum IS NULL".

Answer 3

Take a look here. It's a good explanation of null values.