0

This might be the situation in other databases as well but when you make the following query

SELECT * FROM MyTbl WHERE MyColumn != 'Foo'

then any record where MyColumn is, say, 'Bar' is fetched but not where MyColumn is NULL. I assume this is expected behavior and that there is a reason behind it and I'd like to know why.

Is NULL considered to be equal to 'Foo' or is it just not expected to be part of the condition because the condition (NULL != 'Foo') seems to be true.

Maffelu
  • 2,018
  • 4
  • 24
  • 35
  • 7
    `NULL` means _undefined_. A comparison with something unspecific must return _unequal_. – Tim Schmelter Jan 14 '13 at 13:47
  • http://www.simple-talk.com/sql/learn-sql-server/sql-and-the-snare-of-three-valued-logic/ – Bort Jan 14 '13 at 13:51
  • Very similar to question : http://stackoverflow.com/questions/1843451/why-does-null-null-evaluate-to-false-in-sql-server?rq=1 – msmucker0527 Jan 14 '13 at 13:52
  • 1
    When you compare with `NULL` the DB just _don't know_ the answer which is not the same as _No_. So it decides to omit these "Maybe"-records. – Tim Schmelter Jan 14 '13 at 14:00

4 Answers4

5

In DB logic, NULL means that there is simply no defined data in this field. It's considered neither equal or different to anything. You have to filter on it explicitly if you want to fetch the relevant lines :

SELECT * FROM MyTbl WHERE MyColumn != 'Foo' OR MyColumn IS NULL

See Wikipedia.

xlecoustillier
  • 16,183
  • 14
  • 60
  • 85
1

In SQL Server works three-state logic, which mean that NULL = NULL UNKNOWN which treats as FALSE

Hamlet Hakobyan
  • 32,965
  • 6
  • 52
  • 68
0

In all relational databases, the null value is not equal to anything, including itself.

You would not be able to find the rows with null values for MyColumn even if your query was "select * from MyTbl where MyColumn = null". The only way to get them would be "select * from MyTble where MyColumn is null"

For a detailed explanation, see http://en.wikipedia.org/wiki/Null_(SQL)

GreyBeardedGeek
  • 29,460
  • 2
  • 47
  • 67
0

Yep, as Tim commented, NULL values are unknown. SQL Server isn't going to make an assumption about a NULL value in order to make a match. You can check out more info about how SQL Server processes NULLs.

Matt
  • 2,982
  • 1
  • 22
  • 23