NULL Handling

---

title: “NULL Handling (IS NULL / IS NOT NULL)” part: 1 topic_number: 19 slug: “null-handling” difficulty: “Beginner” prerequisites: “select-from-where” —

NULL Handling

What Is It?

NULL represents “unknown” or “missing” data — not zero, not empty string, but the absence of a value. NULL behaves differently from regular values, and you need special operators (IS NULL / IS NOT NULL) to work with it.

Real-world analogy: A form field left blank. It’s not that the answer is “0” or “” — it’s that no answer was provided.

---

Understanding NULL

NULL is NOT Zero

SELECT * FROM employees WHERE salary = 0;  -- Finds employees with $0 salary
SELECT * FROM employees WHERE salary IS NULL;  -- Finds employees with NO salary info

These are different!

• salary = 0 → The salary exists and is zero dollars
• salary IS NULL → No salary information exists

NULL is NOT Empty String

SELECT * FROM employees WHERE first_name = '';  -- Finds employees with empty string name
SELECT * FROM employees WHERE first_name IS NULL;  -- Finds employees with NO name

Again, different!

• first_name = '' → Name exists but is empty
• first_name IS NULL → No name was provided

---

Basic Examples

Find Employees with No Salary

SELECT first_name, last_name, salary
FROM employees
WHERE salary IS NULL;

Expected output:

first_name	last_name	salary
Noah	Harris	NULL
Paul	Garcia	NULL

Cannot use = NULL! Always use IS NULL.

Find Employees with a Salary

SELECT first_name, last_name, salary
FROM employees
WHERE salary IS NOT NULL;

Expected output: All 18 employees who have salaries assigned.

---

Going Deeper

NULL in Comparisons (Always False/Unknown)

-- These all return FALSE (or NULL, technically)
NULL = NULL  -- Not TRUE!
NULL != NULL
NULL > 100
NULL < 100
100 + NULL

Key rule: Any comparison with NULL results in NULL (unknown), which is treated as FALSE in WHERE clauses.

Example:

SELECT * FROM employees WHERE manager_id = NULL;

Returns: Empty set (0 rows)

Why: manager_id = NULL evaluates to NULL for every row, even rows where manager_id IS NULL. NULL = NULL is not TRUE!

-- • CORRECT
SELECT * FROM employees WHERE manager_id IS NULL;

Returns: Alice and Paul (employees with no manager)

NULL in Arithmetic (Poison!)

SELECT 
    first_name,
    salary,
    salary + 5000 AS salary_with_bonus
FROM employees
WHERE first_name = 'Noah';

Expected output:

first_name	salary	salary_with_bonus
Noah	NULL	NULL

NULL + 5000 = NULL! Any arithmetic operation with NULL produces NULL.

This is called the “NULL poison” effect — NULL spreads through calculations.

NULL in Aggregates

SELECT 
    COUNT(*) AS total_employees,
    COUNT(salary) AS employees_with_salary,
    SUM(salary) AS total_payroll,
    AVG(salary) AS avg_salary
FROM employees;

Expected output:

total_employees	employees_with_salary	total_payroll	avg_salary
20	18	1633000.00	90722.22

Key points:

• COUNT(*) counts all rows (including NULLs)
• COUNT(salary) counts only non-NULL values
• SUM and AVG ignore NULL values

Pause and Predict: What does MIN(manager_id) return if some manager_ids are NULL?

Answer

`MIN(manager_id)` returns 1 (the smallest non-NULL manager_id). NULL values are ignored by MIN, MAX, SUM, AVG — they only process non-NULL values.

---

Watch Out — Common Mistakes

Mistake #1: Using = NULL or != NULL

--  WRONG — Never matches anything!
SELECT * FROM employees WHERE salary = NULL;
SELECT * FROM employees WHERE salary != NULL;

Both return empty sets! Comparisons with NULL always evaluate to NULL (treated as FALSE).

-- • CORRECT
SELECT * FROM employees WHERE salary IS NULL;
SELECT * FROM employees WHERE salary IS NOT NULL;

Mistake #2: Forgetting NULL in NOT IN

--  TRICKY — Doesn't work as expected if the subquery contains NULL
SELECT *
FROM employees
WHERE department_id NOT IN (1, 2, NULL);

Expected: Employees not in departments 1 or 2

What happens: Returns empty set!

Why: department_id NOT IN (1, 2, NULL) is equivalent to:

• department_id != 1 AND department_id != 2 AND department_id != NULL
• The last condition (!= NULL) is always FALSE/NULL
• So the entire AND condition fails

The fix:

-- • CORRECT — Filter out NULLs
SELECT *
FROM employees
WHERE department_id NOT IN (1, 2) AND department_id IS NOT NULL;

