Null - between the ears (long)

[iStrain]

Hiya. I'm _sure_ this is an FAQ, but Googling hasn't produced the
answer in a way I can make sense out of. I know I should get this, but
so far no way...

I'm creating tables and doing queries in Perl, and Nulls have started
to bother me greatly. The first issue is, as far as I understand it, a
column should be NOT NULL if it is necessary (required) data.

Now, if a column doesn't have to be NOT NULL; that is, it's not
_required_ data, but is useful in some way like a descriptive comment,
that specifier is left off. That's easy..unless I'm going stupid.

Then when we get to the default value specifier, my ears leak.
According to the reference manual...

mysql> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ('');
Both statements insert a value into the phone column, but the first
inserts a NULL value and the second inserts an empty string. The
meaning of the first can be regarded as ``phone number is not known''
and the meaning of the second can be regarded as ``the person is known
to have no phone, and thus no phone number.''

Should I interpret the first statement as "the phone value is an
optional field, and is set to NULL, to indicate that we don't have one
yet?" Likewise, if we don't have a descriptive comment for an item, we
simply enter NULL to indicate that. And the second statement means that
there's nothing there, and that's the value that's supposed to be
there.[ It's this second statement that impies a "null" to me. This
seems backwards.]

Now in addition to this, there's the issue of setting the default value
as NULL. Obviously this has to be a column that is allowed to be NULL,
and is therefore not required. In my case, let's say I have a phony
table like this

