MySQL SELECT WHERE NULL

In this article, we will be discussing MySQL SELECT WHERE statement when column value is NULL. While working with strings, we mostly think that NULL is the same as an empty string(‘ ‘), but that is not a valid statement to make. In MySQL, NULL’s concept is different. When NULL is compared to any value, even NULL itself, the expression’s output can never be true. We will be going through examples to demonstrate the same.

We will get started by making the sample data to be used across the examples. The sample data includes creating a table named studentDetails and inserting some rows; a few column values will be inserted as NULL.

#creae table studentDetails
CREATE TABLE studentDetails (
student_id INT,
student_name VARCHAR(255),
total_score INT
);
#insert rows to studentDetails
INSERT INTO studentDetails(student_id,student_name,total_score) 
VALUES(1,'Daniel',455),
(2,'Sandy',500),
(3,'Ryma',NULL),
(4,'Jennifer',280),
(5,'Austin',458),
(6,'Veronica',500),
(7,' ',200),
(8,'Veronica', NULL),
(9,NULL,345),
(10,'Mounica',405),
(11,'Will',NULL),
(12,NULL,150);

Let us see how the data looks by executing:

SELECT * FROM studentDetails;

While working in MySQL, we can never compare a NULL with any value, and hence we cannot use the expression column name = NULL. Therefore let us look into the syntax of the same:

SELECT * FROM tableName WHERE columnName IS NULL;

• tableName: Name of the table on which we will execute the SELECT statement.
• columnName: Name of the column we are referring to for NULL values.

We will now be looking into a few examples to have a better understanding.

Example1: Get the details of the students who have total_score NULL.

What will happen if we execute the below query?

SELECT * FROM studentDetails WHERE total_score = NULL;

Action Output Message:-

0 row(s) returned.

Output:-

Output in image_2 shows that no row has been returned because any expression = NULL is not true.

Now let us execute the correct query.

SELECT * FROM studentDetails WHERE total_score = NULL;

Action Output Message:-

3 row(s) returned.

Output:-

Example2: Get the details of students with student_name NULL.

Note that the datatype of student_name is varchar, and for all datatypes, expression = NULL can never be true.

SELECT * FROM studentDetails WHERE student_name IS NULL;

Action Output Message:-

2 row(s) returned.

Output:-

READ MORE

• MySQL SELECT WHERE
• MySQL SELECT WHERE IN SubQuery
• MySQL SELECT WHERE LIKE
• MySQL SELECT WHERE NOT IN

We hope this article helped you with the SELECT statement where a column value is NULL. Good Luck !!!.