We earlier used NULL values to track down missing data. We had to use “is null” and “is not null” to find null values.
When a field doesn’t contain anything--when it isn’t even empty--it is null. Null is a special stand-in value for unknown.
As we learned earlier when we calculated statistics on our albums, null values are invisible. For any group or statistical function they don’t exist. They also don’t exist for most other purposes.
For example, a comparison with a null value is itself always null. For almost all practical purposes, a comparison with a null value is always false unless you are using one of the made-for-NULL functions or operators such as is null or ifnull().
Suppose you have these three values in your table:
Person Type
John Doe Employee
Jane Doe Student
Jerry Doe NULL
If you asked for all persons “where Type="Employee"” you would get back one person: John Doe. But if you ask for all persons “where Type!= "Employee"” you will also receive only one person: Jane Doe.
Null means no value. A comparison with a null value is itself always null. So when MySQL gets to the entry for Jerry Doe and asks “is NULL not equal to Employee”, the answer is NULL. Null evaluates to false, so Jerry Doe is not in the list of non-employees.
And that’s right. Null means no value. It means you don’t know. You should never use NULL values to mean anything other than that.