Re: sql to see all diff between two table

> 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.)

Create two tables - one with a single row and the other with two rows. Make sure that both tables are identical in structure and that the rows in both tables contain identical data. Now, if you do tab1-tab2 followed by tab2-tab1 you will get 'no rows selected' in both cases. That's not good if you are trying to find out if the two tables are exactly the same (they actually aren't since one of the tables has the extra row). In my case, I needed to see the *extra* identical rows in one or the other table.

Of course, if you don't care about the difference in the number of rows so long as both tables have the same data then Sybrand's example works just fine.

Sergey

In article <lUqz5.13259$Fl2.114185_at_nntpserver.swip.net>,   "Bastorff" <nospam_at_localhost.zz> wrote:
> <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.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Sep 27 2000 - 10:19:07 CDT