Date and Pascal hate nulls. One even goes so far as to say that if you
permit NULLs in a database, then the results from *every* query is
suspect. So they turn perform backflips suggesting ways to avoid nulls.
None, so far, seem appealing.
To me, nulls are quite useful in the Real World. For instance, there
may be a lot of immediate value for end users in committing a row that
has a few nulls (e.g. as in not yet collected), rather than slavishly
follow a rule that says Thou Shalt Not commit a row with nulls.
Can't the intelligent practitioner simply proceed carefully with
queries when nulls are involved? With group functions at least, I
believe nulls are ignored. In Oracle, you can use NVL() to force group
functions to recognize.
What about simply doing an IS NULL test in the code when any table that
allows nulls in involved in a query?
What precisely has Date and Pascal's knickers in such a twist? The fact
that ad hoc queries from random, unintelligent users could give strange
results?
What if one has control over the queries performed through a GUI or
application? Doesn't the problem disappear, presuming the programmer is
aware of the issue and the application is well documented?
What are some of the best ways to deal with the issue of nulls
potentially resulting in questionable query results short of
disallowing them? Storing some sort of coded or numeric value that
represents UNKNOWN or NOT APPLICABLE.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