CREATE TABLE `artist` (
`id` int(11) NOT NULL auto_increment,
`lastName` varchar(30) NOT NULL default 'Unknown',
`firstName` varchar(20) NOT NULL default 'Unknown',
`nickname` varchar(20) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM

Obviously the id and last and first names I want, and the nickname is
optional. The default values for the first & last names are not really
relevant, right, because when I INSERT the record I need a real value
for those, right? I really don't even need defaults, is that correct?

But for the optional field, I may or may not have a value. Now in that
case, the values are very often NULL, so I'll have a column filled with
NULLs. Not only is this a pain in Perl, but it seems to contradict the
reference manual's description. I'd also like to be able to search in
this column, to see if there's a match. So it seems like I've got this
wrong.

Thanks. And thanks for being patient!
--
"It's beyond my ken... and my Barbie, and all of my action figures."

[Bill Karwin]

iStrain wrote:

Now, if a column doesn't have to be NOT NULL; that is, it's not
_required_ data, but is useful in some way like a descriptive comment,
that specifier is left off.
FWIW, one can also use the keyword NULL to indicate a column is nullable.
For example:
CREATE TABLE T (
optionalData INTEGER NULL
);

mysql> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ('');
Both statements insert a value into the phone column,
To be pedantic for a moment, this is not exactly accurate. A NULL in
SQL is considered an absence of a value, not a value itself. The mantra
you should learn is "NULL is a state, not a value." If it were a value,
you could use it in expressions. But a NULL combined in most
expressions yields another NULL.

mysql> select NULL + 10;
+-----------+
| NULL + 10 |
+-----------+
| NULL |
+-----------+
1 row in set (0.01 sec)

mysql> select concat(NULL, "foo");
+---------------------+
| concat(NULL, "foo") |
+---------------------+
| NULL |
+---------------------+
1 row in set (0.00 sec)

There's also an effect of NULL in boolean expressions; the boolean
opposite of NULL is still NULL:

mysql> select NOT NULL;
+----------+
| NOT NULL |
+----------+
| NULL |
+----------+
1 row in set (0.00 sec)

mysql> select NOT NOT NULL;
+--------------+
| NOT NOT NULL |
+--------------+
| NULL |
+--------------+
1 row in set (0.00 sec)

So there is a useful distinction between NULL and '' for strings, or 0
for integers.
but the first
inserts a NULL value and the second inserts an empty string. The
meaning of the first can be regarded as ``phone number is not known''
and the meaning of the second can be regarded as ``the person is
known to have no phone, and thus no phone number.''

This is kind of up to you and the context of your application.
Should I interpret the first statement as "the phone value is an
optional field, and is set to NULL, to indicate that we don't have one
yet?" Likewise, if we don't have a descriptive comment for an item, we
simply enter NULL to indicate that. And the second statement means that
there's nothing there, and that's the value that's supposed to be
there.[ It's this second statement that impies a "null" to me. This
seems backwards.]
Again, it depends on how you're going to use it. If you never combine
the value with something else, then an empty string or a NULL may be
equivalent for purpose of your application. But if you're storing, say,
"earliest_transaction_date" in a banking application, a NULL might be
very informative; it could mean that no transaction has been made on
that account to date.
Now in addition to this, there's the issue of setting the default value
as NULL. Obviously this has to be a column that is allowed to be NULL,
and is therefore not required. In my case, let's say I have a phony
table like this

CREATE TABLE `artist` (
`id` int(11) NOT NULL auto_increment,
`lastName` varchar(30) NOT NULL default 'Unknown',
`firstName` varchar(20) NOT NULL default 'Unknown',
`nickname` varchar(20) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM

Obviously the id and last and first names I want, and the nickname is
optional. The default values for the first & last names are not really
relevant, right, because when I INSERT the record I need a real value
for those, right? I really don't even need defaults, is that correct?
Actually, it is useful to define those defaults for NOT NULL fields,
because the default value is used when you omit those columns from your
insert statement:

INSERT INTO `artist` (`nickname`) VALUES ('Vinny One-Ear');

This inserts the record using the default values for lastName and firstName.
But for the optional field, I may or may not have a value. Now in that
case, the values are very often NULL, so I'll have a column filled with
NULLs. Not only is this a pain in Perl, but it seems to contradict the
reference manual's description. I'd also like to be able to search in
this column, to see if there's a match. So it seems like I've got this
wrong.

Why is it a pain in Perl? NULLs are simply returned as an undef. This
is easy to test for with Perl's defined() function.

Why can't you search the column for matches? WHERE nickname = 'Vinny'
gives the correct answer where the nickname is NULL, because the
expression evaluates to NULL, which is like FALSE in that it fails to
satisfy the WHERE clause.

And I'm not sure what part of the manual you see as contradicting this.

Regards,
Bill K.

[iStrain]

>

mysql> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ('');
To be pedantic for a moment, this is not exactly accurate. A NULL in
SQL is considered an absence of a value, not a value itself. The
mantra you should learn is "NULL is a state, not a value." If it were
a value, you could use it in expressions. But a NULL combined in most
expressions yields another NULL.
Thanks Bill. Okay, this makes sense. but the first inserts a NULL value and the second inserts an empty
string. The meaning of the first can be regarded as ``phone number is
not known'' and the meaning of the second can be regarded as ``the
person is known to have no phone, and thus no phone number.''

This is kind of up to you and the context of your application.

Again, it depends on how you're going to use it. If you never combine
the value with something else, then an empty string or a NULL may be
equivalent for purpose of your application. But if you're storing,
say, "earliest_transaction_date" in a banking application, a NULL might
be very informative; it could mean that no transaction has been made on
that account to date.

Again, that makes perfect sense. I think the reference page on the site
did more harm than good....
Actually, it is useful to define those defaults for NOT NULL fields,
because the default value is used when you omit those columns from your
insert statement:

INSERT INTO `artist` (`nickname`) VALUES ('Vinny One-Ear');

This inserts the record using the default values for lastName and firstName.

But for the optional field, I may or may not have a value. Now in that
case, the values are very often NULL, so I'll have a column filled with
NULLs. Not only is this a pain in Perl, but it seems to contradict the
reference manual's description. I'd also like to be able to search in
this column, to see if there's a match. So it seems like I've got this
wrong.
Why is it a pain in Perl? NULLs are simply returned as an undef. This
is easy to test for with Perl's defined() function.

Ah. Okay, being a Perl newbie too, I used a snippet from someone else
to handle Null values, and it's kind of a mess. Now that I understand
it's simply undef, it's less arcane. Undef should be one of the returns
from a query that I deal with.

Why can't you search the column for matches? WHERE nickname = 'Vinny'
gives the correct answer where the nickname is NULL, because the
expression evaluates to NULL, which is like FALSE in that it fails to
satisfy the WHERE clause.

So, if I'm assimilating this correctly, I can use Nulls as I see fit,
(aside from the rules for primary keys and such, of course) assuming I
understand the ramifications of how it will be evaluated by queries and
functions, et al.

In my trivial example, if i choose for my "optional" data to be default
Null, I simply have to deal with it appropriately, and it's not
breaking any design rules. The use of default Null (that matra works
well, btw), is probably the right choice for columns that will in most
cases be empty strings, zero values, and so on, since they'll
immediately fail WHERE and so on. Does that sound right?

Thx!

Mike

--
"It's beyond my ken... and my Barbie, and all of my action figures."

[Bill Karwin]

iStrain wrote:

Does that sound right?

Sounds like you've got it!
NULL can be your friend. <g>

Regards,
Bill K.