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

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

Previous Page Print Page