nick at byu.edu
Thu Jun 9 12:57:27 MDT 2005
On Thursday 09 June 2005 12:30 pm, Gabriel Gunderson wrote:
The NULL debate is nearly as bad or worse than the vi vs emacs debates by the
academics. IMO, those that argue for strictness to the relational model
haven't written a line of production code in their life. In many cases NULL
is a practical shortcut to a much more laborious schema.
The 'proper' relational way is to have another table that contains a row or
not depending on the existence of the data. Then, you join to that table,
and if there is no matching row, that is how you'd know rather than using
NULLs. Just sticking in a NULL in the first table can avoid the need for
join entirely. For a lot of data, this is IMO the best solution because it
I'm all for NULLs.
MySQL has a few nice functions that help make NULLs less painful.
COERCE() accepts a list of arguments and returns the first that is not null.
CONCAT_WS() can be used to concat a bunch of values, ignoring the null ones:
> SELECT CONCAT_WS('', 'hello ', NULL, 'world');
That one is particularly nice for creating WHERE IN clauses:
> SELECT CONCAT_WS(',', 'foo', NULL, 'bar');
A more useful result in many cases than "foo,,bar" would be.
There are also, of course, foreign key constraints that can help where
references are concerned such as ON DELETE SET NULL.
Sales Team Automation, LLC
1335 West 1650 North, Suite C
Springville, UT 84663 +1 801.853.4090
More information about the PLUG