Re: sql to see all diff between two table

<sergey_s_at_my-deja.com> wrote in message news:8qj5io$m7r$1_at_nnrp1.deja.com...
> 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.

There were actually two statements, tabA-tabB, tabB-tabA. What is the case when they "fail"? (Identical rows between tables should obviously not show up.)

Regards,
/fad

> 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 Sun Sep 24 2000 - 12:31:30 CDT