NULLs and CASE

Why it matters

Real tables have gaps: a customer who did not give a phone number, an employee not yet assigned to a department, a measurement that failed. SQL marks these with NULL. Handling NULL correctly is one of the main differences between a query that looks right and one that is right. In the same lesson you will learn CASE, which lets you turn raw values (including missing ones) into readable categories.

What NULL means

NULL means "unknown" or "no value". It is not zero and not an empty string. '' is a known, empty piece of text; NULL is the absence of any value.

Because NULL is unknown, comparing anything with it gives unknown, not true or false:

SELECT 5 = NULL;     -- NULL
SELECT NULL = NULL;  -- NULL (two unknowns are not known to be equal)

WHERE keeps only rows where the condition is true, so WHERE phone = NULL keeps nothing, ever. Use the special operators instead:

SELECT name FROM employees WHERE phone IS NULL;      -- missing phone
SELECT name FROM employees WHERE phone IS NOT NULL;  -- has a phone

NULL in arithmetic and text

Any arithmetic with NULL gives NULL: salary + NULL is NULL. Joining text with || also gives NULL if any part is NULL, so first_name || ' ' || middle_name disappears for anyone without a middle name.

A trap with NOT and <>

Suppose some employees have no department. This query does not return them:

SELECT name FROM employees WHERE department <> 'Sales';

For a NULL department, NULL <> 'Sales' is unknown, so the row is dropped. If you mean "everyone not in Sales, including the unassigned", say so:

WHERE department <> 'Sales' OR department IS NULL

COALESCE: supply a fallback

COALESCE(a, b, c, ...) returns the first argument that is not NULL. It is standard SQL and works in every major engine.

SELECT name,
       COALESCE(department, 'Unassigned') AS department,
       COALESCE(mobile, office_phone, 'no phone') AS contact
FROM employees;

SQLite and MySQL also have IFNULL(a, b), and SQL Server has ISNULL(a, b); these take exactly two arguments. NULLIF(a, b) does the reverse: it returns NULL when a = b. It is handy to avoid division by zero: total / NULLIF(count, 0) returns NULL instead of an error or a wrong result.

CASE: if-then logic in a query

CASE evaluates conditions in order and returns the value for the first one that is true:

SELECT name,
       salary,
       CASE
         WHEN salary IS NULL  THEN 'unknown'
         WHEN salary < 50000  THEN 'entry'
         WHEN salary < 80000  THEN 'mid'
         ELSE 'senior'
       END AS salary_band
FROM employees;

Points to notice:

• Conditions are checked top to bottom and the first match wins. A salary of 45,000 matches < 50000 and stops there, so the second test does not need salary >= 50000.
• Without ELSE, anything that matches no branch becomes NULL.
• NULL matches no comparison, so check IS NULL first if missing values need their own label. If the IS NULL branch were missing here, a NULL salary would fall through to ELSE and be labelled "senior".

There is also a shorter form for comparing one value with constants:

CASE status
  WHEN 'A' THEN 'Active'
  WHEN 'S' THEN 'Suspended'
  ELSE 'Other'
END

CASE with aggregates

CASE inside SUM or COUNT builds a small pivot table in one pass:

SELECT department,
       COUNT(*) AS staff,
       SUM(CASE WHEN phone IS NULL THEN 1 ELSE 0 END) AS missing_phone
FROM employees
GROUP BY department;

This is a standard data-quality check: how many records per group are missing a key field?

Worked example

HR wants a directory showing every employee's department (or "Unassigned") and a salary band, with unknown salaries labelled clearly:

SELECT name,
       COALESCE(department, 'Unassigned') AS department,
       CASE
         WHEN salary IS NULL THEN 'unknown'
         WHEN salary < 50000 THEN 'entry'
         WHEN salary < 80000 THEN 'mid'
         ELSE 'senior'
       END AS salary_band
FROM employees;

Nothing in the table changes; you are labelling the output.

Common mistakes

• = NULL or <> NULL. Always IS NULL / IS NOT NULL.
• Forgetting that <> and NOT IN skip NULL rows.
• Treating '' and NULL as the same. Check for both if your data has both: WHERE phone IS NULL OR phone = ''.
• CASEAn if-then expression that checks WHEN conditions top to bottom and returns the value of the first one that is true, or ELSE. branches in the wrong order. Put the most specific tests (including IS NULL) first.
• Missing ELSE. Unmatched rows silently become NULL.

Quick recap

• NULL means unknown; comparisons with it are unknown, so use IS NULL.
• Arithmetic and || with NULL give NULL.
• COALESCE returns the first non-nullA marker meaning a value is unknown or missing; it is not zero and not an empty string. value; NULLIF turns a value into NULL.
• CASE WHEN ... THEN ... ELSE ... END buckets values; the first true branch wins.

Try it

Open the Do tab and build an HR directory that handles missing departments and salaries.