how to select rows with no null values (in any column) in SQL?

Question

I have a table called table1

It has 100 columns: {col1, col2, ...,col100}

I understand how to SELECT rows not containing null values in a specific column for instance col1:

SELECT *
FROM table1
WHERE col1 IS NOT NULL

How do I SELECT all rows that do not contain null values in any column

Attempt

SELECT *
FROM table1
WHERE * IS NOT NULL

but this returns an error in MySQL (which I am using)

Answer 1

You need to explicitly list each column. I would recommend:

select t.*
from t
where col1 is not null and col2 is not null and . . . 

Some people might prefer a more concise (but slower) method such as:

where concat(col1, col2, col3, . . . ) is not null

This is not actually a simple way to express this, although you can construct the query using metadata table or a spreadsheet.

Answer 2

While I would recommend writing out each column name and refactoring your table as suggested, here's an option using dynamic sql:

SET @sql = NULL;
SELECT CONCAT('SELECT * FROM table1 WHERE ', 
              GROUP_CONCAT(c.COLUMN_NAME SEPARATOR ' IS NOT NULL AND '), 
              ' IS NOT NULL') INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = 'table1'
ORDER BY c.ORDINAL_POSITION;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

• DB Fiddle Demo