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

 2 2 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... asked 08 Apr '11, 13:57 Volker Barth 31.7k●323●465●680 accept rate: 32%

 One approach is to use the EXCEPT and UNION clauses, i.e. to find the rows that are in T_Old but not or not identically in T_New and the rows that are in T_New but not or not identically in T_Old and 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. answered 08 Apr '11, 14:06 Volker Barth 31.7k●323●465●680 accept rate: 32% 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 `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 `````` answered 08 Apr '11, 17:35 Glenn Paulley 10.7k●5●71●104 accept rate: 43% 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... (09 Apr '11, 06:50) Volker Barth
 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. answered 09 Apr '11, 17:31 Ivan T. Bowman 2.8k●2●27●32 accept rate: 39% 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
 toggle preview community wiki:

### Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

question asked: 08 Apr '11, 13:57

question was seen: 7,519 times

last updated: 02 Sep '11, 15:02