What is null in a database?

I was recently asked about the nuances of null in a database, in essence, the question was “What is null in a database?”

Null is the absence of something. It means there is nothing there, nothing, nada, zilch, not zero, not an empty string, just absolutely nothing.

Null can create a lot of confusion and problems when designing and querying databases. It is often mis-used, such as in “state” columns. If something has a state of being, that state really shouldn’t be null, because that would be a lack of a state and thus a lack of the thing itself. In the case in question, the problem was that rows in the database had null for state, when in reality they should have had a state and the column should have been “not null”.

Another major issue is that you cannot index on NULL values, as you’ll see below. (Note: I believe SQL Server now allows indexing on NULL.)

The query being used had the following where clause:

 where processed!=1

and it wasn’t picking up the correct count of rows, because new rows were inserted with processed=null, instead of processed=”something for new or un-processed”. The solution was one of the following

 where processed!=1 or processed is null

or

 where nvl(processed,-1)!=1

So, to help, I came up with the following examples of what null is and isn’t:

null is not equal to 0 (zero)
null is not equal to ’ ‘
null is not equal to ’’
null is not equal to ’null’
null is not equal to true
null is not equal to false

and here is the kicker,

null is not equal to null

Seem strange? Yes, but null is not equal to or not equal to, greater than or less than, null. Any arithmetic or equality operation on null results in null. The only thing null is, is null. The only thing you can get from operating on null and anything else, is null. Null is more of a concept, similar to Infinity, although quite the opposite. You can see this below:

SQL> select 1 from dual where null=null;
no rows selected

Since any operation or comparison with null returns null, the above query returns no rows; however, if you ask if null is null, then you can see what it is:

SQL> select 1 from dual where null is null;
         1
----------
         1

So, I hope that helps you understand what null is, or isn’t.

Advertisements

One response to “What is null in a database?

  1. Pingback: What is null in a database? | Jed's

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s