What you will be able to do
After this lesson you can find rows with missing values, avoid the classic = NULL mistake, and understand why some rows quietly vanish from your results. NULL is the most common source of "my query looks right but the numbers are off", so it is worth an entire lesson.
What NULL means
NULL marks a missing or unknown value. It is not zero, and it is not an empty piece of text. It says "there is no value here". The sample database has several of them, and each has a reason:
manager_idis NULL for people who report to nobody, such as the directors.department_idis NULL for Ravi Pillai, a consultant who belongs to no department.end_dateis NULL for projects that have not finished.
The meaning of a NULL comes from the table design. A NULL end_date means "not ended yet", but in another table a NULL might mean "not recorded". Always check what a NULL stands for before you act on it.
Testing for NULL
You cannot use = to look for NULL. Try it and see:
SELECT first_name, manager_id
FROM employees
WHERE manager_id = NULL;(no rows returned)Nothing comes back, and no error either. That is because manager_id = NULL is not true or false for any row, it is unknown: is an unknown value equal to an unknown value? Nobody can say. Even NULL = NULL is unknown. The correct tests are IS NULL and IS NOT NULL:
SELECT first_name, manager_id
FROM employees
WHERE manager_id IS NULL;+------------+------------+
| first_name | manager_id |
+------------+------------+
| Meera | NULL |
| Vikram | NULL |
| Dev | NULL |
| Lata | NULL |
| Pooja | NULL |
| Ravi | NULL |
+------------+------------+Six people have no manager. Five of them are heads of departments and the sixth is Ravi, the consultant. To ask the opposite question, use IS NOT NULL:
SELECT name, end_date
FROM projects
WHERE end_date IS NOT NULL;+-----------------+------------+
| name | end_date |
+-----------------+------------+
| Customer Portal | 2024-09-30 |
| Spring Campaign | 2025-04-30 |
| Q3 Sales Push | 2024-09-30 |
+-----------------+------------+Three-valued logic
Because of NULL, a condition can have three outcomes: true, false and unknown. WHERE keeps rows only when the result is true. A row whose condition is unknown is treated just like a row where it is false and is left out.
Here is how that bites. This query asks for everybody not in department 1:
SELECT first_name, department_id
FROM employees
WHERE department_id <> 1;+------------+---------------+
| first_name | department_id |
+------------+---------------+
| Vikram | 2 |
| Neha | 2 |
| Sameer | 2 |
| Tara | 2 |
| Dev | 3 |
| Anika | 3 |
| Yash | 3 |
| Lata | 4 |
| Omar | 4 |
| Pooja | 5 |
| Karan | 5 |
| Sana | 5 |
| Aman | 2 |
+------------+---------------+There are 14 employees outside department 1 in the table, counting Ravi, but only 13 appear. Ravi's department is NULL, so NULL <> 1 is unknown and his row is dropped. If you want him as well, say so:
SELECT first_name, department_id
FROM employees
WHERE department_id <> 1 OR department_id IS NULL;+------------+---------------+
| first_name | department_id |
+------------+---------------+
| Vikram | 2 |
| Neha | 2 |
| Sameer | 2 |
| Tara | 2 |
| Dev | 3 |
| Anika | 3 |
| Yash | 3 |
| Lata | 4 |
| Omar | 4 |
| Pooja | 5 |
| Karan | 5 |
| Sana | 5 |
| Ravi | NULL |
| Aman | 2 |
+------------+---------------+Think of NULL as "I do not know". If you do not know someone's department, you cannot claim that they are not in department 1.
Combining NULL with other conditions
AND, OR and NOT follow the same three-valued rules. A few useful facts: true OR unknown is true, false AND unknown is false, and NOT unknown is unknown. You can mix the NULL tests into normal conditions:
SELECT first_name, manager_id
FROM employees
WHERE manager_id IS NULL AND department_id IS NOT NULL;+------------+------------+
| first_name | manager_id |
+------------+------------+
| Meera | NULL |
| Vikram | NULL |
| Dev | NULL |
| Lata | NULL |
| Pooja | NULL |
+------------+------------+That is the five department heads, without the consultant.
NULL in arithmetic and text
Any calculation involving NULL returns NULL:
SELECT NULL + 5 AS plus_five;+-----------+
| plus_five |
+-----------+
| NULL |
+-----------+Joining text with || also gives NULL when either side is NULL. The CONCAT function is where databases disagree: MySQL returns NULL if any argument is NULL, while PostgreSQL and SQL Server treat a NULL argument as empty text. This is why NULL handling needs a conscious decision whenever you build text or totals.
NULL in aggregates
Aggregate functions are the one place where NULLs are skipped rather than spreading. COUNT(*) counts rows, and COUNT(column) counts the rows where that column is not NULL:
SELECT COUNT(*) AS all_rows,
COUNT(manager_id) AS with_manager,
COUNT(department_id) AS with_department
FROM employees;+----------+--------------+-----------------+
| all_rows | with_manager | with_department |
+----------+--------------+-----------------+
| 20 | 14 | 19 |
+----------+--------------+-----------------+Twenty employees, fourteen with a manager, nineteen with a department. AVG, SUM, MIN and MAX also skip NULLs. The average of a column is the sum of its known values divided by how many known values there are, not by the number of rows. The aggregate lesson covers this in more depth.
Replacing NULL when you display it
Often you want a friendlier value for reports. COALESCE(value, replacement) returns the first argument that is not NULL, and it works on all four databases. The lesson on CAST, COALESCE and NULLIF shows it in full. Preview:
SELECT first_name, COALESCE(manager_id, 0) AS manager_or_zero
FROM employees
WHERE id IN (1, 2, 18);+------------+-----------------+
| first_name | manager_or_zero |
+------------+-----------------+
| Meera | 0 |
| Arjun | 1 |
| Ravi | 0 |
+------------+-----------------+Using 0 as a stand-in is fine for display, but 0 is not a real manager id, so do not store it as one.
Differences between databases
IS NULLandIS NOT NULLare standard and work everywhere.- A null-safe equality, where two NULLs count as equal, is written
<=>on MySQL andIS NOT DISTINCT FROMon PostgreSQL. SQLite usesIS. These are not portable, so most SQL usesIS NULLexplicitly. - How NULL is shown depends on the tool. Some print the word NULL, and some leave the cell empty.
- On Oracle, an empty string is stored as NULL. On the four databases of this course it is not.
- The sort position of NULL differs, as the sorting lesson shows.
The MySQL course has a lesson on NULL values with MySQL-specific functions.
Common mistakes
= NULLor<> NULL. Both are always unknown. UseIS NULLandIS NOT NULL.- Forgetting NULL rows after a negative condition, such as
<>orNOT IN. - Treating NULL as zero in sums and averages, or as an empty string in text.
- Comparing
COUNT(*)andCOUNT(column)without noticing they differ because of NULLs. - Storing special markers like 0, -1 or
'N/A'instead of NULL. They look like real values and spoil calculations.
Exercise
List the projects that have not ended yet, showing name and start_date.
Show one possible answer
SELECT name, start_date
FROM projects
WHERE end_date IS NULL;+-------------------+------------+
| name | start_date |
+-------------------+------------+
| Mobile App | 2024-05-01 |
| Billing Migration | 2025-01-10 |
+-------------------+------------+Key points
- NULL means a value is missing or unknown. It is not zero and not an empty string.
- Use IS NULL and IS NOT NULL to test for it. = NULL never matches anything.
- Any comparison with NULL gives unknown, and WHERE keeps only rows where the condition is true.
- Arithmetic with NULL gives NULL, and aggregate functions such as AVG and COUNT(column) skip NULLs.
- COUNT(*) counts rows, while COUNT(column) counts only rows where that column is not NULL.