How can I explain the difference between NULL and zero?

Working on a problem that uses the percent change formula:

``````percent change =  100 * [(new value - old value) / old value]
``````

How would I explain the difference if `new value or old value = NULL`, rather than `0` to someone who might not be a programmer?

My boss is wondering why there is an empty string in the TextBox rather than a value, because we have the old value, but not the new value.

-

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

"How would I explain the difference"? In which language? Some languages throw (or raise) an exception. Some will return a NULL result. Some depend on the details of how NULL is implemented. You have to be much more specific. –  S.Lott Feb 13 '12 at 19:40
Change the textbox to say "Data unavailable" instead of the empty string. –  Craig Feb 13 '12 at 20:05
@ZJR - so display the message in red below the box. Point is, "NULL" or an empty string have about as much use in a UI as the memory address of an object. –  detly Feb 14 '12 at 2:32
By analogy, 0 Degrees temperature, and No temperature reading are different situations... –  NWS Feb 14 '12 at 9:12
I can't answer now that it's protected, but I always explain that `NULL` is a state, not a value - it means you don't know the value. For SQL I normally use the party analogy to demonstrate. –  JNK Feb 14 '12 at 13:44

To explain to a boss the difference between "zero" and "null":

"Zero" is a value. It is the unique, known quantity of zero, which is meaningful in arithmetic and other math.

"Null" is a non-value. It is a "placeholder" for a data value that is not known or not specified. It is only meaningful in this context; mathematical operations cannot be performed on null (the result of any such operation is undefined, and therefore also generally represented as null).

For example, as in the comments: "What is your yearly income?" is a question requiring a numeric answer. "0" is a perfectly valid answer for someone who does not work and has no investment income. If the user does not enter a value at all, they don't necessarily make no money; they just didn't want to tell your software how much (or little) they make. It's an unknown, not specified; therefore, to allow the software to continue, you specify the "null" placeholder for that data field within the software. That's technically valid from a data perspective; whether it's valid at the business level depends on whether an actual numeric value (even zero) is required in order to perform a mathematical operation (such as calculation of taxes, or comparison with thresholds determining benefits).

In computers, virtually any operation on a variable containing null will result either in null or in an error condition, because since one of the variable's values is not known, the result of the expression cannot be known. The equivalent of performing math on null would be if I asked you "What's five plus the number I'm thinking of right now?". It's impossible for you to give a definite answer because you don't know the number I'm thinking of. An operation on zero, except for dividing by it, is usually valid and will return another known, unique value.

-
"Placeholder" is a helpful description. If you conducted a survey and asked "how old are you?", and someone skipped that question, you should NOT represent that answer as 0. That's making a (probably false) assumption. The correct representation would be `NULL`. –  Nathan Long Feb 13 '12 at 21:24
@Maxood - It depends on the programming language. I would consider an integer variable that is allowed to be null ( using the ? syntax ) and is set to null to have no known integer value. The expected type is known, so to say its not defined, one might consider not 100% correct. It is correct to say that null has no value or type. I personally would say that null or NULL type is not defined depending on the context and the language your using. In C# it would simply mean that the reference to the object would be undefined. –  Ramhound Feb 14 '12 at 15:00

Boss-speak is always tough...

Zero is a number so you can do things with it.

Null is a unicorn. It doesn't exist so you can't do anything at all with it.

-
+1 for the unicorn! I must add : very good explanation. –  BЈовић Feb 13 '12 at 20:20
I may have to start putting in my queries: `NVL(somefield,'UNICORN')` ;) –  FrustratedWithFormsDesigner Feb 13 '12 at 20:50
Then the boss might just ask "Why don't we make it 0 so we can do things with it? Null sounds useless." –  AlbeyAmakiir Feb 13 '12 at 22:19
@AlbeyAmakiir - The correct response to such a boss would be "While we're pretending to know things we don't know, why don't we make it 1 million? That's nice and big." –  Nathan Long Feb 14 '12 at 4:05
I can't believe this got 90+ up-votes. I'm gonna start putting unicorns in all my answers. –  Morons Feb 14 '12 at 14:06

