0

When attempting to run a select statement that simply excludes some data, null values are wrongfully (?) being excluded as well.

Using table a:

ID         NAME
1          Foo
2          Bar
3          (null)

The following statement:

select * from a where NAME<>'Foo'

is returning the following:

ID         NAME
2          Bar

Why is ID 3 not returned as a result of this statement? To include the record with the null value, I must change my select statement to

select * from a where (NAME<>'Foo' or NAME is null)
Hennes
  • 65,142
root
  • 3,730

1 Answers1

1

This is how SQL works. (NULL <> 'Foo') returns NULL, which fails the conditional. If you want to include that row, you either need OR NAME IS NULL or you need to consider storing an empty string instead of NULL, if appropriate for your use-case.

http://dba.fyicenter.com/faq/sql_server_2/What_Are_NULL_Values.html has more information on NULL values and boolean operators.

ChrisInEdmonton
  • 8,706