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

I've got the following SQL table:

CREATE TABLE [dbo].[Test](
    [Test开发者_如何学CID] [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?

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".

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.