Null - between the ears (long)

**Bill Karwin** · Jul 23 '05, 06:48 AM

Re: Null - between the ears (long)

iStrain wrote:[color=blue]
> 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.[/color]

FWIW, one can also use the keyword NULL to indicate a column is nullable.
For example:
CREATE TABLE T (
optionalData INTEGER NULL
);
[color=blue][color=green]
>> mysql> INSERT INTO my_table (phone) VALUES (NULL);
>> mysql> INSERT INTO my_table (phone) VALUES ('');
>>
>>
>> Both statements insert a value into the phone column,[/color][/color]

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.
[color=blue][color=green]
>> 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.''[/color][/color]

This is kind of up to you and the context of your application.
[color=blue]
> 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.][/color]

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_trans action_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.
[color=blue]
> 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?[/color]

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.
[color=blue]
> 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.[/color]

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** · Jul 23 '05, 06:48 AM

Re: Null - between the ears (long)

>[color=blue][color=green][color=darkred]
>>> mysql> INSERT INTO my_table (phone) VALUES (NULL);
>>> mysql> INSERT INTO my_table (phone) VALUES ('');
>>>
>>>[/color][/color]
>
> 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.[/color]

Thanks Bill. Okay, this makes sense.[color=blue]
>[color=green][color=darkred]
>>> 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.''[/color][/color]
>
> 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_trans action_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.[/color]

Again, that makes perfect sense. I think the reference page on the site
did more harm than good....
[color=blue]
> 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.
>[color=green]
>> 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.[/color]
>
> 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.[/color]

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.
[color=blue]
>
> 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.[/color]

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** · Jul 23 '05, 06:48 AM

Re: Null - between the ears (long)

iStrain wrote:[color=blue]
> Does that sound right?[/color]

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

Regards,
Bill K.

Null - between the ears (long)

Null - between the ears (long)

Comment

Comment

Comment