Null value in Database

Question

Null value means

1. No value
2. Inapplicable,unassigned, unknown, or unavailable

Which is true?

Answer 1

NULL is a representation that a field has not had a value set, or has been re-set to NULL. It is not unknown or unavailable.

Note, that when looking for NULL values, do not use '=' in a where clause, use 'is', e.g.:

select * from User where username is NULL;

Not:

select * from User where username = NULL;

Answer 2

From Wikipedia

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, E. F. Codd, SQL Null serves to fulfill the requirement that all true relational database management systems (RDBMS) support a representation of "missing information and inapplicable information". Codd also introduced the use of the lowercase Greek omega (ω) symbol to represent Null in database theory. NULL is also an SQL reserved keyword used to identify the Null special marker.

Answer 3

NULL, in the relational model, means Unknown. It's a mark that appears instead of a value wherever a value can appear in SQL.

Answer 4

Null means nothing, unknown and no value.

It does not mean unavailable or in applicable.

Answer 5

Null is a testable state of a column in a row, but it has no value itself.

By example:

An int can be only ...,0,1,2,3,... and also NULL.

An datetime can be only a valid date... and also NULL.

A bit can be only 0 or 1... and also NULL.

An varchar can be a string... and also NULL.

see the pattern?

You can make a column NOT NULL-able so that you can force a column to take a value.

Answer 6

The NULL SQL keyword is used to represent either a missing value or a value that is not applicable in a relational table

Answer 7

all :-) if you want to add a semantic meaning to your field, add an ENUM

create TABLE myTable
  (
  myfield varchar(50)
  myfieldType enum ('OK','NoValue','InApplicable','Unassigned','Unknown','Unavailable') NOT NULL
  )

Answer 8

It's all about the context in which it's used. A null means there is no value but the reason for this will depend on the domain in which it is being used. In many cases the items you've listed are all valid uses of a null.

Answer 9

Obviously you have the DB definition of what null means, however to an application it can mean anything. I once worked on a strange application (disclaimer- I didn't design it), that used null in a junction table to represent all of the options (allegedly it was designed this way to "save space"). This was a DB design for user and role management.

So null in this case meant the user was in all roles. That's one for daily WTF. :-)

Like many people I tend to avoid using nulls where realistically possible.

Answer 10

It can mean any of those things (and it is not always obvious which), which is one argument against using nulls at all.

See: http://en.wikipedia.org/wiki/Null_(SQL)#Controversy

Answer 11

null indicates that a data value does not exist in the database, thus representing missing information.

Also allows for three-way truth value; true, false and unknown.

Answer 12

The only answer supported by SQL semantics is "unknown." If it meant "no value," then

'Hi there' = NULL

would return FALSE, but it returns NULL. This is because the NULL value in the expression means an unknown value, and the unknown value could very well be 'Hi there' as far as the system knows.