[pearl2]

Greetings,

Suppose I've a table with a single column defined as follows:

CREATE TABLE mytable {
address VARCHAR(80) NULL,
};

When I insert values into the table and I do not have a value for 'address', should I use '' or NULL for that value?

INSERT INTO mytable VALUES ('Queestown'); /* with a value */
INSERT INTO mytable VALUES (''); /* without a value */

OR

INSERT INTO mytable VALUES ('Queestown'); /* with a value */
INSERT INTO mytable VALUES (NULL); /* without a value */


I look forward to hearing from you

[Mincer]

NULL is used to signify that a value for that column does not exist. An empty string shows that a value has been assigned, but that it is empty.

With numerical data this becomes more significant. A common example would be student grades. Say we have a course, English, that it's impossible to score zero marks on. If a student has not taken an exam, NULL is stored against that test in their table entry. If they have, then the mark is stored. But, if they can't score 0, why don't I set the default value as zero. Well, if I want to know the average mark for students that have taken the English module, I can simply do SELECT AVG( score ) FROM .... If I had stored 0 (or some other impossible to attain mark, like -1 for instance), then I could not use the AVG() function as my extra values would skew the results.

Hope this helps.

Matt.

[pearl2]

Thanks, Mincer!

So that means that for a numerical data column, if I don't have a value for that column, I've to insert NULL into it for a function like AVG() to work.

For a string (non-numerical) column, inserting '' is fine.

Am I on the right track?

[sundialsvcs]

So that means that for a numerical data column, if I don't have a value for that column, I've to insert NULL into it for a function like AVG() to work.

For a string (non-numerical) column, inserting '' is fine.

Am I on the right track?

I respectfully submit that "NULL is NULL, and an empty string is not." You should always use what is appropriate for the data. Any column in the database may be NULL (unless of course a non-null value is required).

AVG() will ignore NULL values as though they didn't exist; which, in fact, they don't.

NULL is also handy in mundane applications like printing mailing labels. If the 'Address2' field is NULL, that means without-question that "there is no Address2." Contrast this with, "There is an 'Address2' and its value is and should be an empty string." You're the master of what is 'appropriate' but be sure to think it through.

[pearl2]

Thanks, sundialsvcs!


A little clarification...

Whether numerical or otherwise, it's better to insert NULL into a column if it's value is not known.

I'm thinking about users submitting an online form with some empty fields (non-obligatory fields). Should I insert NULL or '' as default for those empty fields into the database?

[sundialsvcs]

If the user enters nothing meaningful into them (i.e. leaves them blank) I would definitely use NULL.

Empty strings are, to put it frankly, messy in a string-type field. Some databases don't even store them. They just interpret empty-strings as "you must have meant NULL" and that's that. But recognize that this is my opinion only. You alone know your data, your application... HTH.

[pearl2]

Thanks for your advice, sundialsvcs!