Last year during one of the SQL Server User Group event brought by Microsoft, they invited Pinal Dave. I am sure you know this guy who wrote many SQL articles in his blog page, blog.sqlauthority.com.
One of the most interesting topics that he shared that morning was the SQL NULL. Sometimes, we have hard times dealing with the null values when we are working on our queries. During one of the interview, I was being asked about the returned result when we execute the below query.
select case when null = null then 'yup' else 'nope' end
I could not remember if I get the answer correct but I did think the query is wrong. According to my search in the Internet, the same question appeared in one of the links where the explanation is as below:-
This query will yield “Nope”, seeming to imply that null is not equal to itself! The reason for this is that the proper way to compare a value to null in SQL is with the is operator, not with =.
It suggests the correct way to compare null value is using the IS operator. You can give a try running it from the SSMS to see the returned result set which I am going to share here as well.
select case when null is null then 'yup' else 'nope' end
Next, I wish to search a bit more about NULL values.
An article from TechNet website, it says below:-
A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.
Anything compared to NULL evaluates to the third value in three-valued logic: UNKNOWN. Interesting as it is, there are many more things about NULL values can be read from the websites. As for now, we know that to check the NULL values, we shall use IS or IS NOT in WHERE clause.