Comparing NULLs

Question

I'm designing a dynamic SQL-query that will check a source-table against a target table. I would like to compare the rows of the source against the rows of the target to check for differences.

CREATE TABLE TABLE_A (KEY INT, COL1 INT, COL2 INT)
CREATE TABLE TABLE_B (KEY INT, COL1 INT, COL2 INT)

So i created this statement:

SELECT A.* FROM TABLE_A A
INNER JOIN TABLE_B B
ON B.KEY = A.KEY
AND (B.COL1<>A.COL1 OR B.COL2<>A.COL2)

But this only works as long as the values of col1 and col2 are not nulls. If table-a col1 is null and table-b col1 is null then i would consider them equal.

I know i can put an ISNULL around my columns, but this is a dynamic query being built up, so i only know the column name not the datatype.

Any suggestions?

Answer 1

You can use this approach

SELECT A.* 
FROM TABLE_A A
INNER JOIN TABLE_B B
ON B.KEY = A.KEY
WHERE NOT EXISTS (SELECT A.* 
                  INTERSECT 
                  SELECT B.* )