CODEWITHSUNDEEP
sqlpostgresqlnull
gdmplt
gdmplt

Reputation: 33

How to know if the a column contains all NULLs

how to know if all the columns contain only null values, for example

ID         | col1     | col2      |col3       | col4     
-----------+----------+-----------+-----------+-----------
 1         | Null     |Null       | with value| with value
 2         |with value|Null       | with value| with value
 3         |with value|Null       | Null      | Null    
 4         |with value|Null       | with value| with value
 5         |with value|Null       | Null      | Null  
 6         |Null      |Null       | Null      | Null    
 7         |with value|Null       | with value| with value
 8         |with value|Null       | Null      | Null

only col2 is NULL, so the result should be col2 only. 

ID         | col2     |
-----------+----------+
 1         |Null      |
 2         |Null      |
 3         |Null      |
 4         |Null      |
 5         |Null      |
 6         |Null      |
 7         |Null      |
 8         |Null      |

Any help will be greatly appreciated.

Upvotes: 0

Views: 1197

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271013

Why would you want all rows for the columns that are all NULL? You know what the values are.

Further, a SQL query can only return a fixed set of columns, so you can't filter the columns. If you just want a list of the column names all of whose values are NULL, you can concatenate the names together:

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)
                )
from t;

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 247970

You count the columns that are not NULL:

SELECT 0 = count(col1) AS col1_all_null,
       0 = count(col2) AS col2_all_null,
       ...
FROM mytable;

This makes use of the fact that aggregate functions ignore NULL values (well, almost all at least).

Upvotes: 5

Related Questions