IS NULL and IS NOT NULL

What NULL Really Means

NULL represents missing, unknown or not-applicable information. It is not zero, FALSE, a blank string or the word “NULL”. A student whose result is not declared may have marks = NULL; a student scoring zero has a known value.

IS NULL and IS NOT NULL

SELECT student_id, full_name
FROM students
WHERE marks IS NULL;

SELECT student_id, full_name, marks
FROM students
WHERE marks IS NOT NULL
ORDER BY student_id;

The first query finds missing marks; the second finds recorded marks. Do not write marks = NULL or marks <> NULL.

NULL and Three-Valued Logic

UNKNOWN does not pass WHERE. It also explains why NOT IN can surprise when its list contains NULL.

COUNT, Arithmetic and Fallback Values

SELECT COUNT(*) AS total_rows,
       COUNT(marks) AS recorded_marks,
       AVG(marks) AS average_recorded_marks
FROM students;

Most aggregates ignore NULL inputs; COUNT(*) counts rows. Arithmetic involving NULL commonly returns NULL. To display a label without changing storage:

SELECT full_name,
       COALESCE(CAST(marks AS CHAR), 'Not recorded') AS marks_display
FROM students;

Design and Safe Updates

Use NULL when “unknown” is valid, NOT NULL when a value is mandatory, and a CHECK/foreign key for valid choices. Before filling missing data, preview exact rows:

START TRANSACTION;
SELECT student_id, full_name FROM students
WHERE marks IS NULL FOR UPDATE;

UPDATE students SET marks = 0
WHERE marks IS NULL;
-- Verify whether zero truly means the same thing.
ROLLBACK;

Mistakes and Practice

• Writing = NULL.
• Using empty text to mean unknown in every column.
• Forgetting that aggregates ignore NULL differently.
• Applying COALESCE before deciding the business meaning.

1. Add one practice row with NULL marks and compare COUNT(*) with COUNT(marks).
2. Find only recorded marks.
3. Show “Pending” without modifying the stored NULL.
4. Explain why zero and NULL change AVG differently.

Quick Summary

• NULL means no known value.
• Test it with IS NULL or IS NOT NULL.
• WHERE rejects UNKNOWN; most aggregates ignore NULL.
• Fallback functions change display, not the underlying meaning.

Official References

• Working with NULL Values — MySQL 8.4
• Comparison Operators
• Aggregate Function Descriptions

References reviewed 14 August 2026.