From: Dan Nelson
Date: September 16 2003 2:24am
Subject: Re: Does Null == ""?
List-Archive: http://lists.mysql.com/mysql/149829
Message-Id: <20030916022449.GA48979@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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
> meant.

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".
 
-- 
	Dan Nelson
	dnelson@stripped