MYSQL get all rows with only NULL in all columns

Question

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?

Answer 1

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:

1. The SET is to generate a string as below.

   SELECT description FROM t1 WHERE COALESCE( < list of all columns, except description > ) IS NULL

2. The is queried from INFORMATION_SCHEMA.COLUMNS, using the method in the reference link.

To use in your own environment, you need to

1. Change table name 't1' to your own table name;

2. Change TABLE_SCHEMA 'test' to your own database name.

Answer 2

SELECT description FROM TABLE_NAME WHERE colA is NULL AND colB is NULL