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.

Answer 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;

Answer 2

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 
       )

Answer 3

I recently had to make a similar method in another language. The logic was to check that all "columns" in a record were "true" before it could be processed.

The way I got around it was with this logic:

# loop through the records
for ($i=0; $i<count($records); $i++) {

  # in each record, if any column is 0 or null we will disgard it
  # this boolean will tell us if we can keep the record or not
  # default value is 'true', this gives it a chance to survive
  $keepit = true;

  # loop through each column
  for ($j=0; $j<count($records[$i]); $j++) {

    # add your boolean condition (true=go ahead, false=cant go ahead)
    $goahead = (!is_null($records[$i][$j]) && $records[$i][$j] != 0);

    # convert the boolean to a 0 or 1,
    # find the minimum number in a record
    # (so if any field in a record is false i.e. 0 or null, dont use the record)
    $keepit = min(+$goahead, +$keepit);

  }#end of col loop

  if ($keepit) {
    # keep the record
    array_push($anotherArray, $records[$i]);
  }

}

I've written this in PHP for you. It's probably doable in MySQL too but I'd recommend you fetch all of the records, process them in PHP, and send them back to MySQL.

In future, you should design your database table so that invalid records are not allowed/not stored in the first place.

Answer 4

Do you need the columns that have NULL or 0, or the rows with a column that is NULL or 0, or the rows where all columns are either NULL or 0?

But I doubt you would have 10 equivalent columns in a well-designed table, so you might want to re-design your data model -- make the columns rows in another table, and join the two tables together.

You might have something like this:

CREATE TABLE a(a_id int primary key, b0 int, b1 int, b2 int, ..., b9 int );

But you want this:

CREATE TABLE a( a_id int primary key );
CREATE TABLE b( b_id int primary key );

INSERT INTO b (b_id) values (0), (1), ..., (9);

CREATE TABLE ab (
  a_id int, b_id int,
  v int not null, -- this would be the value of b0, b1, ...
  foreign key (a_id) references a(a_id),
  foreign key (b_id) references b(b_id),
  primary key(a_id, b_id)
);

And then you can write something like:

SELECT * FROM a, b -- cross join to get all possible combinations
WHERE (a_id, b_id) NOT IN (
  -- then remove the ones that have a value
  SELECT a_id, b_id FROM a JOIN ab ON a.id = ab.a_id JOIN b ON ab.a_id
  WHERE ab.v <> 0);

The last line is unnecessary if, before running it, you delete all 0'd lines:

DELETE FROM ab WHERE v = 0;

Answer 5

Use where column_name is null or column_name = 0