Home » MySQL

MySQL IS NOT NULL Operator

MySQL | IS NOT NULL Operator: Learn about the MySQL IS NOT NULL Operator, with its explanation, syntax, and query examples.
Submitted by Apurva Mathur, on September 14, 2022

IS NOT NULL Operator

In terms of a database management system, a null value is something that doesn't exist. A field with a null value is a field with no value. It is different from "0" or any real value. Usually, the null value tells us that value exists but it is not known or undefined for that row. So as the name suggests the IS NOT NULL operator gives you the data where the value IS NOT NULL.

IS NOT NULL Operator Syntax

SELECT Column1, Columns2, ...
FROM table_name
WHERE column_name IS NOT NULL;

Let us see some examples;

IS NOT NULL Operator Examples

Suppose we have a table named "student_details" and inside this table, you have the following columns,

CASE 1: Using IS NOT NULL Operator

As you can see in the above table that we have some NULL values in "student_department" and if we only want those values which are not null is the such case we can write,

SELECT * FROM student_details
WHERE student_department IS NOT NULL;

As you can see in the result, the student department column has 0 null values.

CASE 2: Using IS NOT NULL operator with any other operator

You can simply use this operator with other operators, as you can see the gender column also has null values so we can write a condition that will give us all the values excluding null values from the student department and gender column, in case we will use AND operator between two IS NOT NULL condition.

SELECT * FROM student_details
WHERE student_department IS NOT NULL and gender IS NOT NULL;

---