EXPLAIN EXTENDED

From Stack Overflow:

UPDATE  n
SET    AddressID = a.AddressID
FROM   #NewAddress n
JOIN   dbo.Address a
ON     (a.[LineA] = n.[LineA] OR (a.[LineA] is null AND n.[LineA] is null))
AND (a.[LineB] = n.[LineB] OR (a.[LineB] is null AND n.[LineB] is null))
AND (a.[LineC] = n.[LineC] OR (a.[LineC] is null AND n.[LineC] is null))
WHERE  n.Processed = 0

Any ideas on how to UNION or EXISTS this query? This seems to be a pretty common join condition I'm encountering.

Here's a JOIN condition: we should join two tables on a common key, also treating NULL values as equal. Normal equality conditions don't work here: in SQL, NULL values are not considered equal (strictly speaking, their equality is a boolean NULL rather than true or false).

ANSI standard defines a special predicate for that: IS NOT DISTINCT FROM, which is supported by PostgreSQL. This treats values the same value as DISTINCT does: two equal values, as well as two NULL values, are considered not distinct. MySQL also supports similar operator, <=>. Unfortunately, SQL Server supports neither.

In SQL Server, we can emulate this by using set operators which distinctify the queries. Set operators work on sets, so we would need some kind of a SELECT query for them. This would also help us to overcome SQL Server's inability to compare tuples directly. Basically, we need to tell if two tuples: (a.lineA, a.lineB, a.lineC) and (n.lineA, n.lineB, n.lineC) are distinct or not.

We can do that by selecting those tuples in dummy queries (those without a FROM clause), intersecting those queries (which would give us a one-record or zero-record resultset, depending on whether the two tuples are distinct or not) and applying EXISTS to the resultset. EXISTS is a boolean predicate and as such can serve as a join condition.

Let's check it. We'll create two tables, fill them with random values (including occasional NULLs here and there):

CREATE TABLE
a
(
id INT NOT NULL PRIMARY KEY IDENTITY,
lineA VARCHAR(MAX),
lineB VARCHAR(MAX),
lineC VARCHAR(MAX)
)

CREATE TABLE
n
(
id INT NOT NULL PRIMARY KEY IDENTITY,
lineA VARCHAR(MAX),
lineB VARCHAR(MAX),
lineC VARCHAR(MAX)
)

DECLARE @count INT = 0

SELECT RAND(0.20130508)

WHILE @count <= 10000
BEGIN
        INSERT
        INTO    a (lineA, lineB, lineC)
        VALUES  (
                'string' + CASE WHEN RAND() > 0.03 THEN CAST(FLOOR(RAND() * 200) AS VARCHAR) END,
'string' + CASE WHEN RAND() > 0.03 THEN CAST(FLOOR(RAND() * 200) AS VARCHAR) END,
'string' + CASE WHEN RAND() > 0.03 THEN CAST(FLOOR(RAND() * 200) AS VARCHAR) END
)

INSERT
INTO    n (lineA, lineB, lineC)
VALUES  (
'string' + CASE WHEN RAND() > 0.03 THEN CAST(FLOOR(RAND() * 200) AS VARCHAR) END,
'string' + CASE WHEN RAND() > 0.03 THEN CAST(FLOOR(RAND() * 200) AS VARCHAR) END,
'string' + CASE WHEN RAND() > 0.03 THEN CAST(FLOOR(RAND() * 200) AS VARCHAR) END
)

SET @count = @count + 1
END

and join them:

SELECT  *
FROM    a
JOIN    n
ON      EXISTS
(
SELECT  a.lineA, a.lineB, a.lineC
INTERSECT
SELECT  n.lineA, n.lineB, n.lineC
)

