0

I just realized very strange behavior of my MsSql database when filtering empty string != '' or <> '' regarding NULL values.

I have following data in the table

ID Value (table X)
1 (empty string)
2 NULL (no value)
3 text (some real text)

This query select * from X where Value = '' results in:

  • ID: [1]

Both queries select * from X where Value != '' and select * from X where Value <> '' result in:

  • ID: [3]

What I do not understand is, why 2nd query is not returning ID=2?

I know the syntax for checking explicitly on null values where Value IS NULL so I would expect, that 2nd query would behave differently. When checking for non-empty values, I used to write where Value <> '' AND Value IS NOT NULL. From now on I am totally confused...

Lukas K
  • 6,037
  • 4
  • 23
  • 31
  • 3
    Because that is how SQL defines `NULL` values. The queries are behaving correctly and nothing whatsoever is surprising. – Gordon Linoff Feb 03 '21 at 16:44
  • 1
    Different question, same answer: [Sql Server not updating records](https://stackoverflow.com/a/65938681/2029983) – Thom A Feb 03 '21 at 16:57

1 Answers1

2

NULL does not have a string value - or any value at all, whereas ' ' is an empty string. You're looking for not empty strings in your 2nd query. NULL is not an empty string or a string at all. NULL is a "value" of no other type than NULL itself

It is not possible to test for NULL values with comparison operators, such as =, <, or <>. https://www.w3schools.com/sql/sql_null_values.asp

Michał Skorzec
  • 61
  • 7