Return columns that are NULL or 0 values ​​per row

Question

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

Now I need to get only the field (s) that has Null or 0 in the result set.

Answer 1

Use where column_name is null or column_name = 0

Answer 2

It is not clear what you are asking.

Can you talk a little about what should look like a result set, do you want to return all 10 columns, but include only rows containing at least one column containing NULL or 0? This is very easy to do by specifying the 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 

This will return all rows with null or null in at least one of the specified columns.

But your question seems to be asking about something a little different; you seem to be asking about the return of only certain columns based on conditions. The columns returned to the result set are determined by the list of expressions following the SELECT keyword. You cannot dynamically change expressions in a SELECT list based on the values ​​contained in a column.

To return the names of columns that have at least one row containing NULL or zero in this column, you can write such a query (this is limited to 5 columns, you can easily expand 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 

(This query will perform a serious scan through the table. If indexes are available, predicates can be rewritten to allow index range scanning.)

Here is the path to columns_name in one row. (NULL in one of the columns means that the column does not contain 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 perform one scan in 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

It worked for me. For instance:

Instead:

 where column_name is null or column_name = 0 

It will be:

 where COALESCE(column_name,0) = 0 

Posted by Jon Gabrielson on December 18, 2002

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

Answer 4

Recently I had to make a similar method in another language. The logic was to verify that all the “columns” in the record were “true” before they could be processed.

The way I got around this 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 ie 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 wrote this in PHP for you. This is probably possible in MySQL, but I would recommend that you get all the records, process them in PHP, and send them back to MySQL.

In the future, you should create your database table so that invalid entries are not allowed / saved in the first place.

Answer 5

Do you need columns with NULL or 0 or rows with a NULL or 0 column or rows where all columns are NULL or 0?

But I doubt that you will have 10 equivalent columns in a well-designed table, so you may need to redesign your data model — make rows of columns in another table and join 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 not needed if you delete all lines 0'd before starting it:

 DELETE FROM ab WHERE v = 0;