Thursday, March 21, 2024

NOT EQUAL TO (<>, !=) in TSQL does not consider NULL value

Hello guys, 

Last week, I was working on some data validation activity with one of the developers from my company. While working on data validation, we had one surprise and learning also. Maybe you already know this, but if not, I would like to share this with you. 

 To make it easy to understand you, I am explaining scenario with rounded-off values. So have one table, say test with a total of 1000 rows in it. In that we have a one column named ID and allowed for NULL value. In the table test, 400 rows have ID=0, 400 rows have ID=1 and 200 rows have ID value NULL.

Now when we executed 

select count (1) from test where ID=1
 
It gave a output 400 

 but when we executed 

select count (1) from test where ID <>1

 We were expecting output 600, but it gave 400, this was surprise for us. 

By using group by on ID column I checked count for each value of ID and when did math I got to know < > 1 only considers 0 and not NULL. 

 So solution for this is you should use 

select count (1) from test where (ID =0 or ID is NULL)

Let me show this in actual. 1st I am doing select all for reference. we have (0,1,NULL) in ID column.






Now let's see output with ID <> 1 in where clause 




So that's all I wanted to share. I hope this will be helpful and will prevent form having incorrect count when you use <> on column which is allowed with NULL values.




No comments:

Post a Comment