SQL NULL Values — How to Handle Missing Data

What Is NULL in SQL?

NULL represents a missing or unknown value in a database column. A field with NULL has no value — it was left blank when the row was created (perhaps during an INSERT INTO operation) or was explicitly set to NULL.

We will use this Employees table with some NULL values:

+----+-----------+------------+--------+-----------+
| ID | Name      | Department | Salary | City      |
+----+-----------+------------+--------+-----------+
| 1  | Priya K.  | Sales      | 55000  | Mumbai    |
| 2  | John D.   | Marketing  | 62000  | London    |
| 3  | Arun M.   | IT         | 78000  | NULL      |
| 4  | Emma W.   | NULL       | 51000  | Sydney    |
| 5  | Ravi S.   | IT         | NULL   | Mumbai    |
| 6  | Sara L.   | HR         | 58000  | New York  |
+----+-----------+------------+--------+-----------+

Notice that Arun has no City, Emma has no Department, and Ravi has no Salary. These are all NULL values.

You Cannot Use = to Check for NULL

-- ✗ WRONG: This returns NO results
SELECT * FROM Employees WHERE City = NULL;

-- ✓ CORRECT: Use IS NULL
SELECT * FROM Employees WHERE City IS NULL;

IS NULL — Find Rows with Missing Values

The IS NULL operator returns rows where the specified column has no value:

SELECT Name, City
FROM Employees
WHERE City IS NULL;

+-----------+------+
| Name      | City |
+-----------+------+
| Arun M.   | NULL |
+-----------+------+

Find employees with no department assigned:

SELECT Name, Department
FROM Employees
WHERE Department IS NULL;

+-----------+------------+
| Name      | Department |
+-----------+------------+
| Emma W.   | NULL       |
+-----------+------------+

IS NOT NULL — Find Rows with Values

The IS NOT NULL operator returns rows where the column does have a value:

SELECT Name, Salary
FROM Employees
WHERE Salary IS NOT NULL;

+-----------+--------+
| Name      | Salary |
+-----------+--------+
| Priya K.  | 55000  |
| John D.   | 62000  |
| Arun M.   | 78000  |
| Emma W.   | 51000  |
| Sara L.   | 58000  |
+-----------+--------+

Ravi S. is excluded because his Salary is NULL.

COALESCE — Replace NULL with a Default Value

The COALESCE() function returns the first non-NULL value from a list. It is commonly used to replace NULL with a meaningful default:

SELECT Name,
       COALESCE(City, 'Not Assigned') AS City
FROM Employees;

+-----------+--------------+
| Name      | City         |
+-----------+--------------+
| Priya K.  | Mumbai       |
| John D.   | London       |
| Arun M.   | Not Assigned |
| Emma W.   | Sydney       |
| Ravi S.   | Mumbai       |
| Sara L.   | New York     |
+-----------+--------------+

Arun’s NULL city is replaced with “Not Assigned” in the output. The actual table data is not changed — COALESCE only affects the display.

SELECT Name,
       COALESCE(Salary, 0) AS Salary
FROM Employees;

+-----------+--------+
| Name      | Salary |
+-----------+--------+
| Priya K.  | 55000  |
| John D.   | 62000  |
| Arun M.   | 78000  |
| Emma W.   | 51000  |
| Ravi S.   | 0      |
| Sara L.   | 58000  |
+-----------+--------+

NULL in Calculations

Any arithmetic operation involving NULL returns NULL. This is a common trap:

SELECT Name,
       Salary,
       Salary * 12 AS AnnualSalary
FROM Employees;

+-----------+--------+--------------+
| Name      | Salary | AnnualSalary |
+-----------+--------+--------------+
| Priya K.  | 55000  | 660000       |
| John D.   | 62000  | 744000       |
| Arun M.   | 78000  | 936000       |
| Emma W.   | 51000  | 612000       |
| Ravi S.   | NULL   | NULL         |
| Sara L.   | 58000  | 696000       |
+-----------+--------+--------------+

NULL with COUNT

COUNT(*) counts all rows including those with NULL. But COUNT(column) only counts rows where that column is NOT NULL. You will learn more about COUNT and other aggregate functions in Chapter 14: SQL COUNT, AVG, SUM.

SELECT
  COUNT(*) AS TotalRows,
  COUNT(Salary) AS WithSalary,
  COUNT(Department) AS WithDept
FROM Employees;

+-----------+------------+----------+
| TotalRows | WithSalary | WithDept |
+-----------+------------+----------+
| 6         | 5          | 5        |
+-----------+------------+----------+

There are 6 total rows, but only 5 have a Salary value and 5 have a Department value.

For a complete reference of NULL handling functions, see the official MySQL NULL documentation. To permanently fix NULL values in your data, use the SQL UPDATE statement covered in the next chapter.

If you work with data regularly, explore our ready-made Dashboard Templates on NextGenTemplates that use SQL-powered data for business reporting.

📺 Visit our YouTube channel @NeoTechNavigators for step-by-step video tutorials and dashboard demos on this topic.

SELECT Name,
       COALESCE(Department, 'Unassigned') AS Department,
       COALESCE(Salary, 0) AS Salary,
       COALESCE(City, 'Unknown') AS City
FROM Employees;

+-----------+------------+--------+-----------+
| Name      | Department | Salary | City      |
+-----------+------------+--------+-----------+
| Priya K.  | Sales      | 55000  | Mumbai    |
| John D.   | Marketing  | 62000  | London    |
| Arun M.   | IT         | 78000  | Unknown   |
| Emma W.   | Unassigned | 51000  | Sydney    |
| Ravi S.   | IT         | 0      | Mumbai    |
| Sara L.   | HR         | 58000  | New York  |
+-----------+------------+--------+-----------+

📖 Chapter 9 of 30 — SQL Tutorial on Neotech Navigators