Results 1 to 8 of 8

Thread: Null Or ''

  1. #1
    Join Date
    May 2004
    Posts
    144

    Unanswered: Null Or ''

    hi
    i have a table with about 500,000 record, i want to add a new field to table, & for this 500,000, this field is empty, from now new field will have value.
    i dont know which one is more optimized, set default value of new field to NULL, OR '' ?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    I also find it more helpful to use NULL just incase you need to utilise the COALESCE feature of SQL as it won't work with blank strings '' , unless you write something like :
    Code:
    COALESCE(
      CASE WHEN newfield = '' THEN NULL
      ELSE newfield
      END
    ,oldfield)
    If you didn't do something like the above then you will get '' instead of the oldfield value.

    If you use nulls you can do something a little easier and less prone to mistakes, happy in the knowledge you're going to get the intended result :
    Code:
    COALESCE(newfield,oldfield)
    I know which is easier

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Here's an interesting link for you aschk

    And for my 2 cents - go with NULLs all the way! A NULL value takes up no memory, it's the absence of data! NULLs are unknowns - therefore it is correct to use NULLs where you don't know the value of an attribute
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Nice one george, i've used IFNULL before and seen NULLIF, just never had a use for it. Now I do!
    Thanks

  6. #6
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    A NULL value takes up no memory, it's the absence of data!
    According to the MySQL manual a tinyint takes 1 byte and can be any number from 0 to 255 or -127 to 128.

    A byte being 8 bits, you can only store 256 different values in it. But 0 to 255 or -127 to 128 is already 256 values. Add in NULL as an alternative, and you have 257. Therefore, a tinyint column must require an extra bit *somewhere*.

    NULLs are unknowns - therefore it is correct to use NULLs where you don't know the value of an attribute
    No, the correct thing isn't supported by SQL DBMSs. NULLs are fine in relational values, that is, results returned by a query. In practice, you should use them sparingly in your schema because they make queries unpredictable. The reason why follows...

    Base relational variables, tables in SQL-speak, should have predicates. The predicate is the logical statement that declares the semantics of the table. A list of employees, for example, might say "An employee with SSN (ssn), first name (fname), etc, works at this company." Each row of the table is a proposition, that is it corresponds to filling in the blanks.

    Put all the rows together and you have a logical statement.
    Without nulls, the logic is simple: every record in the table represents the true propositions and every record that *could* be in the table but isn't represents the false propositions. So if I know that an employee with SSN 123-45-6789 has the first name Bob, etc, I know that there is definitely *not* an employee with SSN 123-45-6789 with the name John.

    With nulls, you now have to contend with the notion of propositions that have the unknown truth value, but there are infinitely (strictly, a really huge finite number) many other unknown propositions that aren't listed. So now, what does the table mean? How can you reason based on it? The answer is that you can't. If you ask the database to count the number of employees whose name starts with A, it's going to happily return a number that might be wrong if someone's name is unknown. In other words, after all the work of designing that database, you now have something that spits out very official looking garbage.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by sco08y
    A byte being 8 bits, you can only store 256 different values in it. But 0 to 255 or -127 to 128 is already 256 values. Add in NULL as an alternative, and you have 257. Therefore, a tinyint column must require an extra bit *somewhere*.
    A NULL is the absence of data... (0 bytes)
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sco08y
    If you ask the database to count the number of employees whose name starts with A, it's going to happily return a number that might be wrong if someone's name is unknown.
    no

    this is always where the over-analyzing theorists fall on their faces

    the number of employees whose name starts with an A is correct, even if there is an employee whose name is NULL in the database

    the real WTF here is that the database professional did not insist that the employee name be declared NOT NULL

    a gun in the hands of a child can cause havoc, but it's not the child's fault

    use NULL wisely

    end of
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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