Skip to main content

Querying Missing Values with IS NULL and IS NOT NULL

Premium

In SQL, a missing value is often represented by the keyword null. In many cases, we may need to filter our query results based on the presence of null values.

The two most common ways to handle missing values are using the IS NULL and IS NOT NULL operators.

The IS NULL operator returns rows where the specified column has a NULL value. The syntax is as follows:

SQL
SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NULL;

For example, consider the courses table. If we want to find all the courses with missing descriptions, we can use the following query:

SQL
SELECT *
FROM courses
WHERE description IS NULL;

Similarly, the IS NOT NULL operator returns rows where the specified column has a non-NULL value. The syntax is as follows:

SQL
SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NOT NULL;

For example, consider the courses table again. If we want to find all the courses with descriptions, we can use the following query:

SQL
SELECT *
FROM courses
WHERE description IS NOT NULL;

Remember: Syntax matters with NULL values

It's important to note that NULL values are different from zero, an empty string, or any other value. In SQL, NULL is a special marker used to indicate that a data value does not exist in the database, and therefore you should always use IS NULL or IS NOT NULL in your queries to avoid errors. You cannot use the normal comparison operators like = or != for null values.

For example, this query would yield the wrong answer:

SQL
– Incorrect
SELECT * FROM students WHERE email != NULL;

– Correct
SELECT * FROM students WHERE email IS NOT NULL;

In conclusion, using the IS NULL and IS NOT NULL operators allows you to handle missing values in your SQL queries.

Try it yourself

Given a table of customers, try to find all rows that are missing a last_name.

Next Lesson