19

I'm trying to figure out an easy query I can do to test if a large table has a list of entries that has at least ONE blank (NULL / empty) value in ANY column.

I need something like

SELECT * FROM table AS t WHERE ANY(t.* IS NULL)

I don't want to have to do

SELECT * FROM table AS t WHERE t.c1 = NULL OR t.c2 = NULL OR t.c3 = NULL

This would be a HUGE query.

0

6 Answers 6

20

An extension to @db2's answer with less (read:zero) hand-wrangling:

DECLARE @tb nvarchar(512) = N'dbo.[table]';

DECLARE @sql nvarchar(max) = N'SELECT * FROM ' + @tb
    + N' WHERE 1 = 0';

SELECT @sql += N' OR ' + QUOTENAME(name) + N' IS NULL'
    FROM sys.columns 
    WHERE [object_id] = OBJECT_ID(@tb)
    AND is_nullable = 1;

EXEC sys.sp_executesql @sql;
0
9

You should list out all the columns as per JNK's comment.

WHERE c1 IS NULL OR c2 IS NULL OR c3 IS NULL

A somewhat less efficient approach that avoids this is below though.

;WITH xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' AS ns) 
SELECT * 
FROM   YourTable AS T1 
WHERE (
    SELECT T1.* 
    FOR XML PATH('row'), ELEMENTS XSINIL, TYPE
  ).exist('//*/@ns:nil') = 1 

(Based on this SO answer)

0
5

There's no nice built-in syntax, but Management Studio has a couple convenient features to generate the query quickly.

In Object Explorer, drill down to the table you want, expand it, then drag the whole "Columns" folder into a blank query editor. This will add a comma-separated list of columns to the query.

Next, open up Find And Replace. Set "Find What" to , and set "Replace With" to IS NULL OR (with a leading space) then hit Replace All. You'll have to clean up the last one in the sequence by hand.

It's still ugly, but it's less labor intensive ugly.

4

Multiple Solutions for: some nulls, all nulls, single & multiple columns plus making it QUICK using Top 1

If you need to test multiple columns, you could use the following:

Column_1 Column_2 Column_3
-------- -------- --------
1        2        NULL
1        NULL     NULL
5        6        NULL

First, test for NULLs and count them:

select 
    sum(case when Column_1 is null then 1 else 0 end) as Column_1, 
    sum(case when Column_2 is null then 1 else 0 end) as Column_2, 
    sum(case when Column_3 is null then 1 else 0 end) as Column_3,
from TestTable 

Yields a count of NULLs:

Column_1  Column_2  Column_3
0         1         3

Where the result is 0, there are no NULLs.

Second, let's count the non-NULLs:

select 
    sum(case when Column_1 is null then 0 else 1 end) as Column_1, 
    sum(case when Column_2 is null then 0 else 1 end) as Column_2, 
    sum(case when Column_3 is null then 0 else 1 end) as Column_3,
from TestTable

...But because we're counting non-NULLs here, this can be simplified to:

select 
    count(Column_1) as Column_1, 
    count(Column_2) as Column_2, 
    count(Column_3) as Column_3,
from TestTable

Either one yields:

Column_1  Column_2  Column_3
3         2         0

Where the result is 0, the column is entirely made up of NULLs.

Lastly, if you only need to check a specific column, then TOP 1 is quicker because it should stop at the first hit. You can then optionally use count(*) to give a boolean-style result:

select top 1 'There is at least one NULL' from TestTable where Column_3 is NULL

select count(*) from (select top 1 'There is at least one NULL' AS note from TestTable where Column_3 is NULL) a

0 = There are no NULLs, 1 = There is at least one NULL

or

select top 1 'There is at least one non-NULL' AS note from TestTable where Column_3 is not NULL

select count(*) from (select top 1 'There is at least one non-NULL' AS note from TestTable where Column_3 is not NULL) a

0 = They are all NULL, 1 = There is at least one non-NULL

