Excluding matches on JOIN fields that are NULL

Answer 1

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.

Answer 2

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

Answer 3

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