CoDebate

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

1 Comments:

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