What are the differences between Null, Zero and Blank in SQL?

Question

Can someone please explain the differences between Null, Zero and Blank in SQL to me?

Answer 1

Zero is a number value. It is a definite with precise mathematical properties. (You can do arithmetic on it ...)

NULL means the absence of any value. You can't do anything with it except test for it.

Blank is ill-defined. It means different things in different contexts to different people. For example:

• AFAIK, there is no SQL or MySQL specific technical meaning for "blank". (Try searching the online MySQL manual ...)
• For some people "blank" could mean a zero length string value: i.e. one with no characters in it ('').
• For some people "blank" could mean a non-zero length string value consisting of only non-printing characters (SPACE, TAB, etc). Or maybe consisting of just a single SPACE character.
• In some contexts (where character and string are different types), some people could use "blank" to mean a non-printing character value.
• For some people could even use "blank" mean "anything that doesn't show up when you print or display it".
• And then there are meanings that are specific to (for example) ORM mappings.

The point is that "blank" does not have a single well-defined meaning. At least not in (native) English IT terminology. It is probably best to avoid it ... if you want other IT professionals to understand what you mean. (And if someone else uses the term and it is not obvious from the context, ask them to say precisely what they mean!)

---

We cannot say anything generally meaningful about how ZERO / NULL / BLANK are represented, how much memory they occupy or anything like that. All we can say is that they are represented differently to each other .... and that the actual representation is implementation and context dependent.

Answer 2

You may correlate NULL-BLANK-ZERO case by child birth scenario( A real life Example.).

NULL Case: Child is not born yet.

BLANK Case: Child is born but we didn't give any name to him

ZERO Case: We defined it as zero, Child is born but of zero age. See how this data will look in a database table:

Also NULL is a absence of value, where a field having NULL is not allocated any memory, where as empty fields have empty value with allocated space in memory.

Answer 3

Could you be more accurate about blank?

For what I understand of your question: "Blank" is the lack of value. This is a human concept. In SQL, you need to fill the field with a value anyway. So that there is a value which means "no value has been set for this field". It is NULL. If Blank is "", then it is a string, an empty one. Zero: well, Zero is 0 ... It is a number.

To sum up:

NULL --> no value set

Blank ("") --> empty string

Zero --> Number equal to 0

Please, try to be more accurate next time you post an answer on Stack!

If I were you, I would check some resources about it, for example: https://www.tutorialspoint.com/sql/sql-null-values.htm

Answer 4

NULL means it does not have any value not even garbage value. ZERO is an integer value. BLANK is simply a empty String value.