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)

You mean there should not be any single null in any of the column/row? — Jignesh M. Khatri, Apr 13 '21 at 14:18
If you have 100 columns to check, you will write 100 x `and colx is not null` — Stu, Apr 13 '21 at 14:19
@JigneshM.Khatri yes. I want to select rows with no nulls in any field — conor, Apr 13 '21 at 14:22
Yeah so as suggested by others, there is no straight way to achieve this. You must need to write 100 AND clauses. — Jignesh M. Khatri, Apr 13 '21 at 14:26
If they are all numeric data type columns, you can save some typing: `WHERE col1 + col2 + ... + col100 IS NOT NULL`. — jarlh, Apr 13 '21 at 14:37
`WHERE CONCAT(col1, col2, ... , col100) IS NOT NULL` do the same for any scalar datatype. But separate checks are the most fast. — Akina, Apr 13 '21 at 14:54

score 6 · Accepted Answer · answered Apr 13 '21 at 15:02

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.

score 2 · Answer 2 · answered Apr 13 '21 at 15:02

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

Sushanth · Answer 3 · 2022-06-28T18:55:42.723

1

To return rows with no nulls in any columns: (in PostgresSQL)

SELECT *
FROM tablename
WHERE tablename.* IS NOT NULL

edited Jun 28 '22 at 18:55

answered May 02 '22 at 14:11

Sushanth

11
3

This wont work. – Shmiel May 06 '22 at 13:20
1

To VLQ Queue reviewers: [Wrong answers are not Very Low Quality](https://meta.stackoverflow.com/questions/345023/are-blatantly-wrong-answers-very-low-quality). Please vote "Looks OK". – EJoshuaS - Stand with Ukraine May 06 '22 at 22:11
See [db<>fiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=046743a6d6970a85ec036558a68412ff) – Shmiel Jun 27 '22 at 17:19
1

@Shmiel Thanks for spotting! This code works in Postgres SQL not mysql [db fiddle postgres](https://dbfiddle.uk/?rdbms=postgres_14&fiddle=046743a6d6970a85ec036558a68412ff) – Sushanth Jun 28 '22 at 18:58

score 0 · Answer 4 · edited Apr 13 '21 at 14:59

Get the length of each column and multiply them. If the result is not null then all columns are non null values. Try following sample code. Since I am not using multiple AND clause, it is a better approach.

CREATE TABLE #Temp (OdSal INT,NewSal INT,EmpName CHAR(20),IsCurrentEmp BIT)

INSERT INTO #Temp VALUES
(100,150,'Vikas',1),        -- No null records
(NULL,NULL,NULL,NULL),      -- all records are null
(NULL,NULL,'Nayanthara',1), -- more than 1 column is null
(NULL,150,'Priyamani',1)    -- only one column is null

--Will return only one row
SELECT * FROM #Temp
WHERE LEN(OdSal) * LEN(NewSal) * LEN(EmpName) * LEN(IsCurrentEmp) IS NOT NULL

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

Attempt

4 Answers4

Linked