I hope this helps.

3
  • While this seems quite useful, I do feel an obligation to note that it's not what the OP was asking for - they wanted the contents of each row that included a NULL value, not just a check to see if any exist.
    – RDFozz
    Jan 18, 2018 at 0:48
  • Fair enough. I think I was just reading it differently. I was focused on the "...test if a large table has..." part, so... Boolean (in my case Boolean-ish). But if, by "list of entries", he meant rows, then you're absolutely right.
    – jwolf
    Jan 19, 2018 at 2:44
  • Just revisited this. I definitely misinterpreted the question - should have inferred that he was looking for the rows as the result. I think I also misread what he meant by HUGE. I originally thought he meant computationally expensive but now I just think he meant wide with columns so Arron and DB2 got it right, both in the reading and the solutions (depending on which is more tired: your brain or your fingers)
    – jwolf
    Jan 19, 2018 at 7:54
2

UNPIVOT translates columns into rows. In the process it eliminates NULL values (reference).

Given the input

create table #t
(
    ID  int primary key,
    c1  int null,
    c2  int null
);

insert #t(id, c1, c2)
values
    (1, 12, 13),
    (2, null, 14),
    (3, 15, null),
    (4, null, null);

the UNPIVOT query

select
    ID, ColName, ColValue
from
(
    select *
    from #t
) as p
unpivot
(
    ColValue for ColName in
    (c1, c2)                  -- explicit source column names required
) as unpvt;

will produce the output

| ID | ColName | ColValue |
|----|---------|----------|
| 1  | c1      | 12       |
| 1  | c2      | 13       |
| 2  | c2      | 14       |
| 3  | c1      | 15       |

Sadly row 4 has been eliminated entirely since it has only NULLs! It can be conveniently re-introduced by injecting a dummy value into the source query:

select
    ID, ColName, ColValue
from
(
    select
        -5 as dummy,               -- injected here, -5 is arbitrary
        *
    from #t
) as p
unpivot
(
    ColValue for ColName in
    (dummy, c1, c2)                -- referenced here
) as unpvt;

By aggregating the rows on ID we can count the non-null values. A comparison to the total number of columns in the source table will identify rows containing one or more NULL.

select
    ID
from
(
    select -5 as dummy, *
    from #t
) as p
unpivot
(
    ColValue for ColName in
    (dummy, c1, c2)
) as unpvt
group by ID
having COUNT(*) <> 3;

I calculate 3 as
number of columns in source table #t
+ 1 for the injected dummy column
- 1 for ID, which is not UNPIVOTED

This value could be obtained at runtime by examining the catalog tables.

The original rows can be retrieved by joining to the results.

If values other than NULL are to be investigated they can be included in a where clause:

...
) as unpvt
where ColValue <> ''      -- will eliminate empty strings

Discussion

This requires an identifier that is carried through the UNPIVOT. A key would be best. If none exists one can be injected by the ROW_NUMBER() window function, though this may be expensive to execute.

All columns must be explicitly listed inside the UNPIVOT clause. They can be dragged in using SSMS, as @db2 suggested. It will not be dynamic when the table definition chagnes, as Aaron Bertrand's suggestion would be. This is the case for almost all SQL, however.

For my rather limited data set the execution plan is a clustered index scan and a stream aggregate. This will be more expensive of memory than a straight scan of the table and lots of OR clauses.

1

This could also help. It generates the script that lists the column name and the count of how many NULL values are in each of the columns in your table. Once you get the output, you'll have to copy or export it as a text and run it as a script.

DECLARE @tb VARCHAR(100) = '<schema>.<tableName>'

SELECT  'SELECT col_name = ''' + sys_cols.name + ''', null_count = COUNT(*) FROM ' + @tb + ' WHERE ' + sys_cols.name + ' IS NULL;'
FROM sys.columns sys_cols
WHERE sys_cols.[object_id] = OBJECT_ID('' + @tb + '')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.