Excluding matches on JOIN fields that are NULL

Question

If you do a join that looks like this

SELECT T1.KeyField1, T1.KeyField2, T2.Field3
FROM T1 JOIN T2 ON T1.KeyField1 = T2.KeyField1 AND T1.KeyField2 = T2.KeyField2

Is there a way to not allow NULLS to match similar to the results this query would return

SELECT T1.KeyField1, T1.KeyField2, T2.Field3
FROM T1 JOIN T2 ON T1.KeyField1 = T2.KeyField1 AND T1.KeyField2 = T2.KeyField2
               AND T1.KeyField2 IS NOT NULL AND T2.KeyField2 IS NOT NULL

EDIT

I actually asked the question wrong.... Let me try again.

We are comparing an new data to old data and looking for records where the rows are exactly the same.

So both tables defined:

CREATE TABLE [Table](
    [Identifier] [int] IDENTITY(1,1) NOT NULL,
    [Key1] [varchar](50) NOT NULL,
    [Data1] [varchar](50) NULL,
    [Data2] [varchar](50) NULL

If I do the query:

DELETE
FROM T1 JOIN T2 ON T1.Key1 = T2.Key1 
               AND T1.Data1 = T2.Data2 AND T1.Data2 = T2.Data2

Give

T1 & T2

| Key1 | Data1       | Data2   |
| 1000 | 123 Main St | <NULL>  |
| 1001 | 456 High St | FLOOR 2 |

This would not remove the duplicate record 1000 from T1 since Data2 is NULL.

Outside of making use of a magic value in the join, is there any other way to compare these?

I understand that I should make the consultants rewrite the code to insert all NULLS as '', but this is a huge undertaking at this point. I am also looking at hashing the row to look for differences.

Answer 1

Have you considered the somewhat laborious

DELETE
FROM T1 JOIN T2 ON T1.Key1 = T2.Key1 
               AND 
               (T1.Data1 = T2.Data1
                OR 
                   (T1.Data1 is Null AND T2.data1 is Null)
               )
               AND
               (T1.Data2 = T2.Data2
                OR 
                   (T1.Data2 is Null AND T2.Data2 is Null)
               )