473,372 Members | 1,205 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Null - between the ears (long)

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."

Jul 23 '05 #1
3 2091
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.
Jul 23 '05 #2
>
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."

Jul 23 '05 #3
iStrain wrote:
Does that sound right?


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

Regards,
Bill K.
Jul 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

17
by: Douglas Alan | last post by:
Is there a canonical way of iterating over the lines of a file that are null-separated rather than newline-separated? Sure, I can implement my own iterator using read() and split(), etc., but...
11
by: Dmitry D | last post by:
Hi, I'm new to C++ (started learning in the beginning of this summer), and I have the following question (sorry if it sounds stupid): In many code samples and source files, I see NULL expression...
28
by: wwj | last post by:
void main() { char* p="Hello"; printf("%s",p); *p='w'; printf("%s",p); }
8
by: sugaray | last post by:
Hi, I just came upon this code snippet which parses a string stored in buf with comma(,) as delimiter and store each substring into args, the question I'm having here is that I don't get why in the...
2
by: Thomas G. Marshall | last post by:
Arthur J. O'Dwyer <ajo@nospam.andrew.cmu.edu> coughed up the following: > On Thu, 1 Jul 2004, Thomas G. Marshall wrote: >> >> Aside: I've looked repeatedly in google and for some reason cannot >>...
64
by: yossi.kreinin | last post by:
Hi! There is a system where 0x0 is a valid address, but 0xffffffff isn't. How can null pointers be treated by a compiler (besides the typical "solution" of still using 0x0 for "null")? -...
69
by: fieldfallow | last post by:
Hello all, Before stating my question, I should mention that I'm fairly new to C. Now, I attempted a small demo that prints out the values of C's numeric types, both uninitialised and after...
15
by: khan | last post by:
Hi, I read that pointer representation can non-zero bit pattern, machine specific.Compiler when comes accross value '0' in pointer context, converts it to machine specific null pointer...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.