[Microsoft][SQL Server Native Client 10.0][SQL Server]Table 'Worktable'. Scan count 19, logical reads 115, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
[Microsoft][SQL Server Native Client 10.0][SQL Server]Table 'n'. Scan count 1, logical reads 59, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
[Microsoft][SQL Server Native Client 10.0][SQL Server]Table 'a'. Scan count 1, logical reads 59, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
[Microsoft][SQL Server Native Client 10.0][SQL Server]
 SQL Server Execution Times:
   CPU time = 516 ms,  elapsed time = 520 ms. 

  |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([ee].[dbo].[a].[lineA], [ee].[dbo].[a].[lineB], [ee].[dbo].[a].[lineC])=([ee].[dbo].[n].[lineA], [ee].[dbo].[n].[lineB], [ee].[dbo].[n].[lineC]), RESIDUAL:([ee].[dbo].[a].[lineA] = [ee].[dbo].[n].[lineA] AND [ee].[dbo].[a].[lineB] = [ee].[dbo].[n].[lineB] AND [ee].[dbo].[a].[lineC] = [ee].[dbo].[n].[lineC]))
       |--Sort(ORDER BY:([ee].[dbo].[a].[lineA] ASC, [ee].[dbo].[a].[lineB] ASC, [ee].[dbo].[a].[lineC] ASC))
       |    |--Clustered Index Scan(OBJECT:([ee].[dbo].[a].[PK__a__3213E83F76CD3E06]))
       |--Sort(ORDER BY:([ee].[dbo].[n].[lineA] ASC, [ee].[dbo].[n].[lineB] ASC, [ee].[dbo].[n].[lineC] ASC))
            |--Clustered Index Scan(OBJECT:([ee].[dbo].[n].[PK__n__3213E83F7A9DCEEA]))

Note that the query uses Merge Join which is only possible on equality conditions.

Indeed, we see this in the plan:

([ee].[dbo].[a].[lineA], [ee].[dbo].[a].[lineB], [ee].[dbo].[a].[lineC])=([ee].[dbo].[n].[lineA], [ee].[dbo].[n].[lineB], [ee].[dbo].[n].[lineC])

which is good old tuple comparison. As it turns out, SQL Server is capable of doing this behind the scenes, it's just something it does not implement in the front end.

Note that if we put original join clause here, it will yield exactly same plan:

SELECT  *
FROM    a
JOIN    n
ON      (a.lineA = n.lineA OR (a.lineA IS NULL AND n.lineA IS NULL))
AND (a.lineB = n.lineB OR (a.lineB IS NULL AND n.lineB IS NULL))
AND (a.lineC = n.lineC OR (a.lineC IS NULL AND n.lineC IS NULL))

[Microsoft][SQL Server Native Client 10.0][SQL Server]Table 'Worktable'. Scan count 19, logical reads 115, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
[Microsoft][SQL Server Native Client 10.0][SQL Server]Table 'n'. Scan count 1, logical reads 59, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
[Microsoft][SQL Server Native Client 10.0][SQL Server]Table 'a'. Scan count 1, logical reads 59, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
[Microsoft][SQL Server Native Client 10.0][SQL Server]
 SQL Server Execution Times:
   CPU time = 500 ms,  elapsed time = 523 ms. 

  |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([ee].[dbo].[a].[lineA], [ee].[dbo].[a].[lineB], [ee].[dbo].[a].[lineC])=([ee].[dbo].[n].[lineA], [ee].[dbo].[n].[lineB], [ee].[dbo].[n].[lineC]), RESIDUAL:([ee].[dbo].[a].[lineA] = [ee].[dbo].[n].[lineA] AND [ee].[dbo].[a].[lineB] = [ee].[dbo].[n].[lineB] AND [ee].[dbo].[a].[lineC] = [ee].[dbo].[n].[lineC]))
       |--Sort(ORDER BY:([ee].[dbo].[a].[lineA] ASC, [ee].[dbo].[a].[lineB] ASC, [ee].[dbo].[a].[lineC] ASC))
       |    |--Clustered Index Scan(OBJECT:([ee].[dbo].[a].[PK__a__3213E83F76CD3E06]))
       |--Sort(ORDER BY:([ee].[dbo].[n].[lineA] ASC, [ee].[dbo].[n].[lineB] ASC, [ee].[dbo].[n].[lineC] ASC))
            |--Clustered Index Scan(OBJECT:([ee].[dbo].[n].[PK__n__3213E83F7A9DCEEA]))

, so this trick is a pure syntactic sugar. However, it's a good replacement for IS DISTINCT FROM / IS NOT DISTINCT FROM and will make your queries look prettier.