Selecting records where all columns have data and are not null

Question

I have a table with records that have blank/null data in certain columns and I want to find records where ALL columns have a value other than blank/null without creating a really long SQL statement.

EG:

SELECT * FROM table
WHERE col1 IS NOT NULL AND col2 IS NOT NULL AND col3 IS NOT NULL AND...

Is there any way to shorten this? Or is there any way to do this differently (with an SQL procedure maybe?)

Solution 1

The only thing I would do to shorten it would be to do something like:

SELECT * FROM table1 WHERE (val1 AND val2 AND val3 AND val4) IS NOT NULL

Solution 2

If you sometimes want to look only at the rows that contains data in all columns I would suggest creating a view based on the query you posted above. That way you can interact with it in a more elegant and shorter way.

A view is sort of a "virtual table" that is based off a query. If you regularly want to do some kind of complex joining or filtering then using a view can greatly simplify the queries you need to write elsewhere.

Solution 3

Sum up the ISNULL function on all columns.

SELECT * FROM table1 WHERE
ISNULL(val1)+ISNULL(val2)+ISNULL(val3)+ISNULL(val4)=0;

If it totals 0, then all columns have data

If the table you want to test simply has too many columns to check and it would be real chore to type, use INFORMATION_SCHEMA.COLUMN to sculpt the query for you.

I create a table called test.mytable which looks like this:

mysql> show create table test.mytable\G
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `nid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vid` int(10) unsigned NOT NULL DEFAULT '0',
  `type` varchar(32) NOT NULL DEFAULT '',
  `language` varchar(12) NOT NULL DEFAULT '',
  `title` varchar(255) NOT NULL DEFAULT '',
  `uid` int(11) NOT NULL DEFAULT '0',
  `status` int(11) NOT NULL DEFAULT '1',
  `created` int(11) NOT NULL DEFAULT '0',
  `changed` int(11) NOT NULL DEFAULT '0',
  `comment` int(11) NOT NULL DEFAULT '0',
  `promote` int(11) NOT NULL DEFAULT '0',
  `moderate` int(11) NOT NULL DEFAULT '0',
  `sticky` int(11) NOT NULL DEFAULT '0',
  `tnid` int(10) unsigned NOT NULL DEFAULT '0',
  `translate` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`nid`),
  UNIQUE KEY `vid` (`vid`),
  KEY `node_changed` (`changed`),
  KEY `node_created` (`created`),
  KEY `node_moderate` (`moderate`),
  KEY `node_promote_status` (`promote`,`status`),
  KEY `node_status_type` (`status`,`type`,`nid`),
  KEY `node_title_type` (`title`,`type`(4)),
  KEY `node_type` (`type`(4)),
  KEY `uid` (`uid`),
  KEY `tnid` (`tnid`),
  KEY `translate` (`translate`)
) ENGINE=InnoDB AUTO_INCREMENT=73798 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

You can use the following statements to generate my query for this table

SET @MyDB = 'test';
SET @MyTB = 'mytable';
SELECT CONCAT(GROUP_CONCAT(CONCAT('ISNULL(',column_name,')') SEPARATOR '+'),'=0')
INTO @WhereClause FROM information_schema.columns
WHERE table_schema=@MyDB AND table_name=@MyTB;
SET @SQLStmt = CONCAT('SELECT * FROM ',@MyDB,'.',@MyTB,' WHERE ',@WhereClause);
SELECT @SQLStmt\G

Let's run those statements and see what SQL is produced

mysql> SET @MyDB = 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MyTB = 'mytable';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT(GROUP_CONCAT(CONCAT('ISNULL(',column_name,')') SEPARATOR '+'),'=0')
    -> INTO @WhereClause FROM information_schema.columns
    -> WHERE table_schema=@MyDB AND table_name=@MyTB;
Query OK, 1 row affected (0.00 sec)

mysql> SET @SQLStmt = CONCAT('SELECT * FROM ',@MyDB,'.',@MyTB,' WHERE ',@WhereClause);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @SQLStmt\G
*************************** 1. row ***************************
@SQLStmt: SELECT * FROM test.mytable WHERE ISNULL(nid)+ISNULL(vid)+ISNULL(type)+ISNULL(language)+ISNULL(title)+ISNULL(uid)+ISNULL(status)+ISNULL(created)+ISNULL(changed)+ISNULL(comment)+ISNULL(promote)+ISNULL(moderate)+ISNULL(sticky)+ISNULL(tnid)+ISNULL(translate)=0
1 row in set (0.00 sec)

mysql>

From there, just execute the SQL using PREPARE or pass the SQL into PHP mysql_query