MySQL NULL Values: Handling Missing Data in SQL Queries

If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

How to Test for NULL Values?

To test for NULL values in MySQL, use the IS NULL or IS NOT NULL operators since NULL cannot be compared with = or !=. Here are different ways to test for NULL values:

The IS NULL Operator

The IS NULL operator is used to test for empty values (NULL values).

The following SQL lists all students with a NULL value in the "city" field:

Demo Database

Below is a selection from the "Students" table in the school_db database:

student_id	name	gender	city	age	score	course_id	email	phone_number
1	Alice	Female	Delhi	20	85	101	NULL	1234567890
2	Bob	Male	Mumbai	22	75	NULL	bob@email.com	NULL
3	Charlie	Male	Delhi	21	95	102	NULL	NULL
4	David	Male	Bangalore	23	65	101	david@email.com	NULL
5	Eve	Female	Mumbai	20	80	103	NULL	NULL
6	Frank	Male	Delhi	22	90	103	NULL	NULL
7	Alice	Female	Mumbai	19	60	102	NULL	NULL

Example

SELECT * FROM students WHERE city IS NULL;

Try it yourself

The IS NOT NULL Operator

The IS NOT NULL operator is used to test for non-empty values (NOT NULL values).

The following SQL lists all students with a value in the "city" field:

Example

SELECT * FROM students WHERE city IS NOT NULL;

Try it yourself

❮ Previous

Next ❯

Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.