[bono56]

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 '' ?

[r937]

use null

[aschk]

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 :

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 :

COALESCE(newfield,oldfield)

I know which is easier

[georgev]

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

[aschk]

Nice one george, i've used IFNULL before and seen NULLIF, just never had a use for it. Now I do!
Thanks

[sco08y]

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.

[georgev]

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)

[r937]

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