I have in a where clause the following statement COLUMN_1 <> 'O' But the rows that contains NULL on the COLUMN_1 are not take in consideration, it is like they contain the value 'O'
Why is that ?
Thanks in advance.
Asked
Active
Viewed 1,464 times
0
-
SQL Server does not have a null-safe equal operator – juergen d Jul 22 '21 at 16:01
-
Does this answer your question? [SQL is null and = null](https://stackoverflow.com/questions/9581745/sql-is-null-and-null) – Charlieface Jul 22 '21 at 17:39
1 Answers
3
Because NULL does not equal, nor does not not equal anything, including NULL. NULL <> 'O' evaluates to UNKNOWN; which is specifically not TRUE.
If you want to evaluate with NULL values you need to use IS NULL:
WHERE (COLUMN_1 <> 'O' OR COLUMN_1 IS NULL)
This is also documented on both not-equal operator articles:
Not Equal To (Transact SQL) - traditional
Compares two expressions (a comparison operator). When you compare nonnull expressions, the result is TRUE if the left operand is not equal to the right operand; otherwise, the result is FALSE. If either or both operands are NULL, see the topic SET ANSI_NULLS (Transact-SQL).
Not Equal To (Transact SQL) - exclamation
Tests whether one expression is not equal to another expression (a comparison operator). If either or both operands are NULL, NULL is returned. Functions the same as the <> (Not Equal To) comparison operator.
Thom A
- 88,727
- 11
- 45
- 75