Imagine This
You’re collecting a list of student grades, but some students haven’t submitted their homework yet. So their grade is just blank — not 0, not absent, just unknown.
In databases, that “blank” is called NULL — it means “no value”.
SQL gives us tools to check for NULLs or even replace them with default values when needed.
Your Table: grades
| student | subject | score |
|---|---|---|
| Alice | Math | 85 |
| Bob | Math | NULL |
| Charlie | Math | 92 |
| Diana | Math | NULL |
1. Checking for NULL: IS NULL
SELECT student
FROM grades
WHERE score IS NULL;Output:
| student |
|---|
| Bob |
| Diana |
This query asks: “Which students have no score recorded?” You can’t use = NULL — you have to use IS NULL.
2. Replacing NULLs: COALESCE()
SELECT student, COALESCE(score, 0) AS final_score
FROM grades;Output:
| student | final_score |
|---|---|
| Alice | 85 |
| Bob | 0 |
| Charlie | 92 |
| Diana | 0 |
COALESCE(score, 0) checks if score is NULL. If it is, it replaces it with 0. Otherwise, it shows the actual score.
3. Another Option: IFNULL() (MySQL, SQLite)
SELECT student, IFNULL(score, 0) AS final_score
FROM grades;
This works the same way as COALESCE(), but it’s specific to MySQL and SQLite. It only supports two arguments.
Summary
| Function | Use Case |
|---|---|
| IS NULL | Check if a value is missing |
| COALESCE() | Replace NULL with the first non-null value |
| IFNULL() | Replace NULL with a value (MySQL/SQLite) |