NULL Comparison

Question

I am developing a dynamic SQL query that validates a source table against a target table. I would like to compare source lines with target lines 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 zeros. If table-col1 is null and table-b col1 is null, then I consider them equal.

I know that I can put ISNULL around my columns, but this is a dynamic query that is being created, so I only know the column name, not the data type.

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

Answer 2

What about null?

: bot h, ... , ...

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

Answer 3

If TABLE_A and TABLE_B have the same columns. You can try this

SELECT  * FROM TABLE_A
EXCEPT 
SELECT  * FROM TABLE_B