MySQL IS NULL / IS NOT NULL

NULL in MySQL means the complete absence of a value — not zero, not an empty string, but nothing at all. Because of this special nature, you cannot compare NULL using the regular = or != operators. MySQL provides dedicated keywords instead: IS NULL to find missing values and IS NOT NULL to find rows that do have a value.

---

Why = NULL Does Not Work

This is one of the most common beginner mistakes in MySQL:

-- WRONG: This never returns any rows, even if phone is NULL
SELECT * FROM students WHERE phone = NULL;

-- WRONG: This also returns nothing
SELECT * FROM students WHERE phone != NULL;

In SQL, any comparison involving NULL — even NULL = NULL — evaluates to UNKNOWN, not TRUE or FALSE. Since WHERE only returns rows where the condition is TRUE, these queries return nothing.

---

Sample Table

This version of the students table has some missing phone numbers:

id	name	city	phone
1	Rahim Uddin	Dhaka	01711-000001
2	Sara Begum	Chittagong	NULL
3	Karim Ali	Sylhet	01811-000003
4	Nila Akter	Dhaka	NULL
5	Rafi Hossain	Rajshahi	01911-000005

---

IS NULL

Find all students who have no phone number on record:

SELECT name, city FROM students
WHERE phone IS NULL;

name	city
Sara Begum	Chittagong
Nila Akter	Dhaka

---

IS NOT NULL

Find all students who do have a phone number:

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

name	phone
Rahim Uddin	01711-000001
Karim Ali	01811-000003
Rafi Hossain	01911-000005

---

Using IS NULL with UPDATE and DELETE

Fill in a default value for all rows with a missing phone:

UPDATE students
SET phone = 'N/A'
WHERE phone IS NULL;

Remove all rows that have no city recorded:

DELETE FROM students
WHERE city IS NULL;

---

IS NULL with AND / OR

Combine IS NULL with other conditions just like any other comparison:

-- Students from Dhaka with no phone number
SELECT name FROM students
WHERE city = 'Dhaka' AND phone IS NULL;

-- Students who have no phone OR no city
SELECT name FROM students
WHERE phone IS NULL OR city IS NULL;

---

Key Points to Remember

• NULL means no value at all — it is not zero or an empty string.
• Never use = NULL or != NULL — they always return nothing because NULL comparisons are UNKNOWN.
• Use IS NULL to find rows where a column has no value.
• Use IS NOT NULL to find rows where a column does have a value.
• IS NULL and IS NOT NULL work in SELECT, UPDATE, and DELETE.
• You can combine them with AND and OR like any other condition.