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 - Personally, I still prefer this approach for ad-hoc queries, as it allows me to simply use "select "* instead of long column lists...
(02 Sep '11, 15:02)
Volker Barth
|
Using
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 If my reasoning is right, I would guess this is usually not an easier approach...
(09 Apr '11, 06:50)
Volker Barth
|
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. 1
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:)
(10 Apr '11, 15:08)
Volker Barth
|