How to find rows where every column has NULL value

Question

I'm trying to filter out rows, which have NULL values in every column. Since there are around 30 columns, it seems unfeasible to type them out 1 by 1. Is there a way I can do this for the entire table?

I tried something like this

SELECT *
FROM TABLE1
WHERE column1 IS NULL AND column2 IS NULL -- etc...

However this is very time consuming, going through all the columns manually; how can I filter out rows, which have all column values as NULL?

You could execute a dynamically generated query. You cannot do this in pure SQLite though. If you are using the `sqlite3` command line you could use .once to write the generated statement to a file, then .import to execute it. — Allan Wind, Nov 25 '22 at 07:59
This may help https://stackoverflow.com/questions/67076546/how-to-select-rows-with-no-null-values-in-any-column-in-sql — Yacine Hajji, Nov 25 '22 at 08:28

JHH · Answer 1 · 2022-11-26T02:59:25.540

Maybe type a little and get the result you need:

-- step 1. get the column list of a table 
select group_concat(name) as column_list 
  from pragma_table_info('[table_name]');

-- step 2. select those ALL-NULL rows, [column_list] is from the above
select *
  from [table_name]
 where coalesce([column_list]) is null;

An example:

-- create a table 
create table t_null (
    c1  int,
    c2  int,
    c3  int);

insert into t_null
values 
(null, null, null),
(null, null, null),
(null, null, null);

-- get column list from metadata
select group_concat(name) as column_list 
  from pragma_table_info('t_null');

column_list|
-----------+
c1,c2,c3   |

-- 
select *
  from t_null
 where COALESCE(c1,c2,c3) is null;

How to find rows where every column has NULL value

1 Answers1