CODEWITHSUNDEEP
sql-serverselectnullconditional-statements
Saajid Ismail
Saajid Ismail

Reputation: 8319

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

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 above 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?

Upvotes: 5

Views: 2246

Answers (2)

Anon246
Anon246

Reputation: 1841

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

Upvotes: 14

Liwen
Liwen

Reputation: 937

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.

Upvotes: 2

Related Questions