WHERE all IS NOT NULL

Question

Hi, how can I run a MySQL query that selects everything that is not null? It would be something like

SELECT * FROM schedule WHERE ((all)) IS NOT NULL

Do I just remove the all and go..?

SELECT * FROM schedule WHERE IS NOT NULL

Thanks!

Answer 1

You'll have to explicitly state the condition on each column, so e.g.

select * from schedule 
  where id is not null and foo is not null and bar is not null; -- and so on..

Answer 2

It depend on what you mean exactly by "everything that is not null":

• all columns must not be null

  select * from schedule where col1 is not null AND col2 is not null AND ..

• at least one column should be non-null

  select * from schedule where col1 is not null OR col 2 is not null OR ..

At least this is how you would do it in "general sql". I don't know if MySql has special syntax for this.

Answer 3

You can concatenate the fields in order to write only a where-condition:

SELECT *
  FROM SCHEDULE
 WHERE ID || FOO || BAR IS NOT NULL;

Answer 4

If you are using another programming language combined with sql, you could speed up this process by looping through a list of column names and using a parameter to do the if not null check rather than have to type them all in individually e.g

**code SQL**

Select *
FROM Schedule
WHERE @ColumnName IS NOT NULL

**code c#**

for(int i =0; i<parameterArray[].length; i++)
{
command.Parameters.Add(new SqlParameter("ColumnName", parameterArray[i]));
command.Execute();
}

I would suggest using transactions so you can do the above in one batch after the for loop has run through.

Answer 5

You need to get a list of the columns of your table, by looking at the information_schema database.

Let's suppose that your database is called mydata and your table in question is named mytable. You can get the list of the table's nullable columns by issuing the following statement:

SELECT `COLUMN_NAME`
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'mydata'
    AND `TABLE_NAME` = 'mytable'
    AND `IS_NULLABLE` = 'YES'

Our final query will look like this:

SELECT * FROM `mydata`.`mytable`
WHERE CONCAT(<list of columns>) IS NOT NULL

All we are missing now is the list of nullable columns, comma-separated. We're going to use the GROUP_CONCAT function to produce the final statement, which we will execute like this:

SET @query = CONCAT(
    'SELECT * FROM `mydata`.`mytable` WHERE CONCAT(',
    (SELECT GROUP_CONCAT(COLUMN_NAME)
        FROM `information_schema`.`COLUMNS`
        WHERE `TABLE_SCHEMA` = 'mydata' AND
        `TABLE_NAME` = 'mytable'
        AND `IS_NULLABLE` = 'YES'),
    ') IS NOT NULL');

PREPARE stmt_name FROM @query;

EXECUTE stmt_name;

References:

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html

Answer 6

I've just looked at your linked question and agree with Guffa's answer that you should normalise your database.

The above looks more like a spreadsheet then something that belongs in an RDBMS.

To answer your concerns about this being the way you want it displayed. You could write a pivot query and put it in a view to mimic your current table structure and use this for your display query.

This will avoid the need for some horrific 24 column WHERE clause whenever you want to search for data or find out if it is NULL as in this question.