I have updated the content of this entry, and shared a work-related example where I compared a value with a NULL value. This entry dated back in year 2017 after I attended an event organized by Microsoft. One of the sessions they have invited Pinal Dave as the speaker. 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
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. I give a try running it from the SQL Server Management Studio (SSMS) to see the returned result set which I am going to share here as well.
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, I know that to check the NULL values, I shall use IS or IS NOT in WHERE clause.
A work-related example that I want to share is I used ISNULL() to check a column. I used this column for my WHERE clause condition. I realized that it would give me wrong answer when this column is NULL. It is because I was comparing a NULL column greater than LAST_MODIFIED_DTTM column.
Hence, I learned that it would be good to add ISNULL(column_name, GETDATE()) or ISNULL(column_name, ‘1900-01-01’) to return result.
LiYenzWordPress 