I have a table with some rows containing only NULL in all columns except the descrition column.
description | colA | colB
-------------------------
Peter | bla | NULL
Frank | NULL | NULL
George | NULL | blub
How do I select all rows with NULL in all columns without explicitly naming them?
Pseudocode: SELECT ``decription`` WHERE all other columns are NULL should return Frank. How do I accomplish that?
By refering to INFORMATION_SCHEMA and using PREPARE statement, one solution is here, with a full demo provided.
The solution refers to: Select all columns except one in MySQL?
SQL:
-- data
create table t1(description char(20), colA char(20), colB char(20));
insert into t1 values
( 'Peter' , 'bla', NULL),
( 'Frank' , NULL , NULL),
( 'George' , NULL , 'blub');
SELECT * FROM t1;
-- Query wanted
SET @sql = CONCAT(
'SELECT description FROM t1 WHERE COALESCE(',
(SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), 'description,', '')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 't1' AND TABLE_SCHEMA = 'test'),
') IS NULL');
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
Output:
mysql> SELECT * FROM t1;
+-------------+------+------+
| description | colA | colB |
+-------------+------+------+
| Peter | bla | NULL |
| Frank | NULL | NULL |
| George | NULL | blub |
+-------------+------+------+
3 rows in set (0.00 sec)
mysql>
mysql> SET @sql = CONCAT(
-> 'SELECT description FROM t1 WHERE COALESCE(',
-> (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), 'description,', '')
-> FROM INFORMATION_SCHEMA.COLUMNS
-> WHERE TABLE_NAME = 't1' AND TABLE_SCHEMA = 'test'),
-> ') IS NULL');
Query OK, 0 rows affected (0.00 sec)
mysql> PREPARE stmt1 FROM @sql;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE stmt1;
+-------------+
| description |
+-------------+
| Frank |
+-------------+
1 row in set (0.00 sec)
To elaborate the SET statement before PREPARE:
The SET is to generate a string as below.
SELECT description FROM t1 WHERE COALESCE( < list of all columns, except description > ) IS NULL
The is queried from INFORMATION_SCHEMA.COLUMNS, using the method in the reference link.
To use in your own environment, you need to
Change table name 't1' to your own table name;
Change TABLE_SCHEMA 'test' to your own database name.
SELECT description FROM TABLE_NAME WHERE colA is NULL AND colB is NULL
SELECT description FROM table_name WHERE colA is NULL and colB IS NULL