Check if columns are NULL or contains NULL in table. - MS SQL Server

Question

Following is my table (TestTable) where Column_3 is NULL.

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

As per functionality, user can select one or more columns. For example, if user selects Column_3 & Column_2 where Column_3 is NULL. I want to tell user that Column_3 is NULL.

Query : Works for single column

if exists(select * from TestTable where Column_3 is null)
    print 'Yes'
else
    print 'No'

Result :

Yes

Query : For multiple Columns (not working)

declare @columns nvarchar(max), @tableName nvarchar(max), @query nvarchar(max)

set @columns = 'Column_3, Column_2'
set @tableName = 'TestTable'
set @query = 'select * from (select ' + @columns + ' from ' + @tableName + ') as Result'
print @query
EXEC SP_EXECUTESQL @query

Here

@query = select * from (select Column_3, Column_2 from TestTable) as Result

Above query gives me result for those two columns. I'm not sure how can I check NULL in this query for multiple columns. If I add IS NULL (like I did for single column) after or before last parenthesis it gives me Incorrect syntax near the keyword 'is'. error. How can I achieve my goal in this situation?

if exists(select * from TestTable where Column_3 is null or Column_2 is null) — Pரதீப்
– Pரதீப், Commented Aug 6, 2015 at 15:26
Well the multiple column will be Column_3, Column_2 in format. — GThree
– GThree, Commented Aug 6, 2015 at 15:28

Community · Accepted Answer · 2020-06-20 09:12:55Z

Two Solutions (Column is All NULLs, Column Contains Some NULLs)

I have slightly altered your original example in order to provide two solutions:

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.

If you only need to check a given column, then TOP 1 is quicker because it should stop at the first hit:

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

SELECT COUNT(*) FROM (SELECT TOP 1 'There is at least one non-NULL' AS note FROM sat_data_active_season_group WHERE season_group IS NOT NULL) a

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

I hope this helps.

wiretext · Accepted Answer · 2015-08-06 15:51:18Z

3

we can check with the help of IN like

...WHERE NULL IN (Column_2, Column_3)

from your comment Well the multiple column will be Column_3, Column_2 in format might be this is helpful for you

select * from (select Column_3, Column_2 from @temp where null in (Column_3, Column_2)) as Result

edited Aug 6, 2015 at 15:51

answered Aug 6, 2015 at 15:39

wiretext

3,35216 silver badges19 bronze badges

1 Comment

J Kluseczka Over a year ago

In MySQL, expression NULL IN (Column_2, Column_3) always evaluates to NULL; "To comply with the SQL standard, IN() returns NULL [...] if the expression on the left hand side is NULL" ~ dev.mysql.com/doc/refman/8.0/en/…

Waqas Ahmed · Accepted Answer · 2015-08-06 15:41:11Z

1

Try as below.

You can find the null able column by using CASE.

Select CASE WHEN Column_3 IS NULL THEN 'Column 3 is null' ELSE Column_3 END as Column3,
CASE WHEN Column_2 IS NULL THEN 'Column 2 is null' ELSE Column_2 END as Column2
From TableName

answered Aug 6, 2015 at 15:41

Waqas Ahmed

4931 gold badge5 silver badges24 bronze badges

Comments

Bigsby · Accepted Answer · 2015-08-06 15:29:00Z

0

It sounds like you need a CASE statement. Reference

Example:

SELECT CASE
        WHEN ISNULL(Column_3) THEN -- do something
        WHEN NOT ISNULL(Column_3) THEN -- do something else
       AS Column_3 -- or some other name

answered Aug 6, 2015 at 15:29

Bigsby

9628 silver badges21 bronze badges

Collectives™ on Stack Overflow

Check if columns are NULL or contains NULL in table. - MS SQL Server

4 Answers 4

Two Solutions (Column is All NULLs, Column Contains Some NULLs)

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Two Solutions (Column is All NULLs, Column Contains Some NULLs)

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related