Debating Code - [ASP.NET, C#, Sql Server, VB.NET]

Wednesday, July 1, 2009

Strange Behavior of NULL Values in SQL Queries

I found a strange behavior of NULL Values when I have a table values as in the following:

Declare @val table
Id int,
Username varchar(100),
Desg varchar(100),
Level varchar(100)

Insert into @val
select 1,'AAA',NULL,1 union all
select 1,'BBB',NULL,1 union all
select NULL,'CCC','SE',1 union all
select 2,NULL,'PL',4 union all
select 2,'EEE',NULL,5

-- Query 1
select * from @val

-- Query 2
select Id,Username,desg,COUNT(*)
[No. of Employees] from @val
where desg <> 'SE'
group by Id,Username,Desg

-- Query 3
select Id,Username,desg,COUNT(*)
[No. of Employees] from @val
where desg is null or desg <> 'SE'
group by Id,Username,Desg

When we execute the above queries in the Sql Server Management Studio, we would get the following outputs:

Query 1 Result:

Full Table Selection

This is the output of the entire table we just created.

Query 2 Result:

Condition: desg  'SE'

This is the output when I use this condition – desg <> ‘SE’. But it should show the remaining rows that are with NULL values.

Query 3 Result:

Condition: desg IS NULL OR desg  'SE'

when I use this condition – desg IS NULL or desg <> ‘SE’, I am able to get the required output.

Somebody who knows can explain the trick ;-)

Labels: , , , , ,

posted by Karthikeyan @ 10:22 AM


At July 2, 2009 at 11:09 PM , Blogger srinathgnath said...

Null values can't be compared using <, >, <> and != operators. similarly try with '= null', the output will be different what you expect.


Post a Comment

Subscribe to Post Comments [Atom]

<< Home