How to find records with a null value in a set of columns with MySQL

For this, use the concept of GREATEST(). Let us first create a table −

mysql> create table DemoTable1862
     (
     Value1 int,
     Value2 int,
     Value3 int,
     Value4 int
     );
Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1862 values(43,34,56,42);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1862 values(NULL,78,65,NULL);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1862 values(110,NULL,78,NULL);
Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1862;

This will produce the following output −

+--------+--------+--------+--------+
| Value1 | Value2 | Value3 | Value4 |
+--------+--------+--------+--------+
|     43 |     34 |     56 |     42 |
|   NULL |     78 |     65 |   NULL |
|    110 |   NULL |     78 |   NULL |
+--------+--------+--------+--------+
3 rows in set (0.00 sec)

Here is the query to find records with a null value in a set of columns −

mysql> select * from DemoTable1862 where greatest(Value1,Value2,Value3,Value4) IS NULL;

This will produce the following output −

+--------+--------+--------+--------+
| Value1 | Value2 | Value3 | Value4 |
+--------+--------+--------+--------+
|   NULL |     78 |     65 |   NULL |
|    110 |   NULL |     78 |   NULL |
+--------+--------+--------+--------+
2 rows in set (0.00 sec)

AmitDiwan

Published on 26-Dec-2019 06:51:44

• Related Questions & Answers
• MySQL: How can I find a value with special character and replace with NULL?
• Set all the columns of a MySQL table to a particular value with a single query
• Selecting records within a range and with a condition set on two columns in MySQL?
• Find “greatest” between two columns and display with some records already null in MySql
• Display only NOT NULL values from a column with NULL and NOT NULL records in MySQL
• How to select records that begin with a specific value in MySQL?
• Display NULL and NOT NULL records except a single specific value in MySQL
• How to find rows with exact value in one or more columns with MySQL?
• How to delete a single value from a MySQL table with duplicate records?
• Populate null columns in a MySQL table and set values
• Find the records with % character in a LIKE query with MySQL
• Find records with a specific last digit in column with MySQL
• MySQL queries to update date records with NULL values
• How to retrieve the corresponding value for NULL with a MySQL query?
• Find records with double quotes in a MySQL column?