The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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%20Barth's gravatar image

Volker Barth
accept rate: 32%

edited 08 Apr '11, 14:29

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.

permanent link

answered 08 Apr '11, 14:06

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

edited 02 Sep '11, 15:00

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.

permanent link

answered 08 Apr '11, 17:35

Glenn%20Paulley's gravatar image

Glenn Paulley
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:

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.

permanent link

answered 09 Apr '11, 17:31

Ivan%20T.%20Bowman's gravatar image

Ivan T. Bowman
accept rate: 39%

edited 09 Apr '11, 17:31


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
Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • 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: 6,535 times

last updated: 02 Sep '11, 15:02