Sql-server – Comparing Columns that can contain NULLS – is there a more elegant way

nullsql server

I know that you can't compare a value to NULL and expect a result without adding something like in the following code…

SELECT
    *
FROM 
    A INNER JOIN 
    B ON A.ID = B.ID
WHERE
    A.STRING <> B.STRING OR (A.STRING IS NULL AND B.STRING IS NOT NULL) OR (A.STRING IS NOT NULL AND B.STRING IS NULL) OR 
    A.DT <> B.DT OR (A.DT IS NULL AND B.DT IS NOT NULL) OR (A.DT IS NOT NULL AND B.DT IS NULL) OR 
    A.B <> B.B OR (A.B IS NULL AND B.B IS NOT NULL) OR (A.B IS NOT NULL AND B.B IS NULL) OR 
    A.NUM <> B.NUM OR (A.NUM IS NULL AND B.NUM IS NOT NULL) OR (A.NUM IS NOT NULL AND B.NUM IS NULL) 

My question is:

Is there a more elegant way to test for changed values across the two tables where either one could be null?

Solution needs to work uniformly across the data types.

Here is the code to set up the test tables…

CREATE TABLE A
(
    ID INT IDENTITY(1,1) NOT NULL,
    STRING VARCHAR(20) NULL,
    DT DATETIME NULL,
    B BIT NULL,
    NUM INT NULL
)

CREATE TABLE B
(
    ID INT IDENTITY(1,1) NOT NULL,
    STRING VARCHAR(20) NULL,
    DT DATETIME NULL,
    B BIT NULL,
    NUM INT NULL
)


INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES (NULL, '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', NULL, 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', NULL, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, NULL)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO A (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)


INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('STAGE', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2555-11-11 00:00:00.000', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 23)
INSERT INTO B (STRING, DT, B, NUM) VALUES ('TEST', '2012-03-16 16:39:04.893', 0, 999)

Best Answer

You can use this approach from Paul White's article Undocumented Query Plans: Equality Comparisons

SELECT * 
FROM   A 
       INNER JOIN B 
         ON A.ID = B.ID 
            AND EXISTS(SELECT A.* 
                       EXCEPT 
                       SELECT B.*)