Handling Missing Values — NULL in MySQL

Sometimes a value in a table is simply not known, not applicable, or not yet entered. In our Employee table, the salary of employee 106 (Ipsita Rout) has not been fixed, so it is shown as NULL. A NULL represents a missing or unknown value — and it is important to understand that NULL is not the same as zero, and not the same as an empty text (''). Zero is a definite number and an empty string is a definite (though blank) piece of text, whereas NULL means 'no value at all'.

Because NULL means 'unknown', it behaves specially in comparisons. Any ordinary comparison with NULL — such as Salary = NULL or Salary <> NULL — gives a result that is neither true nor false but 'unknown', so such a row is not returned. This is why we cannot test for a missing value with =. Instead MySQL provides two special tests:

• IS NULL — true when the value is missing.
• IS NOT NULL — true when the value is present (not missing).

For example, to list employees whose salary has not yet been fixed:

SELECT Name FROM Employee
WHERE Salary IS NULL;

This returns Ipsita Rout. To list only employees who do have a salary:

SELECT Name, Salary FROM Employee
WHERE Salary IS NOT NULL;

NULL also affects calculations and functions. In any arithmetic, a NULL makes the whole result NULL — for example Salary + 2000 is NULL for Ipsita Rout. Aggregate functions such as SUM, AVG and COUNT(column) (studied later in this chapter) simply ignore NULLs rather than treating them as zero, which is usually what we want.

To supply a stand-in value wherever a NULL appears, MySQL offers the IFNULL(value, replacement) function: it returns the value itself if it is not NULL, and the replacement if it is. For instance, to show an unfixed salary as 0:

SELECT Name, IFNULL(Salary, 0) AS Salary
FROM Employee;
``` …