WHERE all IS NOT NULL

Question

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 

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

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