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?)

Tetartohedral

That is the obvious way... I'm curious to see if someone can come up with a clever shorthand, though. (Although I suspect your method will be the most efficient) – Tetrarch

Answer 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

Quinquereme

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

Cusped

Answer 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

Undershrub

Answer 4

Another way to do it is

SELECT * FROM table1 WHERE val1 AND val2 AND val3 AND val4 is <> '';

This may be slightly shorter. Just make sure there is no space between the quotes.

Banket

Answer 5

Another way to just add columns like below

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

If any of the columns are not character datatype then that column has to be converted to char using cast function

Sedberry

Answer 6

Well there is no way to shorten it, but the query below can make it generic for any table.

DECLARE @tb NVARCHAR(255) = N'table1';

DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM ' + @tb
    + ' WHERE 1 = 1';

SELECT @sql += N' AND ' + QUOTENAME(name) + ' IS NOT NULL'
    FROM sys.columns 
    WHERE [object_id] = OBJECT_ID(@tb);

EXEC sp_executesql @sql;

replace 'table1' with the name of the table you are querying to and you will have the result.

Basically this dynamic sql queries the sys.columns table to get all the columns that belong to the table and appends the IS NOT NULL condition to it.

Blintze

Answer 7

SELECT *
FROM table1
WHERE CONCAT(val1, val2, val3, ...) is not Null

Herl

Answer 8

SELECT * FROM YOUR_TABLE where ((select REPLACE(WM_CONCAT(distinct COLUMN_NAME),',',' IS NOT NULL AND ') from all_tab_columns where OWNER = 'OWNER_DEV' and TABLE_NAME = 'YOUR_TABLE' ||'is not null' )) is not null;

Be sure to change :

  >> 1- YOUR_TABLE   by   the name of your table                                             
  >> 2- OWNER_DEV    by   the name of your owner

.

Driskill