Checking for Null Values to Make Comparisons

Database
Enthusiast

Checking for Null Values to Make Comparisons

Hi,

What is the best structure to cover all possibilities of NULL between two fields?   I am comparing to two fields to see that they are different; however if either of the fields is NULL I need a statement to cover this eventuality.  Furthermore was a difference has been found then I need several business rules to be checked. 

Would the following structure be best?

,CASE
        WHEN (FIELD_1 IS NULL) AND (FIELD_2 IS NOT NULL)
                THEN CASE
                                  WHEN (FIELD_1 IS NOT NULL) AND (FIELD_2 IS NULL)
                                           THEN CASE
                                                            WHEN FIELD_1 <> FIELD_2
                                                                  --- BUSINESS RULE 1
                                                             THEN 'THIS IS A BUSINESS RULE 1 RESULT'

                                                             WHEN FIELD_1 <> FIELD_2
                                                                 --- BUSINESS RULE 2
                                                              THEN 'THIS IS A BUSINESS RULE 2 RESULT'

                                                             WHEN FIELD_1 <> FIELD_2
                                                                 --- BUSINESS RULE 3
                                                              THEN 'THIS IS A BUSINESS RULE 3 RESULT'
                                                     ELSE NULL
                                             END
                           ELSE NULL
                   END
  ELSE NULL
END AS BUSINESS_RULE_DTRMNT

Thanks in advance for your comments.

2 REPLIES
Junior Contributor

Re: Checking for Null Values to Make Comparisons

Seems like you want to return NULL if any of the columns is NULL. As any comparison to NULL will result in UNKNOWN and the default for ELSE is NULL you might simplify it like:

CASE WHEN FIELD_1 = FIELD_2 THEN 'equal'
WHEN FIELD_1 <> FIELD_2 THEN 'not equal'
END
Enthusiast

Re: Checking for Null Values to Make Comparisons

Hi Dieter, 

Because of the comparison of NULLs is unknown, I am fine with that.  Essentially nothing = nothing.   The query above is a snippet of the overall query:  Essentially what I am doing is comparing records.  A CTE is created to contain all records that were last updated and then a query pulling the previous records to that update all brought together by an outer join.

So I am trying to see what has changed.  If the records are equal or both  NULL, I don't care; however if one is NULL and the other is not then I need to identify that change.  You will notice at the begging of my code that I try to look at those possibility before going through the business rules once a difference has been identified.  

I have a feeling that there's a simpler way of doing this but I am finding it difficult to find it.