CODEWITHSUNDEEP
sqlsql-server-2008t-sql
user829237
user829237

Reputation: 1769

Comparing NULLs

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?

Upvotes: 5

Views: 179

Answers (3)

EricZ
EricZ

Reputation: 6205

If TABLE_A and TABLE_B have same columns. You may try this 

SELECT  * FROM TABLE_A
EXCEPT 
SELECT  * FROM TABLE_B

Upvotes: 0

Tevo D
Tevo D

Reputation: 3381

How about IS NULL?

Edit: Updated query to account for bot h columns being null... getting long and ugly, but working...

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

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453877

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

Upvotes: 9

Related Questions