Just rephrase the equations into sentences:

"What is the percentage change if you start with an unknown value and you end up with 150?"

and

"What is the percentage change if you start with 85 and you end up with some unknown value?"

Of course, the answer to both is "It can't be calculated, because one of the critical pieces of the calculation is missing." That's the essence of 'null'.

It should be easy then to figure out the equivalent sentences with zero, and see how they are fundamentally different:

"What is the percentage change if you start with an 0 and you end up with 150?"

and

"What is the percentage change if you start with 85 and you end up with 0?"

Although the answers might not make a lot of sense (with zeroes), at least it can be calculated. With null, the calculation is not even possible.

-
+1 "Unknown" is definitely a great explanation, and is easy to explain in sentences. –  Scott Rippey Feb 13 '12 at 22:51
@Ramhound Here's what I was trying to get at, in the context of the original question: "Unknown" represents the "placeholder" in KiethS' answer, which I think is a great explanation. To a non-programmer boss, it means there may be a value out there somewhere, but we don't have it (in the database, system, form, etc.). Is there a value? We don't have enough information to know, one way or another. Data is missing. That's a subtly different meaning than nothing, which infers there is no value to be had. Depending on the context of the discussion, either explanation can be correct. –  Eric King Jan 24 '13 at 21:26

When talking to you boss, just use `0` for zero, and `?` for null. It correctly captures that it's a placeholder for something but that you don't know what it is.

-

• `0` is the answer to "how much liquid is there in an empty bottle?".
• `NULL` is the answer to "what is the content of an empty bottle?".

Or if you imagine a childless man:

• the number of his children is `0`
• his oldest child is `NULL`

The basic difference is that `0` is about measurable quantity, while `NULL` is about existence. Being a quantity, `0` represents something, i.e. a quantity, that is `0`, much like `0.000000001` represents a quantity (one that in problems of every day life is in fact indistinguishably close to `0`). In contrast to that `NULL` represents nothing. In fact there's nothing close to `NULL`. Any variable's (and expressions) value is either `NULL` or something.

-

That's how I always successfully explain it:

0 is the number 0.

NULL is nirvana, nothing, nada, niente, non-existence, absence.

Arithemetical operations defined on NULL always yield NULL as a result.

... worked for me so far.

-
NULL is the complete cessation of suffering? Woah! I gotta use that more in my code. –  Claudiu Feb 13 '12 at 22:52

Why wouldn't you start by asking the question "why is the value equal to NULL?" That might help you explain why a calculation might be invalid.

If you were doing periodic updates, and the new value didn't present itself (network error for example), then NULL might mean the calculation can't be performed and the old percent value is stale. You might want to show a symbol indicating you have stale data, the update didn't occur as expected.

The same thing might be true for a missing old value, but I have a hard time understanding how an old value could be lost (unless this is the first calculation, or the battery died and the data was lost.) But you might want to show a symbol that indicates this condition as well.

In any case you don't want this to happen:

Image Source

-

`OldValue=Null` you don't know the Old value so the Difference is Unknown (Null)

`OldValue=0` You know the Old value, it is 0, so the Difference is Infinity (Null)

If its important to distinguish between these two, simply store the Old value as `PreviousValue` and display its somewhere.

Edit based on the comment below:
I would just ask him what he wants do in the following situations and show him examples of each of these. Then and ask him "What value do you want to display for these?"

Do not in any way make this a computer issue.. It's a business question.

-

I've had a lot of success with 'blank.'

With people that understand basic algebra (or the simple idea that you can assign a value to a symbol), the idea of a 'blank' value seems to be pretty straightforward and distinct from a zero-value.

-

