SQL NULL Values – IS NULL and IS NOT NULL with Examples (2026-27)

✅ What is NULL in SQL?

✅ NULL means a field has no value — the data is missing, unknown, or was never entered.

✅ NULL is not zero, not empty string, and not false — it is the complete absence of a value.

✅ Any comparison with NULL using =, !=, <, or > returns UNKNOWN — not TRUE or FALSE.

✅ Sample Employees Table (with NULL values shown in red):-

POOJA (ID=2) and SUJAN (ID=4) have no phone number recorded — their Phone column is NULL.

✅ IS NULL and IS NOT NULL Syntax

-- Find rows where the column IS NULL (missing value):
SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NULL;

-- Find rows where the column IS NOT NULL (has a value):
SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NOT NULL;

✅ IS NULL:- Returns rows where the column has no value.

✅ IS NOT NULL:- Returns rows where the column has a value.

✅ Example 1 – Find Rows with NULL (IS NULL)

Find all employees whose phone number is missing.

✅ SELECT Query:-

SELECT * FROM Employees
WHERE Phone IS NULL;

✅ Result:-

✅ Example 2 – Find Rows without NULL (IS NOT NULL)

Find all employees who have a phone number recorded.

✅ SELECT Query:-

SELECT * FROM Employees
WHERE Phone IS NOT NULL;

✅ Result:-

✅ Example 3 – Update NULL Values

Update POOJA's phone number (previously NULL) to a real number.

✅ UPDATE Query:-

UPDATE Employees
SET Phone = '9000000001'
WHERE ID = 2;

✅ Result — After Update:-

✅ Example 4 – COUNT with NULL Values

Understanding how NULL affects COUNT() is essential for accurate reporting.

✅ SELECT Query:-

-- COUNT(*) counts ALL rows including NULLs:
SELECT COUNT(*) AS TotalEmployees FROM Employees;

-- COUNT(column) counts only NON-NULL values:
SELECT COUNT(Phone) AS EmployeesWithPhone FROM Employees;

✅ Result:-

✅ Example 5 – COALESCE: Replace NULL with a Default Value

COALESCE returns the first non-NULL value from a list of arguments — perfect for displaying a fallback when a column is NULL.

✅ SELECT Query:-

SELECT Name,
       COALESCE(Phone, 'Not Provided') AS PhoneNumber
FROM Employees;

✅ Result:-

✅ NULL vs Empty String vs Zero

✅ How NULL Affects Aggregate Functions

✅ Key Points to Remember

✅ NULL = missing or unknown data — not zero, not empty string.

✅ Use IS NULL — to find rows with no value in a column.

✅ Use IS NOT NULL — to find rows that have a value in a column.

✅ Never use = NULL or != NULL — always returns no results (UNKNOWN).

✅ COALESCE(col, 'default') — replaces NULL with a fallback value in SELECT output.

✅ COUNT(*) includes NULLs — COUNT(column) skips NULLs.

✅ SUM, AVG, MAX, MIN all ignore NULLs — only non-NULL values are used.

✅ NULL in NOT IN list — can cause the entire NOT IN query to return zero rows.

✅ Common SQL NULL Mistakes to Avoid