Is Division by Zero NULL?

I think a better option would be to provide support for the IEEE arithmetic standard which would give the values +/- infinity, NaN and also +/-0 the latter would be applicable where otherwise you would get an underflow error.

None of these are NULL although NaN (not a number) is similar.

Where those standards are not applied division by zero is an error in all (AFAIK) programming languages so I see no good reason for SQL behaving differently.

Well, if you are processing a big set of data and one row has a division by zero, I certainly would want the option of it not throwing an error (which I have now by circumventing the operation) and being able to complete the task.

As long as your query has division by zero mitigation handling in it, there won't be an error.

But, if you have to add division by zero handling to overcome this problem, why not have a standardised, optional, internal way of returning some kind of value from this disallowed operation, rather than have each programmer write their own version?

For me, the option for x / y to return a null (as with ANSI_WARNINGS off) is better than having everyone use CASE statements or NULLIF() isn't it?

Again, to be sure, I do not agree that x / 0 equals NULL, but pragmatically, I'm fairly sure most people put some form of protection in their divisions and most of those would return a NULL - or am I wrong?

It is important.

Because, as I said before, every occasion of division by zero indicates an error in code or/and in data design.

Use of zeros instead of nulls - one of typical such errors.

Which NULLIF([somecolumn],0) handles perfectly, if that is the desired behavior.

Because there is and cannot be no standardised interpretation of zero divider.

Those who would writing such a function would have no idea what kind of error was made in each particular case, what kind of design flaw, shortcut in code, whatever else put a zero behind a slash.

In each particular case the output value would be different, depending on the actual meaning of that zero in each particular business case.

Therefore, "each programmer write their own version", according to the business rules in each particular case.

My understanding of databases and mathematics for NULL and infinity is as follows:

Infinity represents a known value, but for which is infinite in size. Size can be either macro or micro. Examples would include the size of the universe or the size of a point. With these references to infinity, there is no number which could be substituted (i.e. ... If you wanted to talk about the size of the universe multiplied by 2, there is no number which could be used for the calculation).

Infinity can also represent a known value that is seemingly impossible to identify the exact value. An example of this would be Pie. It's known to be greater than 3 and less than 4. The difference here is that a number can be used to substitute for it and the accuracy of your results depends upon how many decimal places to carry it to when applying it in a formula.

A NULL value represents the lack of a number. There is no knowledge if it is bigger or smaller than anything. When a row is created and one of the columns is a numeric but the data to populate that column hasn't been input yet, then NULL identifies that the value could be anything or nothing. The only thing that can be said about the value is that it is unknown.

There seem to be 2 question at hand with this forum thread:

1) Is division by zero considered NULL by proper definition

2) Even if #1 is false, wouldn't almost every usage of division by zero be more appropriate to return NULL instead of raising an error.

My opinion on #1 is that division by zero is properly defined by NULL, because the result is not infinitely large nor is it infinitely small nor is it a number where the exact precision can't be determined (like Pie). It is truly an "unknown" value because the operation cannot be performed.

My opinion on #2 is "Heck Yeah!" In databases and in Excel sheets and programming languages and everything else that handles division.

The bottom line here is that neither side is going to be correct. Discussions about NULL often go south because people have differing views. They frequently end up with people throwing insults around, like on this thread.

I would argue strongly that it should NOT return NULL because it is an invalid mathematical calculation. In many cases the business rule to have it return NULL is perfectly valid. That does not mean that the sql engine should return NULL because it is the easiest for the developer in most situations. Consider this, a NULL value for the denominator or numerator will result in NULL. If dividing by 0 also results in NULL you would have now way of knowing why you got NULL. It could be because either the denominator or numerator is NULL OR it could be because denominator is 0. This leads to ambiguous results which is a criminal wrongdoing for the sql engine to make. Dividing by 0 is not allowed in math and anything other than an error from the sql engine would be a decision by the sql engine that it knows best how to handle an invalid operation. This would be a horribly poor decision. I know Steve suggested creating a connect item for this but I would be shocked if it gained any momentum. That being said I can conceive of times I would want to know that the denominator is 0 and not get NULL although I can't think of a time I have needed this. But knowing the option is there allows me the freedom to decide how I want to deal with dividing by 0 instead of being forced to treat it the same as a NULL in the equation.