MySQL. The point in NULL?

[Stephen Philbin]

Hi folks. Just wondering, what the point in setting a field column with NULL is? I mean, if it's not gonna hold anything, why have it?

Or is it that it is at NULL by default but if a piece of data is entered into the column on a row, that single entry will alone will switch from null to whatever is has been given, whilst the other entries on other rows in that column will remain null. And if that is so, what is a non null, yet empty entry classed as? An empty string? integer 0 or -1?

[NogDog]

I suspect that what you are talking about is defining a column with the attribute "nulls allowed," i.e.: it is OK to enter a record with no data in that field. If a field is not set to "nulls allowed", any query which tries to add a record to that table with no value for that field will generate a SQL error.

[Stephen Philbin]

Ahhhhh. So it's just allowing null. Not setting a default entry.

Nice one Nog. That cleared things up nicely. ;)

Have a bone. :D

EDIT: P.S. What about this signed and unsigned business though? I've seen it mentioned in many other things but never found an explanation of the difference between signed and unsigned data.

[CardboardHammer]

An integer of a given type size has a fixed range from maximum value to minimum value. A signed 1 byte integer ranges from -128 to 127. An unsigned 1 byte integer ranges from 0 to 255. If one is not going to be encountering negative numbers, an unsigned integer yields a greater usable range.

Going back to NULL, it allows one to indicate unknown/missing value without having to do any hacks. For example, if one were storing customer survey results and there was a field for net worth, NULL would indicate that it was unknown, while any numeric value could conceivably appear there, so one couldn't easily be picked out as representing unknown. While there are cases where it would be possible to use a numeric value to indicate unknown (or, in any case, one could use a second field to indicate whether the value in the first was valid,) it also makes data easier to process, as it doesn't require more convoluted code to retrieve correct results.

Example:
SELECT AVG(age) FROM PERSON
vs.
SELECT AVG(age) FROM PERSON WHERE age_is_valid = 1
or
SELECT AVG(age) FROM PERSON WHERE age > -1