SQL Null Values

What NULL really means

NULL is SQL's way of saying "there is no value here." It is not the same as the number 0, and it is not the same as an empty string ''. A NULL simply means the information is missing, unknown, or does not apply to that row. For example, an employee who has not yet been assigned a department would have NULL in the department column.

Testing for NULL with IS NULL and IS NOT NULL

Because NULL represents an unknown, you cannot compare it with the normal equals sign. Writing column = NULL never returns true. Instead, SQL provides two dedicated operators: IS NULL to find rows that are missing a value, and IS NOT NULL to find rows that have one.

-- Employees who have no department assigned
SELECT first_name, last_name
FROM Employees
WHERE department IS NULL;

-- Employees who do have a department
SELECT first_name, last_name
FROM Employees
WHERE department IS NOT NULL;

How NULL behaves in calculations

Any arithmetic that involves a NULL usually produces NULL, because the result of a calculation with an unknown part is itself unknown. If salary is NULL, then salary + 1000 is also NULL. This can surprise you when a computed column comes back empty for certain rows.

Replacing NULL with a fallback value

Often you want a readable substitute when a value is missing, such as showing 0 for a missing salary or 'Unassigned' for a missing department. Most databases offer a function for this. The standard SQL function is COALESCE, which returns the first non-NULL argument it is given. Some systems also provide IFNULL (MySQL) or ISNULL (SQL Server).

SELECT
  first_name,
  COALESCE(department, 'Unassigned') AS department,
  COALESCE(salary, 0) AS salary
FROM Employees;

• NULL means missing or unknown, not zero and not an empty string.
• Use IS NULL and IS NOT NULL to test for it, never = or <>.
• Arithmetic involving NULL generally returns NULL.
• Use COALESCE (or IFNULL / ISNULL) to display a fallback value.