Return columns which has NULL or 0 Values in a row

Question

I've a Table with 10 fields. Each field or column contains Integer values.

Now I need only field(s) to be returned which has Null or 0 in the result set.

Solution 1

This worked for me. For instance:

Instead of:

where column_name is null or column_name = 0

It would be:

where COALESCE(column_name,0) = 0

Posted by Jon Gabrielson on December 18, 2002

The function 'COALESCE' can simplify working with null values. for example, to treat null as zero, you can use: select COALESCE(colname,0) from table where COALESCE(colname,0) > 1;

Solution 2

Use where column_name is null or column_name = 0

Solution 3

It's not clear what you are asking.

Can you elaborate a bit on what the resultset should look like, do you want all 10 columns returned, but only include the rows that have at least one column containing NULL or 0? That's very easy to do, by specifying appropriate predicates in the WHERE clause.

SELECT col0, col1, col2, col3, col4, col5, col6, col7, col8, col9
  FROM mytable
 WHERE IFNULL(col0,0) = 0
    OR IFNULL(col1,0) = 0
    OR IFNULL(col2,0) = 0
    OR IFNULL(col3,0) = 0
    OR IFNULL(col4,0) = 0
    OR IFNULL(col5,0) = 0
    OR IFNULL(col6,0) = 0
    OR IFNULL(col7,0) = 0
    OR IFNULL(col8,0) = 0
    OR IFNULL(col9,0) = 0

That will return all rows that have a zero or NULL in at least one of the specified columns.

But your question seems to be asking about something a little bit different; you seem to be asking about returning only certain columns based on conditions. The columns to be returned in the result set are determined by the list of expressions following the SELECT keyword. You can't dynamically alter the expressions in the SELECT list based on the values the column contain.

To return the names of the columns which have at least one row that contains a NULL or zero in that column, you could write a query like this (this is limited to 5 columns, could be easily extended to 10 or more columns):

SELECT 'col0' AS col_name FROM mytable WHERE IFNULL(col0,0) = 0
 UNION SELECT 'col1' FROM mytable WHERE IFNULL(col1,0) = 0
 UNION SELECT 'col2' FROM mytable WHERE IFNULL(col2,0) = 0
 UNION SELECT 'col3' FROM mytable WHERE IFNULL(col3,0) = 0
 UNION SELECT 'col4' FROM mytable WHERE IFNULL(col4,0) = 0

(That query is going to do some serious scanning through the table. If indexes are available, the predicates can be rewritten to allow for index range scan.)

Here's a way to to the column_names in a single row. (A NULL in one of the columns would mean that the column does not contain any zeros or NULL.)

SELECT (SELECT 'col0' FROM mytable WHERE IFNULL(col0,0)=0 LIMIT 1) AS col0
     , (SELECT 'col1' FROM mytable WHERE IFNULL(col1,0)=0 LIMIT 1) AS col1
     , (SELECT 'col2' FROM mytable WHERE IFNULL(col2,0)=0 LIMIT 1) AS col2
     , (SELECT 'col3' FROM mytable WHERE IFNULL(col3,0)=0 LIMIT 1) AS col3
     , (SELECT 'col4' FROM mytable WHERE IFNULL(col4,0)=0 LIMIT 1) AS col4

But it would be much faster to do a single scan through the table:

SELECT IF(c0>0,'col0',NULL)
     , IF(c1>0,'col1',NULL)
     , IF(c2>0,'col2',NULL)
     , IF(c3>0,'col3',NULL)
     , IF(c4>0,'col4',NULL)
  FROM ( SELECT SUM(IF(IFNULL(col0,0)=0,1,0)) AS c0
              , SUM(IF(IFNULL(col1,0)=0,1,0)) AS c1 
              , SUM(IF(IFNULL(col2,0)=0,1,0)) AS c2 
              , SUM(IF(IFNULL(col3,0)=0,1,0)) AS c3
              , SUM(IF(IFNULL(col3,0)=0,1,0)) AS c4
         FROM mytable 
       )