CODEWITHSUNDEEP
mysqlsql
magon
magon

Reputation: 81

Find columns with empty value in all rows of a table

i'm searching for an SQL request which gives me all columns in a table that are never populated.

For example, in a table like this one :

column 1 | column 2 | column 3 | column 4 | column 5
---------+----------+----------+----------+---------
value    | NULL     | value    | value    | NULL
NULL     | NULL     | value    | value    | NULL
value    | NULL     | value    | NULL     | NULL
value    | NULL     | value    | value    | NULL

The request will return : 

column 2, column 5

EDIT :

I've created this little PHP script to generate the query and print the result :

$columns = $sql->select("SELECT column_name FROM information_schema.COLUMNS WHERE TABLE_NAME='table_name'");

$query = "SELECT ";
foreach($columns as $column) {
    $query .= "case when count(".$column[0].") = 0 then '".$column[0]."' end, ";
}
$query = substr($query, 0,-2);
$query .= " FROM table_name";

var_dump($sql->select($query));

Upvotes: 1

Views: 1993

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can determine if a column contains no values by doing:

select 'column2'
from table t
having max(column2) is null;

You can repeat this for all columns using union all:

select 'column1'
from table t
having max(column1) is null
union all
select 'column2'
from table t
having max(column2) is null
. . .

This returns the results on multiple rows.

If you want a single row:

select concat_ws(', ',
                 (case when max(column1) is null then 'column1' end),
                 (case when max(column2) is null then 'column2' end),
                 (case when max(column3) is null then 'column3' end),
                 (case when max(column4) is null then 'column4' end),
                 (case when max(column5) is null then 'column5' end)
                )
from table t;

Upvotes: 3

jarlh
jarlh

Reputation: 44696

Something like:

select case when count(column1) = 0 then 'column 1' end,
       case when count(column2) = 0 then 'column 2' end,
       case when count(column3) = 0 then 'column 3' end,
       case when count(column4) = 0 then 'column 4' end,
       case when count(column5) = 0 then 'column 5' end
from tablename

I.e. count all values for a column, if 0 found return column name.

Upvotes: 3

Related Questions