In the last episode (Sep 15), Bruce Feist said:
> Bob Hall wrote:
> >The meaning of NULL is defined in the SQL specification; it means
> >"not known" or "not applicable".
> Which is just about as useful as not defining it, actually. The
> vagueness is the cause of a great many program bugs when database
> designers don't specify what NULL means for a given field. To give a
> hypothetical example:
> The application is payroll/personnel. A programmer is tasked with
> creating forms for data entry on new employees, including supervisor.
> If the user doesn't enter a new employee's supervisor, the
> application accepts it, figuring that it is not yet known, and stores
> NULL for the field ("not known" use of NULL).
> Meanwhile, a payroll programmer has been tasked with writing an
> application to give the CEO a huge bonus and stock options. To
> figure out which employee is the CEO, the application looks for the
> employee with NULL for supervisor ("not applicable" use of NULL).
> Suddenly, a large number of new hires are fabulously wealthy. Who
> screwed up? Answer: the DB designer who didn't specify what NULL
Your example has nothing to do with the vagueness of NULL though.
Replace NULL with "0" and you get the same result.
Of course, if I were the payroll programmer, I would simply select all
employees WHERE emp.titleid = titles.id AND titles.name="CEO".