Or use NOT EXISTS:

SELECT *
FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM (SELECT 1 AS id UNION SELECT 2 UNION SELECT NULL) vals 
    WHERE vals.id = e.department_id
);

Mistake #3: NULL in CONCAT (String Concatenation)

SELECT CONCAT(first_name, ' ', last_name, ' - Dept: ', department_id)
FROM employees
WHERE first_name = 'Paul';

Expected: “Paul Garcia - Dept: NULL”

What you get: NULL (entire result is NULL)

Why: CONCAT with any NULL argument returns NULL.

The fix: Use COALESCE or IFNULL (we’ll cover COALESCE in Part 2):

SELECT CONCAT(first_name, ' ', last_name, ' - Dept: ', IFNULL(department_id, 'None'))
FROM employees
WHERE first_name = 'Paul';

Output: “Paul Garcia - Dept: None”

---

Edge Case Spotlight

ORDER BY with NULLs

SELECT first_name, salary
FROM employees
ORDER BY salary ASC;

MySQL sorts NULL values first in ASC order (treated as “lowest” values).

Output (top rows):

first_name	salary
Noah	NULL
Paul	NULL
Ivy	55000.00
…	…

In DESC order:

SELECT first_name, salary
FROM employees
ORDER BY salary DESC;

NULLs appear last (still treated as “lowest”).

To control NULL positioning:

-- Put NULLs last even in ASC
SELECT first_name, salary
FROM employees
ORDER BY salary IS NULL, salary ASC;

How this works: salary IS NULL evaluates to 0 (false) or 1 (true). Sorts FALSE (0) first, so non-NULLs appear first.

---

Try This

Exercise 1 (Guided)

Find all employees who don’t have a manager assigned. Show their first_name, last_name, and manager_id.

Hint

WHERE manager_id IS NULL

Exercise 2 (Independent)

Count how many employees have a manager vs. how many don’t. Show both counts in one query.

Hint

Use COUNT with two different conditions. You might need SUM with a CASE statement, or two separate COUNT expressions.

Exercise 3 (Challenge)

Find employees whose salary is unknown (NULL) or less than $60,000. Show their name and salary, sorted by salary (with NULLs last).

---

Answer Key

Exercise 1 Answer

```sql SELECT first_name, last_name, manager_id FROM employees WHERE manager_id IS NULL; ``` **Expected output:** | first_name | last_name | manager_id | |------------|-----------|------------| | Alice | Johnson | NULL | | Paul | Garcia | NULL |

Exercise 2 Answer

**Approach 1: Two separate queries (simple but not elegant):** ```sql SELECT COUNT(*) AS has_manager FROM employees WHERE manager_id IS NOT NULL; SELECT COUNT(*) AS no_manager FROM employees WHERE manager_id IS NULL; ``` **Approach 2: One query with CASE:** ```sql SELECT SUM(CASE WHEN manager_id IS NOT NULL THEN 1 ELSE 0 END) AS has_manager, SUM(CASE WHEN manager_id IS NULL THEN 1 ELSE 0 END) AS no_manager FROM employees; ``` **Expected output:** | has_manager | no_manager | |-------------|------------| | 18 | 2 | **Approach 2 is more elegant** — all in one query.

Exercise 3 Answer

```sql SELECT first_name, last_name, salary FROM employees WHERE salary IS NULL OR salary < 60000 ORDER BY salary IS NULL, salary ASC; ``` **Expected output:** | first_name | last_name | salary | |------------|-----------|--------| | Ivy | Taylor | 55000.00 | | Tina | Rodriguez | 58000.00 | | Noah | Harris | NULL | | Paul | Garcia | NULL | **Explanation:** - WHERE captures both NULL salaries and salaries < $60K - `ORDER BY salary IS NULL, salary ASC` sorts non-NULLs first (ascending), then NULLs at the end **Alternative (NULLs first):** ```sql ORDER BY salary ASC; -- Default behavior, NULLs first ```

---

Quick Recap

• NULL represents unknown/missing data — not zero, not empty string
• Use IS NULL and IS NOT NULL — never = NULL or != NULL
• NULL in comparisons always evaluates to NULL (treated as FALSE)
• NULL in arithmetic makes the entire result NULL (“NULL poison”)
• Aggregates (SUM, AVG, MIN, MAX) ignore NULL values
• COUNT(*) includes NULLs, COUNT(column) excludes them
• Be careful with NOT IN when the list contains NULL

---

Up Next

Next topic: String Functions (UPPER, LOWER) → part1_20_string_functions_upper_lower.md

You’ve mastered the tricky world of NULL! Next, you’ll learn useful string functions to manipulate text data!