NULL comparison

Today i had learn a pain lesson of comparing null value with a string.

Lets create a table with 2 column and 2 rows.
Query

DECLARE @Test TABLE(Col1 INT, Col2 INT) INSERT INTO @Test VALUES (1,2),(2,NULL)

Output

The mistake i had done with following query; the original intention is to get all data except row no.2 which happen the be null at column 2 (filtering condition).

Query

SELECT * FROM @Test WHERE Col2 <> 2

Output

There are multiple way to address this issue however i would like to show why the comparison was correct in the way it is.

SELECT CASE WHEN 1 = NULL THEN 1 WHEN 1 <> NULL THEN 2 ELSE 3 END

As you can see, NULL is not comparable, it is not "True" or "False" but "Unknown". For more details please read this.

SET ANSI_NULLS OFF will not help in this scenario as it is not comparing null with null. If will only be helpful if the where clause like
1. WHERE Col2 = NULL

2. WHERE Col2 <> NULL

Solution:
1. Update the source column to other valid value before comparing.

2. Using COALESCE() or ISNULL() to replace null value to other valid value before comparing.
3. Address the null value in the where clause. (WHERE Col2 <> 2 OR Col2 IS NULL)