The Difference between Null and Empty String

There are a lot of posts out there telling you why one particular language or another treats null strings (or does not treat them) the same as an empty string.

Remember, conceptually a null is an unknown value.  So let's say we have a middle name field, and two people in the table look like this:

Bob <null> Wilson
Theron <empty string> Shan

Bob's middle name is not known.  He never provided it, we do not know if he has one or not.  Theron, on the other hand HAS NO MIDDLE NAME.  We know for a fact that we asked him, and he does not have one.

 Likewise, with numbers, dates, guids.  If you had a <null> number of widgets, that is different from 0 widgets.  Again, <null> means unknown.  0 means zero.

So, queries.

select * from customers where middle_name <> "Revinal" 

Theron's middle name is clearly NOT Revinal.  But we don't know that Bob's isn't (his middle name is unknown).  This query would return Theron, but since null cannot be evaluated, we would not see Bob.

select * from customers where middle_name = "Revinal" 

 This would return neither Bob nor Theron.  Theron's middle name is NOT Revinal, but we don't know if Bob's is or not.

THIS IS HOW COUNTS GET OFF.  If you made one list of names that are Revinal (0) and another that are not (1), you would conclude that there is one name in the database.  This is because <null> does not satisfy either condition.

To write queries that treat nulls as empty strings, look up nvl (Oracle) and isNull (SQL Server).

So the next time someone tells you that a null is the same as an empty string, tell them they are full of an unknown number of prunes.
...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.