How to display ALL the Non-Null and ALL the Non-Empty records without mentioning ALL the column-name in the where clause using a MySql query?

Question

Please consider that there are eight columns (col1, col2, ...col8) and the col1 contains primary_key.

Table1
col1: val1
col2: val2
col3: NULL
col4: 
col5: NULL
col6: NULL
col7: 
col8: val8

Tried:

Select * from Table1 
WHERE col1 and col2 and col3 and col4 and col5 and col6 and col7 and col8 IS NOT NULL \G

Expected:

But I do not want to mention all these column names in where clause because I am working with a large table with 100 columns.

Please help. Thanks in advance.

Caius Jard Caius Jard · Accepted Answer · 2017-11-29T12:06:51

You can't; something, somewhere is going to have to form a query where all the column names are mentioned. Perhaps it would benefit you to know though, that you can make you life a bit easier with text editor power features.

The following query in mySQL

SHOW COLUMNS FROM mydb.mytable

Should produce an output like this:

+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+

If you use a power text editor (this example is for sublime, most good text editors have this feature) it will have a column select mode. In sublime if you drag a selection with the middle mouse button, then you can highlight a square area of just the column names

Take these and paste them somewhere else, then use the middle mouse button again to draw a vertical line, zero wide at the end of the lines, then start typing:

As you can see, sublime is typing on all the lines simultaneously. This can greatly reduce your query writing chores and boring repetitive parts. Read up about your favorite text editor's advanced abilities. I like sublime because it can place multiple cursors anywhere with ctrl+click and then type on all of then at the same time - i havent found this feature in any other editor:

Your other options, if the table changes a lot, is to build the query in code/using SQL. Something like this, perhaps:

SELECT 'SELECT * FROM tableWhatever WHERE '
UNION ALL
SELECT CONCAT(`COLUMN_NAME`, ' is not null and ')
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='yourdatabasename' 
    AND `TABLE_NAME`='yourtablename'
UNION ALL
SELECT '1=1'

It would produce an SQL like this (multiple cells in your query editor's grid output, then copy the cells out of the query editor):

SELECT * FROM tableWhatever WHERE
 ID          is not null and 
 Name        is not null and 
 CountryCode is not null and 
 District    is not null and 
 Population  is not null and 
 1=1

it is syntactically correct. You can remove the last AND and the 1=1 if you want, using the backspace key..

Using SQL to write SQL can also save you a lot of typing.

How to display ALL the Non-Null and ALL the Non-Empty records without mentioning ALL the column-name in the where clause using a MySql query?

1 Answers