Why Sql Conditions (not in , not like , !=) not working on varchar value of table

Question

I have a table with two columns and containing two records.

SQL table structure:

CREATE TABLE #TEMP_TEST
(
    ID bigint NOT NULL,
    DESCRIPTION varchar(500) NULL
) ON [PRIMARY]

INSERT INTO #TEMP_TEST VALUES(1, 'obsolete')
INSERT INTO #TEMP_TEST VALUES(2, NULL)

SELECT * FROM #TEMP_TEST

Total dummy records in #TEMP_TEST table

ID      | DESCRIPTION |
--------+-------------+
1       | obsolete    |
2       | NULL        |

Query with these conditions =, like, in are working fine.

SELECT * FROM #TEMP_TEST LC2 WHERE LC2.DESCRIPTION ='obsolete'
SELECT * FROM #TEMP_TEST LC2 WHERE LC2.DESCRIPTION LIKE 'obsolete'
SELECT * FROM #TEMP_TEST LC2 WHERE LC2.DESCRIPTION IN ('obsolete')

Working fine with these results:

ID      | DESCRIPTION |
--------+-------------+
1       | obsolete    |

Problem :

Query with these conditions !=, not like, not in are not working fine.

SELECT * FROM #TEMP_TEST LC2 WHERE LC2.DESCRIPTION != 'obsolete'
SELECT * FROM #TEMP_TEST LC2 WHERE LC2.DESCRIPTION NOT LIKE 'obsolete'
SELECT * FROM #TEMP_TEST LC2 WHERE LC2.DESCRIPTION NOT IN ('obsolete')

Results:

ID      | DESCRIPTION |
--------+-------------+

Expected results:

ID      | DESCRIPTION |
--------+-------------+
2       | NULL        |

What should I do to get expected result?

Kindly review and give a required answer or comment if any further condition is required for getting expected result.

Answer 1

This is because of three valued logic in SQL Server. Predicate can evaluate to:

• true
• false
• unknown

When you are comparing data in SQL Server you should always think about possible NULLs. Consider these predicates:

where 1=1 => evaluates to true
where 2=1 => evaluates to false
where 1=null => evaluates to unknown
where null=null => evaluates to unknown

So comparing NULL to any value, even with NULL evaluates to UNKNOWN. Now you should know howWHEREclause works. It returns rows where predicate evaluates toTRUE` only!

In your case the predicate:

WHERE  LC2.DESCRIPTION != 'obsolete'

will evaluate to:

obselete != obselete => false
obselete != null => unknown

So there are no rows where predicate evaluates to TRUE and you get nothing as a result.

As for the question what should you do, you can do the following:

WHERE ISNULL(LC2.DESCRIPTION, 'not absolete') != 'obsolete'

but here your predicate is not SARGable and you will not gain from indexes if any is created for LC2.DESCRIPTION column.

The standard way is to use OR:

WHERE LC2.DESCRIPTION != 'obsolete' OR LC2.DESCRIPTION IS NULL 

Answer 2

You can use OR and It will work even if ANSI_NULLS is ON

SELECT * FROM #TEMP_TEST LC2 WHERE  LC2.DESCRIPTION != 'obsolete' OR LC2.DESCRIPTION IS NULL
SELECT * FROM #TEMP_TEST LC2 WHERE  LC2.DESCRIPTION NOT LIKE 'obsolete' OR LC2.DESCRIPTION IS NULL
SELECT * FROM #TEMP_TEST LC2 WHERE  LC2.DESCRIPTION NOT IN ('obsolete') OR LC2.DESCRIPTION IS NULL