NULL is not a value, its a lack of one. Zero is still a value. (even if it can be used in cases where it cant be fully evaluated.

By analogy (a good way of explaining to non teckies;

``````if I have three apples then I subtract three apples how many apples do I have? is 0

If I have a bag of apples and I never look in the bag, how many apples do I have? is null
``````

Although in this case the two may have the same value (depending on your langage) they are conceptually very different

-

Really it isn't relevant whether your boss is a programmer. The issue is a conceptual one not a technical one.

Ask him to assume you got a raise. Your old salary was 175k, but your new salary is unknown. Then ask him - what percentage raise did you receive?

If he is arithmetically disabled, walk him through the process until he can see where the missing link is.

-

I use the coin flip. True is heads, false is tails, and one hand cupped over the other hiding the coin is NULL. If you know the coin is heads, it's very easy to answer the question "whats the opposite of this value?". One hand hiding the coin shows very easily that the person could answer if you'd let them see the current value, but since you won't, they can't give you an answer. For all they know, you don't even have a coin under your hand!

-

In a field, NULL says I have no idea; there's no data here -- its equivalent of blank. Zero (0) says I know the value of this field to be precisely 0, e.g., the integer that's one lower than the number one.

E.g., I could have a sheet of paper that I'm typing into the computer system that says how much each customer owes. Customer A owes \$50, customer B owes \$0, and customer C owes NULL (????), because the amount customer C owes number was redacted (someone drew a black line over it and its unreadable; or their was a note saying that their bill for isn't ready yet). I can comfortably say customer B owes \$0, and can comfortably say I don't know what customer C owes. I don't want to charge customer C \$0 (because they may owe money).

-

Assuming we're not getting into language-specific stuff like:

``````#define NULL 0
``````

The difference is that 0 is a numeric value where NULL is not. It is like an empty cell on a sheet of graph paper. A cell can have a "0" in it, but it can also be empty. Null is still a value, but you have deemed it special. For example if your cells were now characters, you may use the empty cell as a space and a null cell is no longer possible unless you designate or create a character to be the special case.

What null means depends on how the the data is interpreted:

• Null data may mean "not yet provided", and a program will wait a while before checking the value again.
• Data may not be able to have a null value. It may be interpreted strictly as an integer, and a non-integer value would be impossible.
• Commonly in memory, the address 0 is interpreted as null, which means it cannot be used.
-

I would run NVL check and if one of the parameters is NULL I would display the String "N/A" (don't return "0" since it's just not true!).

This is something that, from a manager point of view, would look more professional then displaying NULL value which kind of looks like you have a bug (which of course you don't - but that's the way it look to a non-engineer).

-

we have the old value, but not the new value.

Seems like in his mind, not having a new value doesn't mean it is unknown; it is the same as the old value. You can code that accordingly without explaining null because in his mind, it's never null.

Instead of arguing the point about NULLs, just make sure this doesn't create other problems. What are the ramifications of having data that are too old? When someone sees a 0% change, do they hit the panic button? Will this skew any analysis or aggregates over time?

I just don't think he needs a computer programming tutorial, but it would be nice if he knew what a null was and asked the right question instead.

-

If the `old value` is null the result of the equations should rightly be null. This is because null is not a number, it means unknown value. Therefore the calculation cannot proceed.

Of course your equation also fails if `old value = 0` as you cannot divide by zero.

Typically you handle these things with a case statement that will take you the path of what you want to show. So if you want the result to show as null when you can't calculate (as with a null or a zero, then you would do a case statement something like

``````case when [old value] = 0 then null
else 100 * (([old value] - [new value]) /[old value])
end
``````
-

Some great discussion on analogies to explain the mechanical aspects of a null symbol compared with other values; however, the crux of this question perhaps from the manager's perspective in adequately expressing the results in the UI, representing the meaningful limits of the perecnt changed formula as applied to their specific problem, can be answered more specifically I believe.

In the case of the percentage changed forumula, two things need to be represented: when something is first measured, and when nothing is measured for a particular aspect.

When something is first measured (oldValue === NULL), the percentage change is not applicible; and could/should explicitly be noted that it is the initial measurement for that measurement cycle.

When nothing is measured (newValue === NULL), the actual resulting percent change is actually the value of zero (assuming non-null in the previous cycle). Zero is generally the appropriate answer to the formula in this case. There was no measurement in this most recent cycle, and thus no measured change. That should not imply it did not change, just that nothing was measured, recorded, or communicated in regards to that aspect.

-

Certainly they understand the concept of putting "N/A" on a form when they mean "not available". For instance, to answer the question, "How old is Benjamin Franklin?" the answers 0 and N/A are obviously different (and the latter one is correct). Similarly the answers 0 and NULL are different.

-
• NULL is the absence of a value.
• -Zero- is a value, and that value is -Zero-.

If I had to explain these concepts to somebody, I would draw two boxes.

• An empty box would represent NULL.
• A box with a number -Zero- in it would represent -Zero-.
-

The difference between 0 and null is like the difference between having a bank account with a 0 balance and not having a bank account at all.

-

This is best explained with pointers.

``````var a -> [0] // variable a is initialized and set to hold 0
var b -> [123asgeb0] // variable b is initialized but not set, memory is pointed to, but holds whatever was there last
``````

Because of this, if your language does not keep track of NULL variables for you, then you will use whatever was already there. This can lead to overflows, stack corruptions, or other nasty side effects.

If the language does keep track of NULL variables, then it will have a stated case for handling this. I.E. treat NULL as zero, throw exception, treat as nop, etc.

To demonstrate for your boss, get a few pieces of used paper.

1. Take 2 glasses, and a white out stick.
2. White out a section of the page, and write down a zero
3. Put a glass over that (this is var a)
4. Put a glass over another section of the page (this is var b)
-

Judging by the variety of answers on this question, it can be concluded what we already know: null can mean many things.

Either it means the data is:

• not known to be known,
• known to be not known,
• invalid (e.g. measuring error),
• not accessible (e.g. security-wise),
• known to be known but irrelevant for the current data processing
• the data is infinite (often used in databases)
• probably 10 more things I didn't think of

For this, and many other reasons, it is often useful to define a datastructure which encapsulates the meaning of the null value (e.g. an Optional class).

-

The person who -1ed this has no sense of humor. FWIW I am an Architect myself.

Null is emptiness. According to legend there are 17 flavors (possible meanings) of Null including "I dont know", "They didnt say", and "somebody broke this". Nobody knows why except DBAs and Architects who scowl from dark back rooms at mere programmers. They smile politely at bosses and wag their tongues in deep concern with impossibly complex wisdom that must be so, and is deeply important but not very clear - because bosses deliver important performance to the DBA and Architect. A pay check. Even so Nulls never tell their secrets. They are black holes of empty. One null never equals another null because each emptiness is empty of equality and refuses to admit that it might not be all the empty there is. This of course is conjecture because no self respecting null could ever actually admit it. Sometimes nulls suck vast quantities of real data into their (). This is called a bug but really is just the emptyness of the programmer's mind forgetting about nulls. So you see they do breed sometimes. But how or why is always quickly forgotten. () is sometimes a sign of a null or his cousin nil but paradoxically seldom a bug.

Zero is a definite quantity of something. Zeros are much happier than nulls. Zeros are merely lazy and dont add much to the outcome.

For the boring, very old accurate answer Null is a representation of "missing information and inapplicable information" E.F. Codd, creator of SQL.

Codd also invented 3rd Normal Form which is applied to the minds of young Computer Scientists as a form of preliminary torture. Once mastered it is completely disregarded . Similar things happen when Object Oriented Design is introduced. And we are never happy to leave it alone, so along comes impedance mismatch to add to the frolicking fray.

-

protected by Mark Trapp Feb 14 '12 at 12:44

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.