patternsqlMajor
Comparing Columns that can contain NULLS - is there a more elegant way?
Viewed 0 times
cancomparingcolumnscontainmorewaythateleganttherenulls
Problem
I know that you can't compare a value to NULL and expect a result without adding something like in the following code...
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 (
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 (
Solution
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.*)Code Snippets
SELECT *
FROM A
INNER JOIN B
ON A.ID = B.ID
AND EXISTS(SELECT A.*
EXCEPT
SELECT B.*)Context
StackExchange Database Administrators Q#15190, answer score: 26
Revisions (0)
No revisions yet.