[SOLVED] SQL Question: Find differences between tables

[tarsier]

I have a SQL question, for any of you SQL experts out there.

I have the following SQL statement to return all the lines that are different between two tables. This is to say that if there is a line in table_2 that differs from any line in table_1 in field a, b, or c, it gets selected.

 

SELECT * FROM table_2

WHERE NOT EXISTS

(SELECT * FROM table_1

WHERE  table_2.a = table_1.a

AND table_2.b = table_1.b

AND table_2.c = table_1.c )

 

My problem is that this, for some reason, also selects any row that has a NULL in field a, b, or c, even if it's NULL in both table_1 and table_2. Any ideas why this would be and how to correct for it?

 

- Dave

 

[btherl]

The problem is that null is not equal to null, because null means "not known".  You'll need to change it to this:

 

SELECT * FROM table_2
WHERE NOT EXISTS
   (SELECT * FROM table_1
   WHERE (table_2.a = table_1.a or (table_2.a is null and table_1.a is null))
   AND (table_2.b = table_1.b or (table_2.a is null and table_1.a is null))
   AND (table_2.c = table_1.c or (table_2.a is null and table_1.a is null))

 

There may be a simplification of the above.. basically I am checking the case where both columns are null, in which case you want the condition also to be satisfied, even though null != null.

[tarsier]

That did the trick. Thanks!

 

[fenway]

You may want to consider rewriting that as a JOIN... but in general, JOINing on columns that are NULLable poses the problem you just encountered.