Working with NULL

NULL means unknown or missing — it is not zero, not an empty string, and not false.

Why NULL is special

Comparing anything to NULL with = or != always yields NULL (not TRUE or FALSE):

-- This returns NO rows, even if manager_id is NULL
WHERE manager_id = NULL   -- WRONG

-- This is correct
WHERE manager_id IS NULL

IS NULL and IS NOT NULL

-- Employees with no manager assigned
SELECT first_name, last_name
FROM employees
WHERE manager_id IS NULL;

-- Employees who have a manager
SELECT first_name, last_name
FROM employees
WHERE manager_id IS NOT NULL;

COALESCE — replace NULL with a default

COALESCE(a, b, c, ...) returns the first non-NULL argument:

SELECT
  first_name,
  COALESCE(manager_id::text, 'No manager') AS manager
FROM employees;

NULLs in aggregates

Most aggregate functions (SUM, AVG, COUNT(col)) silently skip NULLs. COUNT(*) counts all rows including those with NULLs.

Tips

• When you LEFT JOIN and the right side is NULL, use IS NULL to detect unmatched rows (covered in Joins).
• NULLIF(a, b) returns NULL when a = b — useful to avoid divide-by-zero: salary / NULLIF(hours, 0).