SQL NULL Handling — Dealing with Missing or Unknown Data: IS NULL, COALESCE, IFNULL

One student has no mark yet. The report must distinguish not assessed from an actual score of zero.

NULL represents a missing, unknown or inapplicable value. Ordinary comparisons involving NULL generally produce unknown, which is different from true and false.

IS NULL tests for the missing value directly. COALESCE selects the first non-null argument for a display or calculation. WHERE accepts only true, so marks = NULL cannot find missing marks. AVG and SUM ignore NULL inputs; an all-null or empty input has special aggregate outcomes.

Related concepts: Filter with WHERE and SQL Functions and LEFT JOIN.

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.

SQL · portable concepts; preserved examples identify their engine

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);

SQL · portable concepts; preserved examples identify their engine

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;

SQL · portable concepts; preserved examples identify their engine

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
...

SQL · portable concepts; preserved examples identify their engine

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;

SQL · portable concepts; preserved examples identify their engine

This will NOT work as expected:

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

Always use IS NULL instead of = NULL.

SQL · portable concepts; preserved examples identify their engine

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.

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

• 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.

Replacing missing marks with zero changes the meaning of an average. NOT IN can be affected by a NULL member. NULL is not a string containing the word NULL, although the display table uses that text to make absence visible.

Make Diya’s mark missing, then compare COUNT(*) with COUNT(marks).

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

UPDATE practice_students SET marks = NULL WHERE roll_no = 2;
SELECT COUNT(*) AS student_rows, COUNT(marks) AS entered_marks FROM practice_students;

There are still three students but only two entered marks. Missing assessment data is not automatically a failing score.

NULL represents a missing, unknown or inapplicable value. Ordinary comparisons involving NULL generally produce unknown, which is different from true and false.

There are still three students but only two entered marks. Missing assessment data is not automatically a failing score.

Continue your understanding with Filter with WHERE and SQL Functions and LEFT JOIN.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.