Selection of records where all columns have data and are not null

Question

I have a table with records with empty / null data in certain columns, and I want to find records where ALL columns have a value other than empty / zero 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 cut this? Or is there a way to do this differently (using an SQL procedure, maybe?)

Answer 1

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

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

Answer 2

If you sometimes want to look only at rows containing data in all columns, I would suggest creating a view based on your query above. This way you can interact with it in a more elegant and shorter way.

A view is a kind of "virtual table" based on a query. If you regularly want to perform some kind of complex join or filtering, then using the view can greatly simplify the queries you need to write elsewhere.

Answer 3

Summarize the ISNULL function for all columns.

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

If it is 0, then all columns have data

If the table you want to test has too many columns to validate and this will be a real input task, use INFORMATION_SCHEMA.COLUMN to validate the query for you.

I create a test.mytable table that 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 operators 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 

Run these instructions and see which SQL is created.

 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 SQL using PREPARE or pass SQL to PHP mysql_query

Answer 4

Another way to do this is

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

It may be a little shorter. Just make sure there is no space between quotation marks.

Answer 5

Another way to just add columns is as below

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

If any of the columns is not a character data type, then this column must be converted to char using the translation function

Answer 6

Well, there is no way to shorten it, but the query below can make it common to 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 accessing, and you will get the result.

Basically this dynamic sql queries the sys.columns table to get all the columns belonging to the table and adds the IS NOT NULL condition to it.

Answer 7

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

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 

.