Null vs empty field in database

Question

I have database in which data is imported from the other table. if data is empty there. so while importing to here it became null. When i query the columns like, name doesnot starts with 'a', it should return all records whose name doesn't start with 'a'. including NULL/empty column. its returning epty records but not null. But i need null feilds also. I useing hibernate and sqlserver 2005. how to achieve this.?please help. Thanks

Answer 1

I don't use nhibernate. The SQL you need to get it to generate is WHERE name IS NULL OR name NOT LIKE 'a%' though.

Answer 2

Null and Empty are different things.

When you say "Retrieve all the entries that do not start with a" it means that it will retrieve all the entries with something that is not a. Null is not something. Null is nothing. Empty is something.

You should modify your query to add OR IS NULL, to retrieve also the null fields.

Answer 3

From Wiki:

Null is a special marker used in Structured Query Language (SQL) to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model...

...Since Null is not a member of any data domain, it is not considered a "value", but rather a marker (or placeholder) indicating the absence of value. Because of this, comparisons with Null can never result in either True or False, but always in a third logical result, Unknown.

Checkout this discussion.