SQL Return rows with mix of nulls and non nulls in certain columns

June 24, 2022

If I have the following table

 id     a      b      c   time
 ----------------------------- 
 0      1      4   "ca"     23 
 1   NULL   NULL   NULL     18
 2   NULL      1   "pn"     13
 3      6   NULL   "ar"     27
 4      1      2   NULL     24

I want to return all rows with at least one null and one non-null in columns a, b, and c. So I want to return:

 id     a      b      c   time
 ----------------------------- 
 2   NULL      1   "pn"     13
 3      6   NULL   "ar"     27
 4      1      2   NULL     24

I know I can write

select *
from table
where ((a is null and (b is not null or c is not null))
or (a is not null and (b is null or c is null)))

But what happens if I need to consider 4 columns or more? It becomes a mess. Note that the table could have 20 or more columns, of which I am only considering a small subset of columns for null/non-null analysis. Is there a concise way of doing this? Thanks

>Solution :

One method would be to unpivot your data, and COUNT the NULL and non-NULL values, and filter on that:

SELECT V.ID,
       V.a,
       V.b,
       V.c,
       V.time
FROM (VALUES(0,1,4,'"ca"',23),
            (1,NULL,NULL,NULL,18),
            (2,NULL,1,'"pn"',13),
            (3,6,NULL,'"ar"',27),
            (4,1,2,NULL,24))V(ID,a,b,c,time)
     CROSS APPLY (SELECT COUNT(UP.V) AS NonNull,
                         COUNT(CASE WHEN UP.V IS NULL THEN 1 END) AS IsNull
                  FROM (VALUES(CONVERT(varchar(1),V.a)),
                              (CONVERT(varchar(1),V.b)),
                              (CONVERT(varchar(1),V.c)))UP(V))C
WHERE C.[IsNull] > 0
  AND C.NonNull > 0;