Handling NULLs: IS NULL and COALESCE

In the employees dataset, Andrea Costa is special: he has department_id = NULL. You have already brushed against NULL (with LEFT JOIN), but the time has come to take it seriously: NULLs in SQL behave differently from any other value, and ignoring them is one of the top causes of bugs in production code.

NULL is not zero, not empty string

NULL means unknown / missing value. It is not 0, not '', not false. It is "I don't know". From this follows a surprising property: NULL is not equal to anything, not even itself.

-- All these expressions return NULL (not true!):
SELECT NULL = NULL;       -- NULL
SELECT NULL = 0;          -- NULL
SELECT NULL <> 'qualcosa'; -- NULL

Concrete consequence: WHERE column = NULL never works. You must use the dedicated operators IS NULL / IS NOT NULL:

-- Employees without a department:
SELECT first_name, last_name
FROM   employees
WHERE  department_id IS NULL;

-- Employees WITH a department:
SELECT first_name, last_name
FROM   employees
WHERE  department_id IS NOT NULL;

COALESCE — the NULL fallback

Often you want to display something instead of NULL when the data is missing: the string '(none)', zero, a default date. The function that does exactly this is COALESCE(a, b, c, ...): it returns the first non-NULL argument in the list.

-- Show the department_id, or 0 if missing:
SELECT first_name, last_name, COALESCE(department_id, 0) AS dept
FROM   employees;

Combined with a LEFT JOIN it becomes incredibly powerful for reports:

SELECT e.first_name,
       e.last_name,
       COALESCE(d.name, '(nessun dipartimento)') AS dipartimento
FROM   employees   AS e
LEFT JOIN departments AS d ON e.department_id = d.id;

Result: all employees returned, with the department name or an explicit label for those who do not have one.

NULL and aggregates

Aggregate functions ignore NULLs (except COUNT(*)):

• COUNT(department_id) counts only the rows with a value;
• AVG(salary) averages ignoring NULLs — it does not treat them as 0;
• SUM(...) sums only the non-NULL values.

This is almost always what you want. But it is good to know: if you are looking for "the total number of employees" use COUNT(*), not COUNT(department_id).

Try it

Review exercise