Results 1 to 7 of 7

Thread: Null or ''

  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Unanswered: Null or ''

    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

  2. #2
    Join Date
    Sep 2003
    Location
    London
    Posts
    56
    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.

  3. #3
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    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?

  4. #4
    Join Date
    Oct 2003
    Posts
    706

    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.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  5. #5
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    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?

  6. #6
    Join Date
    Oct 2003
    Posts
    706
    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.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  7. #7
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Thanks for your advice, sundialsvcs!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •