CODEWITHSUNDEEP
sqlsql-server
Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

Optimize way of Null checking for multiple columns

Suppose I have a table with three columns and i want to take all rows where at least one column value is not null , right now i am using coalesce()below way for null checking and it is working fine

with Test_name AS(

    select null as id , null as f_name , null as l_name
    union ALL
    select 1,'fname1', null
    union ALL
    select null,null,null
) select tn.* from Test_name tn where coalesce(id,f_name,l_name) is not null

expected output and which gives me my query

1,'fname1', null

I am wonder is there any more better way to do this null checking

Upvotes: 3

Views: 4229

Answers (2)

Sebastian Brosch
Sebastian Brosch

Reputation: 43564

You can check the row for not NULL values in three ways:

  1. COALESCE(col1, col2, col3) IS NOT NULL
  2. col1 IS NOT NULL OR col2 IS NOT NULL OR col3 IS NOT NULL
  3. ISNULL(col1, ISNULL(col2, ISNULL(col3, NULL))) IS NOT NULL

You can use the Microsoft SQL Server Management Studio to compare multiple querys.

result of comparison:

  • COALESCE vs. IS NOT NULL: 57% to 43%
  • COALESCE vs. ISNULL: 56% to 44%
  • IS NOT NULL vs. ISNULL: 49% to 51%

So using IS NOT NULL is the fastest way to check if a row has a column without a NULL value. In case of readability the COALESCE can be much shorter than a IS NOT NULL or ISNULL comparison. You can decide between readability and speed.

Upvotes: 3

user11380812
user11380812

Reputation:

You can add computed column like this 

ALTER TABLE myTableName
ADD newColumnName AS (CASE
                    WHEN id IS NULL
                         AND fname IS NULL
                         AND lname IS NULL
                    THEN 0
                    ELSE 1
                END) PERSISTED

Then you can easily query your table 

SELECT 
*
FROM myTable 
WHERE newColumnName = 1

Upvotes: 1

Related Questions