SQL NULL Handling
Dealing with Missing or Unknown Data

Introduction

Not all data is always available — sometimes a student's city is missing, or marks haven't been entered yet. In SQL, these unknown or missing values are called NULL. But handling NULLs isn’t as simple as checking for blank strings — they need special care. Let’s explore how to work with NULLs properly in SQL.

What is NULL in SQL?

NULL represents an unknown, missing, or inapplicable value. It is not the same as zero or an empty string. You must use specific operators like IS NULL or COALESCE to handle NULL values.

Sample Table – students

CREATE TABLE students (
  roll_no INT,
  name VARCHAR(50),
  marks INT,
  city VARCHAR(30)
);

INSERT INTO students VALUES
(1, 'Aarav Sharma', 85, 'Delhi'),
(2, 'Diya Iyer', NULL, 'Chennai'),
(3, 'Sneha Patil', 78, NULL),
(4, 'Mehul Agarwal', NULL, 'Delhi'),
(5, 'Saira Bano', 90, NULL);

1. IS NULL and IS NOT NULL

Find students whose marks are missing:

SELECT name
FROM students
WHERE marks IS NULL;

name
--------------
Diya Iyer
Mehul Agarwal

Find students who have provided their city:

SELECT name, city
FROM students
WHERE city IS NOT NULL;

2. COALESCE – Replace NULL with a Default

Show city, but default to 'Unknown' if NULL:

SELECT name, COALESCE(city, 'Unknown') AS display_city
FROM students;

name           | display_city
----------------+---------------
Aarav Sharma   | Delhi
Diya Iyer      | Chennai
Sneha Patil    | Unknown
...

3. IFNULL / NVL

Different SQL dialects provide similar functions:

• IFNULL(x, y) → MySQL
• NVL(x, y) → Oracle
• COALESCE(x, y) → standard and portable

SELECT name, IFNULL(marks, 0) AS safe_marks
FROM students;

4. Using NULL in Conditions – Be Careful!

This will NOT work as expected:

-- Wrong: This returns 0 rows
SELECT * FROM students WHERE marks = NULL;

Always use IS NULL instead of = NULL.

5. Aggregates and NULL

Aggregate functions like AVG() or SUM() ignore NULLs:

SELECT AVG(marks) AS avg_marks
FROM students;

avg_marks
-----------
84.33

Only non-NULL marks are used in the average.

Real-World Use Case

In a school database:

• NULL marks mean exams not yet evaluated
• NULL city means address details are incomplete
• COALESCE helps you cleanly show 'Unknown' instead of blanks

Best Practices

• Never use = NULL; use IS NULL or IS NOT NULL.
• Use COALESCE or IFNULL to avoid NULLs in outputs.
• Test queries with and without NULLs — they can silently affect results.

Summary

NULLs represent uncertainty — and that requires precision. From filtering missing data to replacing it for cleaner reports, handling NULLs properly is a critical SQL skill. It helps ensure your queries reflect the real world — even when the data is incomplete.

What’s Next?

Next, we’ll dive into SQL CASE expressions — to apply logic and decisions directly within SELECT queries.