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...
One approach is to use the EXCEPT and UNION clauses, i.e. to find
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 -
answered 08 Apr '11, 17:35
You could do something like the following:
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.