Get list of columns where all records have null values in a table - SQL

Question

I have a table that contains millions of records and 50 odd columns, many of these columns have null values for all the records in the table. How do I write a SQL Query that tells me which columns have all null records?

Answer 1

You could use bool_and:

WITH sample (column1, column2, column3) AS (
    VALUES 
        ('LOREM IPSUM', 'LOREM IPSUM', NULL),
        ('LOREM IPSUM', 'LOREM IPSUM', NULL),
        ('LOREM IPSUM', 'LOREM IPSUM', NULL),
        ('LOREM IPSUM', 'LOREM IPSUM', NULL),
        ('LOREM IPSUM', NULL, NULL)
)
SELECT
    bool_and(column1 IS NULL) AS is_column1_entire_null, 
    bool_and(column2 IS NULL) AS is_column2_entire_null,
    bool_and(column3 IS NULL) AS is_column3_entire_null 
FROM 
    sample

A quote from the docs:

(...) true if all input values are true, otherwise false

Answer 2

If you want to know which columns have ALL null values meaning no row has a value a count of that row will be 0 so just check for that.

select concat_ws(',',
                 (case when count(col1) = 0 then 'col1' end),
                 (case when count(col2) = 0 then 'col2' end),
                 (case when count(col3) = 0 then 'col3' end),
                 . . .
                ) as columns_with_all_null_values
from t;

Answer 3

A brute force approach would be something like:

select concat_ws(',',
                 (case when count(*) <> count(col1) then 'col1' end),
                 (case when count(*) <> count(col2) then 'col2' end),
                 (case when count(*) <> count(col3) then 'col3' end),
                 . . .
                ) as columns_with_null_values
from t;