mysqlsqlisnull

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

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)

Solution

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