Excluding matches on JOIN fields that are NULL

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

SELECT T1.KeyField1, T1.KeyF开发者_如何学Cield2, 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.

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)
               )

try using this:

SET ANSI_NULLS ON

http://msdn.microsoft.com/en-us/library/aa259229(SQL.80).aspx

EDIT

joining with "magic numbers" like:

ISNULL(T1.Field1, '-9999') = ISNULL(T2.Field2, '-9999') 

is the best you can do in your situation, and will most likely hurt the query performance significantly. I'd say the real issue is a design one, joining on NULLs is just plain strange to me.

Conrad's and KM's answers achieve your task, but none is very clean. The main reason is that SQL with introduction of NULLs allowed support for three value logic where NULL is not equal NULL (operator =).

Your case is one of the reasons why NULLs are controversial and you can read some interesting rationale on NULLs starting with wikipedia