Find rows with empty columns without mentioning the column name
I want to find rows from my db tables that have empty columns.
Suppose my table has 10 columns and 100 rows, any of the 10 columns can be NULL / EMPTY.
Therefore I cannot use WHERE command
eg:
SELECT * FROM CUSTOMER WHERE REVENUE IS NULL OR ID IS NULL OR INCOME IS NULL ....(this goes on till 10 columns)
How can I write this query to select rows that have empty / null values (in any column).
source to share
The table information_schema.columns
contains column information for each table in each database in the system. We can extract the column names for your table from this and use it to create a prepared statement that we can execute to find your values.
Assuming your database is named foo
and your table is named test
, we can do this:
select concat("SELECT * FROM test WHERE ", group_concat(concat(column_name, " IS NULL ") SEPARATOR "OR "))
into @sql
from information_schema.columns
where table_name = 'test'
and table_schema = 'foo';
This will generate and save to a @sql
request that looks like this:
SELECT *
FROM test
WHERE id IS NULL
OR col1 IS NULL
OR col2 IS NULL
OR col3 IS NULL
OR col4 IS NULL
OR col5 IS NULL
We then prepare our statement as follows:
prepare stmt from @sql
And then we'll run it to get your values
execute stmt
Finally, we release the statement.
deallocate prepare stmt;
This would be an example of the output from this sequence:
mysql> select * from test;
+----+------+------+------+------+------+
| id | col1 | col2 | col3 | col4 | col5 |
+----+------+------+------+------+------+
| 1 | 1 | 2 | 3 | 4 | 5 |
| 2 | 1 | 2 | 3 | 4 | 5 |
| 3 | 1 | 2 | 3 | 4 | 5 |
| 4 | 1 | 2 | 3 | 4 | NULL |
| 5 | NULL | 2 | 3 | 4 | 5 |
| 6 | 1 | NULL | 3 | 4 | 5 |
+----+------+------+------+------+------+
6 rows in set (0.00 sec)
mysql> select concat("SELECT * FROM test WHERE ", group_concat(concat(column_name, " IS NULL ") SEPARATOR "OR "))
-> into @sql
-> from information_schema.columns
-> where table_name = 'test'
-> and table_schema = 'foo';
Query OK, 1 row affected (0.01 sec)
mysql> prepare stmt from @sql;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> execute stmt;
+----+------+------+------+------+------+
| id | col1 | col2 | col3 | col4 | col5 |
+----+------+------+------+------+------+
| 4 | 1 | 2 | 3 | 4 | NULL |
| 5 | NULL | 2 | 3 | 4 | 5 |
| 6 | 1 | NULL | 3 | 4 | 5 |
+----+------+------+------+------+------+
3 rows in set (0.00 sec)
mysql> deallocate prepare stmt;
Query OK, 0 rows affected (0.00 sec)
source to share