Re: sql to see all diff between two table

I would have suggested the same thing as Sybrand Bakker did, but I recently learned that there is a case when the sql statement mentioned in his response would fail to show the difference between two tables. The safest way to go would be the following.

select count(column1), column1, column2, column3, ... from table1
group by column1, column2, column3, ...
MINUS
select count(column1), column1, column2, column3, ... from table2
group by column1, column2, column3, ...

When you have identical rows in two tables (one in table1 and two or more in table2) then the above statement will show the extra rows from the second table whereas Sybrand's example will report 'no rows selected'.

Sergey

In article <Jc3z5.14663$tn.229863_at_typhoon.ne.mediaone.net>,   "xgong" <xgong_at_mediaone.net> wrote:
> I have duplicated table, table1 and table2. Then table2 has been
 updated
> with some fields.
> How to get the all diff between two tables using sql?
>
> following does not work for me:
>
> select * from table1 where * not in (select * from table2)
>
> thank you for your help!
>
> ydeng
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Sep 23 2000 - 15:59:11 CDT