NULL — Handling Missing Data

7 min read

Understanding NULL

NULL represents an absent or unknown value. It is not zero, not an empty string — it is the absence of any value. This makes NULL behave differently from all other values in SQL.

Any arithmetic or comparison involving NULL returns NULL (unknown). This is why WHERE city = NULL never matches any rows — the comparison itself is NULL, not TRUE.

sql

-- Students with no city recorded
SELECT name FROM students WHERE city IS NULL;

-- Students who DO have a city
SELECT name FROM students WHERE city IS NOT NULL;

-- COALESCE returns the first non-NULL value
SELECT name, COALESCE(city, 'Unknown') AS city FROM students;

-- Enrolments where a score has not been entered yet
SELECT student_id, course_id FROM enrolments WHERE score IS NULL;

Aggregate functions like COUNT, SUM, and AVG silently ignore NULL values. COUNT(score) counts only rows where score is not NULL, while COUNT(*) counts all rows.

Ready to test yourself?

Practice SQL— quiz & coding exercises