How to compare two nearly identical tables and list the differencing rows?

[VolkerBarth]

A typical ad-hoc task - here with SA 12.0.1:

I have two tables with identical scheme and nearly indentical contents, e.g. a table with old data and a copy of that table with new data. Say, they are named T_Old and T_New. Furthermore both tables have a primary key (say, "pk") and that pk field won't be changed in between.

Now I want to list all rows with identical pk and changed contents (including deleted and added rows), and I want to have them orderer in pk order, then by old/new.

---

Note: I'm gonna present an answer but would like to know if there are better/easier approaches...

[VolkerBarth · Accepted solution]

One approach is to use the EXCEPT and UNION clauses, i.e. to find

1. the rows that are in T_Old but not or not identically in T_New and
2. the rows that are in T_New but not or not identically in T_Old and
3. UNION them together.

In order to tag "old" and "new", add an according dummy column.

Note that UNION and EXCEPT treat nulls as identical - which is very important here.

So the following would do:

(select 'old', * from T_Old
except all
select 'old', * from T_New)
union all
(select 'new', * from T_New
except all
select 'new', * from T_Old)
order by pk, 1 desc

That will display all added, changed or deleted rows in "pk" order, and for each pk row, the "old" row will list before the "new" one.

Note that EXCEPT ALL and UNION ALL can be used for better performance as the underlying sets are disjunct by definition (for the EXCEPT sets, each table has a PK, and for the UNION, the 'old' vs. 'new' enforces the uniqueness).

---

EDIT: As to the default precedence of UNION/EXCEPT, cf. my other question - in general, it seems clearer to put parantheses around the EXCEPT parts as EXCEPT and UNION have the same precedence and bind in left-to-right-order, the EXCEPT parts must be put in parantheses as in the sample.

[VolkerBarth · Accepted solution]

Personally, I still prefer this approach for ad-hoc queries, as it allows me to simply use "select "* instead of long column lists...

[Former Member]

You could do something like the following:

:::SQL
select sum(old) sum_old,sum(new)sum_new, [.all columns of T_Old.]
from (
  select 1 as old, 0 as new, * from T_Old
  union all
  select 0 as old, 1 as new, * from T_New
) DT
group by [.all columns.]
having sum_old <> sum_new
order by PK, sum_old desc

It is likely more efficient to execute because the server can process the entire result set in one pass instead of doing two EXCEPTs. The INTERSECT and EXCEPT operators effectively do something like this (with an extra operator above to get the desired number of duplicates for the ALL variants). Generating the SQL is more work because you need to know the schema of T_Old and T_New, but not too much more work.

[VolkerBarth]

Wow, I took my quite a while to understand this interesting approach - and I guess I'm not finally done... I'll have to try it in practise.

Well, I have to confess that's not my level of ease - but thanks for widening the SQL horizon:)

[Former Member]

Using FULL OUTER JOIN is a possibility.

SELECT T_NEW.*, T_OLD.*
FROM T_NEW FULL OUTER JOIN T_OLD 
   ON (T_NEW.PK = T_OLD.PK AND T_NEW.X = T_OLD.X AND T_NEW.Y = T_OLD.Y ... )
WHERE T_NEW.PK IS NULL OR T_OLD.PK IS NULL

[VolkerBarth]

Another approach, I would agree - but in case of lots of columns (which usually may be nullable) I guess the join condition

a) would be accordingly long and
b) would need to handle NULLs appropriately, say by using the new v12 NOT DISTINCT FROM expression to treat NULLs in both tables as identical - otherwise I guess identical rows with NULL values would be contained in the result.

If my reasoning is right, I would guess this is usually not an easier approach...