MySQL NULL Values

NULL means that a value is missing or unknown.

SELECT *
FROM students
WHERE mobile IS NULL;

This finds students whose mobile number has no value.

NULL and zero are completely different.

A column containing 0 does not mean that the column contains NULL.

An empty string '' is different from NULL.

INSERT INTO students (name, email)
VALUES ('Rahul', '');

Here, email contains an empty string, not NULL.

The IS NULL operator is used to find NULL values.

SELECT *
FROM students
WHERE email IS NULL;

This returns rows where email has a NULL value.

The IS NOT NULL operator finds values that are not NULL.

SELECT *
FROM students
WHERE email IS NOT NULL;

This returns students whose email has a value.

Do not use = NULL to check for NULL.

-- Incorrect
SELECT *
FROM students
WHERE email = NULL;

Use:

SELECT *
FROM students
WHERE email IS NULL;

Similarly, do not use != NULL to find non-NULL values.

-- Incorrect
WHERE email != NULL;

Use:

WHERE email IS NOT NULL;

By default, a column may allow NULL unless a constraint such as NOT NULL prevents it.

CREATE TABLE students (
    id INT,
    name VARCHAR(100),
    mobile VARCHAR(15),
    email VARCHAR(150)
);

The mobile and email columns can contain NULL values in this example.

You can explicitly insert NULL into a nullable column.

INSERT INTO students
(name, mobile, email)
VALUES
('Amit', NULL, 'amit@gmail.com');

Here, the mobile value is NULL.

If a nullable column is omitted from an INSERT statement, MySQL can store NULL when no other default value is specified.

INSERT INTO students (name)
VALUES ('Priya');

Nullable columns not supplied in the statement can remain NULL.

You can set a nullable column to NULL using UPDATE.

UPDATE students
SET mobile = NULL
WHERE id = 5;

This removes the mobile value from the selected row by setting it to NULL.

Use IS NULL whenever you need to find missing values.

SELECT id, name, email
FROM students
WHERE email IS NULL;

This is a common way to find incomplete student records.

SELECT id, name, email
FROM students
WHERE email IS NOT NULL;

This returns only records where the email column contains a non-NULL value.

IS NULL can be combined with AND.

SELECT *
FROM students
WHERE email IS NULL
AND age >= 18;

This finds adults whose email is missing.

IS NULL can also be combined with OR.

SELECT *
FROM students
WHERE email IS NULL
OR mobile IS NULL;

This finds students missing either email or mobile information.

IS NOT NULL can be used to exclude missing values.

SELECT *
FROM students
WHERE email IS NOT NULL;

This is the practical form of checking that a value is present.

NULL values can appear when results are sorted.

SELECT name, fee
FROM students
ORDER BY fee ASC;

When working with NULL sorting, the position of NULL values depends on the sorting direction and expression being used.

COUNT(column_name) counts non-NULL values in that column.

SELECT COUNT(email)
FROM students;

This counts rows where email is not NULL.

By contrast:

SELECT COUNT(*)
FROM students;

COUNT(*) counts rows regardless of whether individual columns contain NULL.

Many aggregate functions ignore NULL values.

SELECT AVG(fee)
FROM students;

NULL fee values are not included in the calculation.

Functions such as SUM(), AVG(), MIN(), and MAX() generally ignore NULL values.

COALESCE() can return an alternative value when a value is NULL.

SELECT name,
COALESCE(email, 'Not Available') AS email
FROM students;

If email is NULL, the query displays Not Available.

MySQL also provides the IFNULL() function.

SELECT name,
IFNULL(email, 'No Email') AS email
FROM students;

If email is NULL, IFNULL() returns the replacement value.

Be careful when NULL values are involved with NOT IN. NULL does not behave like an ordinary value in comparisons.

SELECT *
FROM students
WHERE course NOT IN ('Python', 'Java');

Rows with NULL course values do not satisfy this condition in the same way as ordinary non-matching values.

LIKE is not the correct operator for finding NULL values.

-- Correct
SELECT *
FROM students
WHERE email IS NULL;

Use LIKE when searching for a text pattern and IS NULL when checking for NULL.

IS NULL can be used to delete records containing missing values.

DELETE FROM students
WHERE email IS NULL;

IS NULL can be used to identify records that need information updated.

UPDATE students
SET email = 'unknown@example.com'
WHERE email IS NULL;

This replaces NULL email values with the specified email value.

• Using = NULL instead of IS NULL
• Using != NULL instead of IS NOT NULL
• Confusing NULL with zero
• Confusing NULL with an empty string
• Using LIKE to find NULL values
• Forgetting that many aggregate functions ignore NULL values
• Deleting NULL records without checking them first

A simple NULL-checking workflow is:

1. Identify the column.
2. Decide whether you need NULL or non-NULL records.
3. Use IS NULL or IS NOT NULL.
4. Combine with other conditions if required.
5. Test using SELECT before UPDATE or DELETE.

SELECT id, name, email
FROM students
WHERE email IS NULL;

These values should not be treated as identical.

Suppose we want to find students who have either a missing email or a missing mobile number.

SELECT id, name, mobile, email
FROM students
WHERE email IS NULL
OR mobile IS NULL
ORDER BY name ASC;

This is useful for finding incomplete student records.

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    course VARCHAR(100),
    mobile VARCHAR(15),
    email VARCHAR(150),
    fee DECIMAL(10,2)
);

INSERT INTO students
(name, course, mobile, email, fee)
VALUES
('Amit', 'Python', '9876543210', 'amit@gmail.com', 15000.00),
('Priya', 'Java', NULL, 'priya@gmail.com', 18000.00),
('Rahul', 'PHP', '9876500000', NULL, 12000.00),
('Neha', 'Python', NULL, NULL, 15000.00);

SELECT id, name, mobile, email
FROM students
WHERE mobile IS NULL
OR email IS NULL
ORDER BY name ASC;

This query finds students who have a missing mobile number or email address.