Jump to content

[SOLVED] SQL Question: Find differences between tables


tarsier

Recommended Posts

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

 

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.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.