MySQL query to display only the empty and NULL values together?

To check for NULL, use the IS NULL. For empty values, you need to check with an empty string. We will now see an example.

Let us first create a table −

mysql> create table DemoTable691(
   PlayerId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   PlayerName varchar(100),
   PlayerScore int
);
Query OK, 0 rows affected (0.56 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable691(PlayerName,PlayerScore) values('Robert',56);
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable691(PlayerName,PlayerScore) values('David',89);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable691(PlayerName,PlayerScore) values('',98);
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable691(PlayerName,PlayerScore) values(null,71);
Query OK, 1 row affected (0.17 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable691;

This will produce the following output −

+----------+------------+-------------+
| PlayerId | PlayerName | PlayerScore |
+----------+------------+-------------+
|        1 | Robert     | 56          |
|        2 | David      | 89          |
|        3 |            | 98          |
|        4 | NULL       | 71          |
+----------+------------+-------------+
4 rows in set (0.00 sec)

Following is the MySQL query to display empty and NULL value together −

mysql> select *from DemoTable691 where PlayerName IS NULL OR PlayerName='';

This will produce the following output −

+----------+------------+-------------+
| PlayerId | PlayerName | PlayerScore |
+----------+------------+-------------+
|        3 |            |          98 |
|        4 | NULL       |          71 |
+----------+------------+-------------+
2 rows in set (0.00 sec)

AmitDiwan

Updated on 26-Aug-2019 08:52:04

• Related Questions & Answers
• MySQL query to compare and display only the rows with NULL values
• MySQL query to convert empty values to NULL?
• Return only the non-empty and non-null values from a table and fill the empty and NULL values with the corresponding column values in MySQL?
• MySQL query to replace only the NULL values from the table?
• GROUP BY and display only non-empty column values in MySQL
• Display only NOT NULL values from a column with NULL and NOT NULL records in MySQL
• Query non-empty values of a row first in ascending order and then display NULL values
• How MySQL handles the empty and null values for enumerations?
• Ignore null values in MySQL and display rest of the values
• MySQL query to display only the column values with corresponding column having whitespace
• MySQL query to order by NULL values
• Count only null values in two different columns and display in one MySQL select statement?
• Display and concatenate records ignoring NULL values in MySQL
• MySQL query to display custom text for empty columns
• Ignore NULL values from separate tables in a single MySQL query and display count of NOT NULL records