CODEWITHSUNDEEP
sqlsql-servert-sql
bitsmuggler
bitsmuggler

Reputation: 1729

Check if any column is NOT NULL

I need to check whether a column is NOT NULL in my SQL statement.

My SQL query:

select column_a, column_b, column_c, column_d, column_x
from myTable

I've a lot of columns in my select. So I've got a performance issue, If I would do the following:

select column_a, column_b, column_c, column_d, column_x
from myTable
where column_a is not null or column_b is not null or column_c is not null 
or column_x  is not null

Is there another (better) way to check if there are any columns that are NOT NULL?

Upvotes: 19

Views: 50584

Answers (4)

Juozas
Juozas

Reputation: 935

Answer accepted 5 years ago, but, as said Brad, by question title coalesce is wrong approach. If in some cases you really need to check or ANY parameter is null, you can use this:

where convert(binary, column_a) + convert(binary, column_b) + convert(binary, column_c), + convert(binary, column_k) is null

Upvotes: 1

Solomon Rutzky
Solomon Rutzky

Reputation: 48874

I generally like @RedFilter's suggestion of COALESCE, but another solution might be to use the CHECKSUM() function. Of course, the value of the checksum for all NULLs depends on the columns and datatypes so you would need to first run a query to get that value. Something like:

select CHECKSUM(*) AS [All_NULL_Value]
from myTable
where column_a is null
AND column_b is null
AND column_c is null
AND column_d is null
AND column_x  is null

Then you can do this:

select column_a, column_b, column_c, column_d, column_x
from myTable
where CHECKSUM(*) <> {All_NULL_Value_obtained_above}

I am not sure if this performs better or worse than the COALESCE idea but might be worth a try.

Upvotes: 2

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171559

You can use COALESCE for this. COALESCE returns the first non-null value, if any. This will likely not perform any better, but is much more readable.

Example:

where coalesce(column_a, column_b, column_c, column_x) is not null

Depending on the cardinality of your data, you may be able to add indexes to help performance.

Another possibility is to use persisted computed column that tells you whether all four columns are NULL or not.

Upvotes: 28

RQDQ
RQDQ

Reputation: 15579

One way to attack this might be to add an additional bit column that keeps track of whether there are any values or not.

Pros

  • Can be implemented with triggers so you don't need to change the rest of your code
  • Doesn't require scanning the other columns
  • That column can be indexed

Cons

  • Your data would be de-normalized
  • More complicated / more maintenance
  • More storage space for the additional column

Whether the pros outweigh the cons depend on how much of a performance hit you're taking by looking at the other columns. Profile it before committing!

Upvotes: 3

Related Questions