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:
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
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.