Columns evaluating to null correction

Question

Table 1:

ID   email
A    a@gmail.com

Table 2:

ID   email2        email3   email4
A    a@gmail.com   null     ab@gmail.com

Query:

select T1.ID, T1.email, 
case when T1.email<>T2.email2 then T2.email2 end email2,
case when T1.email<>T2.email3 and T2.email2<>T2.email3 then T2.email3 end email3,
case when T1.email<>T2.email4 and T2.email2<>T2.email4 and T2.email3<>T2.email4 then T2.email4 end email4
from t1 
left join t2 on t1.id=t2.id

Output:

ID  email         email2   email3   email4
A   a@gmail.com   null     null     null

Expected Output:

ID  email         email2   email3   email4
A   a@gmail.com   null     null     ab@gmail.com

It looks like email4 evaluates to null because email3 is null. I think I understand why as per Why does NULL = NULL evaluate to false in SQL server, but is there an easy way for me to avoid that from happening? The real query is alot more complex and if I add something like isnull(value,'') around every variable in the case statement to avoid comparing to a null value, that can get abit much.

Note that I dont have permission to change ansi_nulls settings and such

You already know the answer....wrap them with ISNULL. This is because nothing ever equals NULL so your case expressions return NULL. If you could normalize your data instead of email1, email2 etc this would be a lot simpler. What you have here violates 1NF. — Sean Lange, Oct 04 '18 at 19:50
"The real query is alot more complex and if I add something like `isnull(value,'')` around every variable in the case statement to avoid comparing to a null value, that can get abit much." Looking for an easier way than that if possible — user8834780, Oct 04 '18 at 19:57
There really isn't an easier way unless you can fix the design. You are struggling here with being forced to write horrific queries because your design lacks proper normalization. Either fix the design (not always possible) or get busy writing ISNULL. — Sean Lange, Oct 04 '18 at 19:59
The multiple email values come from an XML parsed column unfortunately, so not too much leeway. `isnull()` it is I guess — user8834780, Oct 04 '18 at 20:01
Parse it into a normalized table, then this becomes trivial and simple to write. — Sean Lange, Oct 04 '18 at 20:01

D-Shih · Accepted Answer · 2018-10-04T20:03:43.963

Because NULL means

I don't know.

So you can try to add check IS NULL in each CASE WHEN columns

 CREATE TABLE Table1(
  ID VARCHAR(50),
  email VARCHAR(50)
);

INSERT INTO Table1 VALUES ('A','a@gmail.com');

CREATE TABLE Table2(
  ID  VARCHAR(50),
  email2 VARCHAR(50),
   email3 VARCHAR(50),
    email4 VARCHAR(50)
);



INSERT INTO Table2 VALUES ('A','a@gmail.com',null,'ab@gmail.com');

Query 1:

select T1.ID, T1.email, 
case when T1.email<>T2.email2  then T2.email2 end email2,
case when T1.email<>T2.email3 and T2.email2<>T2.email3 then T2.email3 end email3,
case when (T1.email<>T2.email4 OR T1.email IS NULL) and 
            (T2.email2<>T2.email4  OR T2.email2 IS NULL)  and 
            (T2.email3<>T2.email4  OR T2.email3 IS NULL)  then T2.email4 end email4
from Table1 t1 
left join Table2 t2 on t1.id=t2.id

Results:

| ID |       email | email2 | email3 |       email4 |
|----|-------------|--------|--------|--------------|
|  A | a@gmail.com | (null) | (null) | ab@gmail.com |

The problem is, I need to have every single DISTINCT email returned in its own column; so if email3 is not null, email4 will not be returned — user8834780, Oct 04 '18 at 19:55
"I don't know" = "Unknown" might be a slightly more concise way to say it — Joe Phillips, Oct 04 '18 at 20:08

score 1 · Answer 2 · answered Oct 04 '18 at 20:07

"The real query is alot more complex and if I add something like isnull(value,'') around every variable in the case statement to avoid comparing to a null value, that can get abit much." Looking for an easier way than that if possible

You could ISNULL each column once in a derived table, then keep the rest of your query the same:

select T1.ID, T1.email, 
case when T1.email<>T2.email2 then T2.email2 end email2,
case when T1.email<>T2.email3 and T2.email2<>T2.email3 then T2.email3 end email3,
case when T1.email<>T2.email4 and T2.email2<>T2.email4 and T2.email3<>T2.email4 then T2.email4 end email4
from t1 
left join (SELECT ID, 
                  ISNULL(email2,'') email2,
                  ISNULL(email3,'') email3,
                  ISNULL(email4,'') email4
           FROM t2) t2 on t1.id=t2.id

Columns evaluating to null correction

2 Answers2