SQL NULL Handling Explained with Examples + Practice Questions

What is NULL

NULL represents missing or unknown data.

It is not:

• 0
• empty string
• false

It means no value exists.

When to Use NULL Handling

• When data is missing
• When filtering incomplete records
• When working with joins and optional relationships

NULL-related questions are very common in SQL interviews.

Example Dataset

Table: employees

Checking NULL Values

Incorrect Way

sql
SELECT * FROM employees
WHERE department = NULL;

This will NOT work.

Correct Way

sql
SELECT * FROM employees
WHERE department IS NULL;

Checking NOT NULL

sql
SELECT * FROM employees
WHERE department IS NOT NULL;

NULL in Conditions

NULL cannot be compared using:

• =
• !=

Always use:

• IS NULL
• IS NOT NULL

Handling NULL with Functions

Using COALESCE (Replace NULL)

sql
SELECT name, COALESCE(department, 'Not Assigned') AS dept
FROM employees;

Replaces NULL with a default value.

Using IFNULL (MySQL specific)

sql
SELECT name, IFNULL(salary, 0) AS salary
FROM employees;

NULL in Aggregations

sql
SELECT AVG(salary) FROM employees;

NULL values are ignored. Only valid numbers are used.

NULL in COUNT

sql
SELECT COUNT(department) FROM employees;

Counts only non-null values.

sql
SELECT COUNT(*) FROM employees;

Counts all rows.

NULL with JOIN

sql
SELECT e.name
FROM employees e
LEFT JOIN departments d
ON e.department = d.name
WHERE d.name IS NULL;

Used to find unmatched records.

Key Concepts

• NULL means missing data
• Cannot use = or != with NULL
• Use IS NULL / IS NOT NULL
• Use COALESCE to replace NULL
• NULL is ignored in most aggregations

Common Questions (FAQ)

What is NULL in SQL

NULL represents missing or unknown data.

Why = NULL does not work

Because NULL is not a value, it is a state.

How to replace NULL values

Use COALESCE or IFNULL.

Does COUNT include NULL

COUNT(column) ignores NULL, COUNT(*) includes all rows.

Internal Learning Links

• Learn WHERE filtering
• Learn JOINs
• Learn Aggregations

Practice CTA

Practice NULL-based SQL problems on Schoolabe to handle real-world messy data.